Wednesday, January 25, 2012

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:
 
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create role role_a
  2  /
 
Role created.
 
SQL> create role role_b
  2  /
 
Role created.
 
SQL> grant role_a to user_1
  2  identified by user_1
  3  /
 
Grant succeeded.
 
SQL> grant role_a, role_b to user_2
  2  identified by user_2
  3  /
 
Grant succeeded.
 
SQL> grant role_b to user_3
  2  identified by user_3
  3  /
 
Grant succeeded.
 
SQL> select distinct grantee from dba_role_privs
  2  where grantee in
  3  (select grantee from dba_role_privs
  4   where granted_role = 'ROLE_A')
  5  and grantee in
  6  (select grantee from dba_role_privs
  7   where granted_role = 'ROLE_B')
  8  /
 
GRANTEE
------------------------------
ORACLE
USER_2
 
SQL> select grantee from dba_role_privs
  2  where granted_role = 'ROLE_A'
  3  intersect
  4  select grantee from dba_role_privs
  5  where granted_role = 'ROLE_B'
  6  /
 
GRANTEE
------------------------------
ORACLE
USER_2
 
SQL> create role blah
  2  /
 
Role created.
 
SQL> select grantee from dba_role_privs
  2  where granted_role = 'BLAH'
  3  /
 
GRANTEE
------------------------------
ORACLE
 
SQL>

No comments: