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