Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Sunday, June 19, 2011

"WITH GRANT OPTION" and Cascading Revoke

Most of you probably already know, but I want to demonstrate again one of the important features of Oracle database called "Cascading Revoke". I will use Oracle 11g as an example database for this demonstration but this feature is also available in the older versions of Oracle databases.

First connect to the database as SYS user.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 12:05:59 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Create sample schemas
SQL> create user u0001 identified by u0001;

User created.

SQL> grant create session to u0001;

Grant succeeded.

SQL> grant create table to u0001;

Grant succeeded.

SQL> create user u0002 identified by u0002;

User created.

SQL> grant create session to u0002;

Grant succeeded.

SQL> create user u0003 identified by u0003;

User created.

SQL> grant create session to u0003;

Grant succeeded.

Create a sample table in u0001 schema and give select privilege on this table to u0002 user with grant option.
SQL> conn u0001/u0001
Connected.
SQL> create table t0001 (col1 number, col2 varchar2(100));

Table created.

SQL> grant select on t0001 to u0002 with grant option;

Grant succeeded.

SQL> column grantee format a7
SQL> column owner format a5
SQL> column table_name format a10
SQL> column grantor format a7
SQL> column privilege format a9
SQL> column grantable format a9
SQL> select * from user_tab_privs

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0002   U0001 T0001      U0001   SELECT    YES       NO

Connect with u0002 and grant select privilege on u0001.t0001 table to user u0003
SQL> conn u0002/u0002
Connected.
SQL> grant select on u0001.t0001 to u0003;

Grant succeeded.

Check grants on table u0001.t0001
SQL> conn u0001/u0001
Connected.
SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
------- ----- ---------- ------- --------- --------- ---
U0003   U0001 T0001      U0002   SELECT    NO        NO
U0002   U0001 T0001      U0001   SELECT    YES       NO

Now revoke select privilege from only user u0002
SQL> revoke select on t0001 from u0002;

Revoke succeeded.

List privileges on table u0001.t0001
SQL> select * from user_tab_privs;

no rows selected

As you can see, although we revoked the select privilege only from user u0002, select privilege given to user u0003 by u0002 user was also revoked, because a "Cascading Revoke" occured.

In summary, any privilege given to any user(u0003) by another grantor user(u0002) having this privilege "WITH GRANT OPTION" will automatically be revoked when this privilege revoked by object owner(u0001) from this grantor user(u0002).

3 comments:

Mike said...

Lovely! I just tried query shared by you. It worked great for me. Its really great experience of using "WITH GRANT OPTION" and Cascading Revoke . THANKS!
sap test

Tani said...

Using Oracle 11g as for explaining the feature of Cascading Revoke is a excellent idea. it cleared my doubt on using with grant option.

oracle r12 new features

Ural Ural said...

Thank you all of you for your precious comments.
Cheers,
Ural