With Grant Option

This post 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>