A Problem with REVOKE

If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege. This isn’t too much of an issue.

 

However, if you grant the UNLIMITED TABLESPACE system privilege to a user by itself THEN grant it the DBA role, Oracle seems to have no idea where the UNLIMITED TABLESPACE system privilege came from. If you then revoke the DBA role from this user, Oracle still revokes the UNLIMITED TABLESPACE system privilege from it. This may not be what you intended to do. You can see what I mean in the example below, which I tested in an Oracle 11.2 database.

 

First I created a user:

 

SQL> create user a identified by b

  2  /

 

User created.

 

SQL>

 

I checked that it had no roles nor system privileges:

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the DBA role to the user and checked that this also gave it the UNLIMITED TABLESPACE system privilege:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

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

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

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

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role and checked that Oracle also revoked the UNLIMITED TABLESPACE system privilege:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL>

 

I granted the UNLIMITED TABLESPACE system privilege to the user independently:

 

SQL> grant unlimited tablespace to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

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

UNLIMITED TABLESPACE

 

SQL>

 

I granted the DBA role to the user:

 

SQL> grant dba to a

  2  /

 

Grant succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

GRANTED_ROLE

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

DBA

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

 

PRIVILEGE

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

UNLIMITED TABLESPACE

 

SQL>

 

I revoked the DBA role from the user. Oracle revoked the UNLIMITED TABLESPACE system privilege at the same time despite the fact that I had granted it separately:

 

SQL> revoke dba from a

  2  /

 

Revoke succeeded.

 

SQL> select granted_role from dba_role_privs

  2  where grantee = 'A'

  3  /

 

no rows selected

 

SQL> select privilege from dba_sys_privs

  2  where grantee = 'A'

  3  /

no rows selected

SQL>

ORA-01700 and/or ORA-01711

I tested these on Oracle 11.2. You can grant a privilege like this:

SQL> grant create table to andrew

  2  /

 

Grant succeeded.

SQL>

... but if you try to grant a privilege more than once in the same grant statement, you get an ORA-01711:

SQL> grant create table,

  2  select any table,

  3  create table to andrew

  4  /

create table to andrew

*

ERROR at line 3:

ORA-01711: duplicate privilege listed

SQL>

... and if you try to grant a privilege to the same user more than once in the same grant statement, you get an ORA-01700:

SQL> grant create table to

  2  andrew, fred, andrew

  3  /

andrew, fred, andrew

              *

ERROR at line 2:

ORA-01700: duplicate username in list

 

SQL>

ORA-01749

This was tested on an Oracle 11.2 database. You cannot GRANT or REVOKE object privileges to or from yourself:

 

SQL> show user

USER is "ANDREW"

SQL> grant select on blah to john

  2  /

 

Grant succeeded.

 

SQL> grant select on blah to andrew

  2  /

grant select on blah to andrew

                        *

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from

yourself

 

SQL> revoke select on blah from john

  2  /

 

Revoke succeeded.

 

SQL> revoke select on blah from andrew

  2  /

revoke select on blah from andrew

                           *

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from

yourself

 

SQL>

ORA-01934

This example was tested on Oracle 11.2. It should be fairly obvious that you cannot grant a role to itself. You get an ORA-01934 if you try:

SQL> create role andrew1;

Role created.

SQL> grant andrew1 to andrew1;

grant andrew1 to andrew1

*

ERROR at line 1:

ORA-01934: circular role grant detected

SQL>

However, if you weren't paying attention, you might, for example, grant one role to a second role then grant that second role to a third role. If you later tried to grant the third role back to the first role, you would get another ORA-01934: 

SQL> create role andrew2;

Role created.

SQL> create role andrew3;

Role created.

SQL> grant andrew1 to andrew2;

Grant succeeded.

SQL> grant andrew2 to andrew3;

Grant succeeded.

SQL> grant andrew3 to andrew1;

grant andrew3 to andrew1

*

ERROR at line 1:

ORA-01934: circular role grant detected

SQL>

ORA-00990

This was tested on Oracle 11. You can grant roles and system privileges in the same statement. But if you want to include object privileges, you need to use a new GRANT statement:

SQL> grant dba, alter system,

  2  execute on sys.dbms_lock

  3  to andrew

  4  /

grant dba, alter system,

      *

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant dba, alter system

  2  to andrew

  3  /

Grant succeeded.

SQL> grant execute on sys.dbms_lock

  2  to andrew

  3  /

Grant succeeded.

SQL>

ORA-01919

This example, tested on Oracle 9, demonstrates the ORA-01919 error message, which some people find misleading. First create a user called ANDREW, a role called BLAH and another user called ANOTHER_USER:

 

SQL> conn / as sysdba

Connected.

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> create user another_user

  2  identified by another_password

  3  /

 

User created.

 

SQL>

 

Connect as ANDREW then try to grant role BLAH to ANOTHER_USER. This fails but the error message is not helpful. Role BLAH does exist but it has not been granted to ANDREW yet:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01919: role 'BLAH' does not exist

 

SQL>

 

Grant the role BLAH to ANDREW:

 

SQL> conn / as sysdba

Connected.

SQL> grant blah to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

Then login as ANDREW and try to grant BLAH to ANOTHER_USER again. This time the error message gives a better explanation:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01932: ADMIN option not granted for role 'BLAH'

 

SQL>

 

Now grant the role to ANDREW with the ADMIN option:

 

SQL> conn / as sysdba

Connected.

SQL> grant blah to andrew with admin option

  2  /

 

Grant succeeded.

 

SQL>

 

Login as ANDREW again. This time the GRANT statement works:

 

SQL> conn andrew/reid

Connected.

SQL> grant blah to another_user

  2  /

 

Grant succeeded.

 

SQL>

Improve your Security with Stored Procedures

Try not to grant access to tables directly to users. If you grant delete access, for example, you cannot prevent a user from deleting 1000 rows when he should only be deleting 1. It is better to write stored procedures to access your data then grant execute access on these procedures to the users. The logic in the stored procedure will then provide a further level of control over what the user does.

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>