Tuesday, June 14, 2011

With Grant Option

I used Oracle Database 11g, The Complete Reference by Kevin Loney to help me with this example. It is advertised at the end of this post if you would like to buy it.

It illustrates a problem I had recently while running some SQL, which failed because the person who wrote it did not understand the with grant option.

First create a user to own a table:

SQL> create user data_owner identified by data_owner
  2  quota unlimited on users
  3  /


User created.

SQL> grant create session, create table to data_owner
  2  /


Grant succeeded.

SQL>

Next, create a user who will have a view on that table:

SQL> create user view_owner identified by view_owner
  2  /


User created.

SQL> grant create session, create view to view_owner
  2  /


Grant succeeded.

SQL>

Then create a test user who will have access to the view:

SQL> create user tester identified by tester
  2  /


User created.

SQL>

Create the table and allow view_owner to look at it:

SQL> conn data_owner/data_owner
Connected.
SQL> create table test_table (col1 varchar2(5))
  2  /


Table created.

SQL> grant select on test_table to view_owner
  2  /


Grant succeeded.

SQL>

Now create the view:

SQL> conn view_owner/view_owner
Connected.
SQL> create view test_view as
  2  select * from data_owner.test_table
  3  /


View created.

SQL>

Try to allow tester to see test_view. This fails because although view_owner can see test_table, it does not have permission to allow other users to look at it:

SQL> grant select on test_view to tester
  2  /
grant select on test_view to tester
                *
ERROR at line 1:
ORA-01720: grant option does not exist for
'DATA_OWNER.TEST_TABLE'


SQL>

To get round this problem, data_owner needs to add with grant option to the end of the grant statement:

SQL> conn data_owner/data_owner
Connected.
SQL> grant select on test_table to view_owner
  2  with grant option
  3  /


Grant succeeded.

SQL> conn view_owner/view_owner
Connected.
SQL> grant select on test_view to tester
  2  /


Grant succeeded.

SQL>

No comments: