Network Encryption from Client to Server on Oracle Database connections.

For symmetric cryptsystem  , the same key is used for encryption and decryption .Oracle has a solution to encrypt data on wire.
In order to make secure data transmission from client to server we can make some configuration in sqlnet.ora file.

First we look at network encryption from client to server. For this reason ; the easiest way is to change the sqlnet.ora at client side.

Below you can see some definitions in sqlnet.ora file at client side. Simply client requests to server for secure transmission. If server Acccepts it ; network encryption starts automaticly.

-----------


SQLNETSQLNET.ENCRYPTION_CLIENT =   Requested.
ENCRYPTION_TYPES_CLIENT = ( AES128 )
SQLNET.CRYPTO_CHECKSUM_CLIENT =   Requested
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)

----------

at this example ; we assume  that there is no configuration about data encryption at server side.
It means that default value for

SQLNET.ENCRYPTION_SERVER =   Accepted

If there is a configuration at server side; like this

SQLNET.ENCRYPTION_SERVER =   Rejected

You can not start data encryption between client and server.   Because your  configuration is in Requested mode and you can create connection without encryption 

In order to check data encrption we can open trace files with the following configuration in sqlnet.ora file at client side

----------------

TRACE_LEVEL_CLIENT = 10
TRACE_DIRECTORY_CLIENT =  C:\Users\u003475\Oracle\oradiag_u003475\diag\clients\user_u003475\host_389884333_11\trace

----------------

After changing sqlnet.ora file look at the trace files and search for  "encryption is active"  words. 
If you find  these words at trace file. It means that you succesfuly started a secure connection between client and Server.

----------------

the inside of trace file

2014-07-03 15:08:21.586127 : nau_adi:exit

2014-07-03 15:08:21.586146 : na_tns: authentication is not active

2014-07-03 15:08:21.586166 : na_tns: encryption is active, using RC4_128

2014-07-03 15:08:21.586186 : na_tns: crypto-checksumming is not active

2014-07-03 15:08:21.586205 : na_tns:exit

2014-07-03 15:08:21.586224 : na_coco:exit

------------------

The use of Global_Names as a Security Parameter

The parameter GLOBAL_NAMES specifies whether a database link is required to have the same name as the global database name to which it connects.

Simply If you set GLOBAL_NAMES  to True ; Whenever you create a database link the name of the db link must equal to global database name.

If you set GLOBAL_NAMES  to False ; Oracle does not make this check.

Oracle recommends the use of this parameter to ensure the use of consistent naming conventions.
But for security there is more than namig convention;

If you set global_names=FALSE ; someone can  change the definition of the link  and you can connect to wrong database. Simply this parameter assures that you connected the right database.


In order to see the value of this parameter



SQL> show parameter global_Names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE


 If you create a database link with the name a1 to connect OIDSRC database.



SQL>  create database link a1 connect to a1 identified by a1 using 'OIDSRC';

Database link created.



Check the connection

SQL> select * from dual@a1;

D
-
X


And then set Global_Names to TRUE


SQL> alter system set global_Names =TRUE;

System altered.


Check the database Link Now


SQL>  select * from dual@a1;
 select * from dual@a1
                    *
ERROR at line 1:
ORA-02085: database link A1 connects to OIDSRC


Now you can get ORA-02085 because Oracle checks the name of the database link  link with database name to assure that you are trying to connect the right database.

Now correct the database link definition and it works.


SQL> create database link OIDSRC connect to a1 identified by a1 using 'OIDSRC';

Database link created.

SQL> select * from dual@OIDSRC;

D
-
X

                                

Finding Oracle Users with DBA role

Finding Oracle Users with DBA role can be simple question, but when you think about roles assigned to roles and roles assigned to roles and at the end roles assigned to a user . In that case ; It can be diffucult question.

Normally we use below query to find  users with DBA role

SELECT *
  FROM dba_ROLE_PRIVS
 WHERE granted_role = 'DBA';

This query returns to you users and roles who have directly assigned DBA role .

Maybe you give grant DBA role to any role and when you assign this role to a user. You can not see this user at the output of this query.  Only you can see that one role has DBA role, but you can not see which user has this right.

To solve this problem we will use Hierarchial Queries


START WITH ... and
CONNECT BY ...


key clauses to solve this problem;  If you have any idea about Hierarchial Queries ; you can  use below link

http://docs.oracle.com/cd/E16655_01/server.121/e17209/queries003.htm#SQLRF52332

And my query like this ; You can write your own query in an different way. But It works.

