When you Create a Role it is Granted to you Automatically

I noticed recently that, when you create a role, it is automatically granted to you with the admin option. You can see what I mean in the example below, which I tested in Oracle 11.2:

 

SQL> conn andrew/reid

Connected.

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee, admin_option

  2  from dba_role_privs

  3  where granted_role = 'BLAH'

  4  /

 

GRANTEE                        ADMIN_OPTION

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

ANDREW                         YES

 

SQL>

Grant Any Object Privilege

This example was tested on Oracle 11.2. It demonstrates the grant any object privilege system privilege. First create three users:

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

User created.

SQL> grant create session, create table

  2  to andrew

  3  /

Grant succeeded.

SQL> create user fred

  2  identified by bloggs

  3  /

User created.

SQL> grant create session, grant any object privilege

  2  to fred

  3  /

Grant succeeded.

SQL> create user nosey

  2  identified by parker

  3  /

User created.

SQL> grant create session to nosey

  2  /

Grant succeeded.

SQL>

Andrew Reid has won a lot of money on the lottery. He creates a table to record his bank balance but wants to keep it secret:

SQL> conn andrew/reid

Connected.

SQL> create table bank_account

  2  as select 1234567 balance

  3  from dual

  4  /

Table created.

SQL> select balance from bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

Nosey Parker hears a rumour about the lottery win and tries to look at Andrew's bank account:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

select balance from andrew.bank_account

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Nosey knows that Fred Bloggs has the grant any object privilege system privilege and asks him to help. Fred grants select privilege on Andrew's bank_account table to Nosey: 

SQL> conn fred/bloggs

Connected.

SQL> grant select on andrew.bank_account to nosey

  2  /

Grant succeeded.

SQL>

Nosey can then see how much money Andrew has:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

You might think that Fred would appear in dba_tab_privs as the grantor of this privilege as he ran the grant statement but you would be wrong. Andrew appears as the grantor instead: 

SQL> conn / as sysdba

Connected.

SQL> select grantor from dba_tab_privs

  2  where grantee = 'NOSEY'

  3  and owner = 'ANDREW'

  4  and table_name = 'BANK_ACCOUNT'

  5  and privilege = 'SELECT'

  6  /

GRANTOR

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

ANDREW

SQL>

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>