SELECT c.DBA_USER,
       b.account_status,
       b.created,
       b.profile
  FROM (SELECT username DBA_USER FROM V$PWFILE_USERS
        UNION
        SELECT DISTINCT grantee DBA_USER
          FROM (    SELECT DISTINCT LEVEL level_deep, grantee, granted_role
                      FROM dba_role_privs
                START WITH granted_role = 'DBA'
                CONNECT BY PRIOR grantee = granted_role)
         WHERE grantee IN (SELECT username FROM dba_users)) c,
       dba_users b
 WHERE c.DBA_USER = b.username

Oracle PSU vs CPU

PSU and CPU is one of the biggest issue during audit. Auditors wants to see that you should apply the last patches.

But  the question is
Should you apply PSU or CPU ?
Actually If you apply last PSU , normally It consist of last CPU as well.

PSU and CPU 's are inclusive, A PSU consist of last PSU as well. Therefore If you apply last PSU , It means you apply all PSUs for this version.

To find PSU number ; you should check the last 5th digit. That works for   10g,11g and 12c . For these versions I strongly recommend  below metalink notes. These notes can answer all of your specific questions.

• Patch Set Updates for Oracle Products (Doc ID 854428.1)
• My Oracle Support Note 1618213.1
• Oracle Announces First Patch Set Update For Oracle Database Release 10.2 (Doc ID 850471.1)

And some practical information ;

If you want to check which PSUs or CPUs are applied to your database from command shell.

$ opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE'

14727310   14727310  Sun Mar 24 05:53:51 EET 2013   DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPU

14275605   14275605  Sun Mar 24 05:52:28 EET 2013   DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU13923374   

13923374  Sun Mar 24 05:52:01 EET 2013   DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES 13696216   

13696216  Sun Mar 24 05:51:07 EET 2013   DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES 

13343438   13343438  Sun Mar 24 05:49:26 EET 2013   DATABASE PATCH SET UPDATE 11.2.0.3.1


For the same Database ; you can query PSU history by a sql statement.

  SELECT *

    FROM DBA_REGISTRY_HISTORY

    ORDER BY action_time DESC;

Finding Oracle DB Users with Critical System Priviliges

First you have to define which system privileges are critical for you.  For example the below list is enough for me. Normal App user should not have below priviliges.

• BECOME USER
• ALTER USER
• DROP USER
• CREATE ROLE
• ALTER ANY ROLE
• DROP ANY ROLE
• GRANT ANY ROLE
• CREATE PROFILE
• ALTER PROFILE
• DROP PROFILE
• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP ANY TABLE
• INSERT ANY TABLE
• UPDATE ANY TABLE
• DELETE ANY TABLE
• CREATE ANY PROCEDURE
• ALTER ANY PROCEDURE
• DROP ANY PROCEDURE
• CREATE ANY TRIGGER
• ALTER ANY TRIGGER
• DROP ANY TRIGGER
• CREATE TABLESPACE
• ALTER TABLESPACE
• DROP TABLESPACES
• ALTER DATABASE
• ALTER SYSTEM

Now The simplest query to find who has the system privileges. But the output of this query can be devious.

Because ; Users and Roles can be seen at the query result.

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE PRIVILEGE IN
          ('BECOME USER',
           'ALTER USER',
           'DROP USER',
           'CREATE ROLE',
           'ALTER ANY ROLE',
           'DROP ANY ROLE',
           'GRANT ANY ROLE',
           'CREATE PROFILE',
           'ALTER PROFILE',
           'DROP PROFILE',
           'CREATE ANY TABLE',
           'ALTER ANY TABLE',
           'DROP ANY TABLE',
           'INSERT ANY TABLE',
           'UPDATE ANY TABLE',
           'DELETE ANY TABLE',
           'CREATE ANY PROCEDURE',
           'ALTER ANY PROCEDURE',
           'DROP ANY PROCEDURE',
           'CREATE ANY TRIGGER',
           'ALTER ANY TRIGGER',
           'DROP ANY TRIGGER',
           'CREATE TABLESPACE',
           'ALTER TABLESPACE',
           'DROP TABLESPACES',
           'ALTER DATABASE',
           'ALTER SYSTEM');


In order to report only Oracle user ; we should add a simple predicate .

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE     PRIVILEGE IN
              ('BECOME USER',
               'ALTER USER',
               'DROP USER',
               'CREATE ROLE',
               'ALTER ANY ROLE',
               'DROP ANY ROLE',
               'GRANT ANY ROLE',
               'CREATE PROFILE',
               'ALTER PROFILE',
               'DROP PROFILE',
               'CREATE ANY TABLE',
               'ALTER ANY TABLE',
               'DROP ANY TABLE',
               'INSERT ANY TABLE',
               'UPDATE ANY TABLE',
               'DELETE ANY TABLE',
               'CREATE ANY PROCEDURE',
               'ALTER ANY PROCEDURE',
               'DROP ANY PROCEDURE',
               'CREATE ANY TRIGGER',
               'ALTER ANY TRIGGER',
               'DROP ANY TRIGGER',
               'CREATE TABLESPACE',
               'ALTER TABLESPACE',
               'DROP TABLESPACES',
               'ALTER DATABASE',
               'ALTER SYSTEM')
       AND grantee IN (SELECT username FROM dba_users);



Now the point is ; some Oracle users normally have SYSTEM PRIVILEGES; therefore  SYSTEM , SYS and some oracle users should not be on the query output.

SELECT *
  FROM DBA_SYS_PRIVS
 WHERE     PRIVILEGE IN
              ('BECOME USER',
               'ALTER USER',
               'DROP USER',
               'CREATE ROLE',
               'ALTER ANY ROLE',
               'DROP ANY ROLE',
               'GRANT ANY ROLE',
               'CREATE PROFILE',
               'ALTER PROFILE',
               'DROP PROFILE',
               'CREATE ANY TABLE',
               'ALTER ANY TABLE',
               'DROP ANY TABLE',
               'INSERT ANY TABLE',
               'UPDATE ANY TABLE',
               'DELETE ANY TABLE',
               'CREATE ANY PROCEDURE',
               'ALTER ANY PROCEDURE',
               'DROP ANY PROCEDURE',
               'CREATE ANY TRIGGER',
               'ALTER ANY TRIGGER',
               'DROP ANY TRIGGER',
               'CREATE TABLESPACE',
               'ALTER TABLESPACE',
               'DROP TABLESPACES',
               'ALTER DATABASE',
               'ALTER SYSTEM')
       AND grantee IN
              (SELECT username
                 FROM dba_users
                WHERE username NOT IN
                         ('PGAADMIN',
                          'WMSYS',
                          'LBACSYS',
                          'SYS',
                          'SYSTEM',
                          'SGYON_KEY_USR',
                          'EXFSYS',
                          'DVOWNER',
                          'DVACCTMNGR',
                          'DVSYS',
                          'DWOWNER',
                          'RMAN', 'GGATE'));



Now we can list the Oracle users who have the critical system privileges. Is that enough ? It is not enough ;
because we can create a role and we assign this role to any user. After that we can create another role and we can grant this role the previous create role and at the end we can grant a critical system privilege to this role . If you run this query you can't list this user. Because the nature of recursive roles hides this information.
To solve this problem we should use hierarchical query START WITH and CONNECT BY clauses.

And the result ;


    SELECT DISTINCT c "User Names"
      FROM (    /* THE PRIVILEGES */
            SELECT NULL p, name c
              FROM system_privilege_map
             WHERE name IN
                      ('BECOME USER',
                       'ALTER USER',
                       'DROP USER',
                       'CREATE ROLE',
                       'ALTER ANY ROLE',
                       'DROP ANY ROLE',
                       'GRANT ANY ROLE',
                       'CREATE PROFILE',
                       'ALTER PROFILE',
                       'DROP PROFILE',
                       'CREATE ANY TABLE',
                       'ALTER ANY TABLE',
                       'DROP ANY TABLE',
                       'INSERT ANY TABLE',
                       'UPDATE ANY TABLE',
                       'DELETE ANY TABLE',
                       'CREATE ANY PROCEDURE',
                       'ALTER ANY PROCEDURE',
                       'DROP ANY PROCEDURE',
                       'CREATE ANY TRIGGER',
                       'ALTER ANY TRIGGER',
                       'DROP ANY TRIGGER',
                       'CREATE TABLESPACE',
                       'ALTER TABLESPACE',
                       'DROP TABLESPACES',
                       'ALTER DATABASE',
                       'ALTER SYSTEM')
            /*  ROLE RELATIONS */
            UNION
            SELECT granted_role p, grantee c FROM dba_role_privs
            /* PRIVILEGE RELATIONS */
            UNION
            SELECT privilege p, grantee c FROM dba_sys_privs)
     WHERE c IN
              (SELECT username
                 FROM dba_users
                WHERE username NOT IN
                         ('PGAADMIN',
                          'WMSYS',
                          'LBACSYS',
                          'SYS',
                          'SYSTEM',
                          'SGYON_KEY_USR',
                          'EXFSYS',
                          'DVOWNER',
                          'DVACCTMNGR',
                          'DVSYS',
                          'DWOWNER',
                          'RMAN',
                          'GGATE'))
START WITH p IS NULL
CONNECT BY p = PRIOR c;