GRANTS WITH GRANT OPTION: The transitive property granting privileges

 

Grant command is used to grant to user's databases generic privileges or permissions over objects.

It's syntax is: 
GRANT [system_privileges | roles] 
TO [user | role | PUBLIC] {WITH GRANT OPTION} 

to grant generic privileges

GRANT [ALL {PRIVILEGES} | SELECT | INSERT | UPDATE | DELETE] ON object 
TO [user | role | PUBLIC] {WITH GRANT OPTION} 

to grant privileges on objects

The syntax is very simple, and privileges can be granted by the user owner of the objects, or by a user with privileges to grant permissions on objects from other users (DBA's).

I want to comment how to use the option WITH GRANT OPTION, which allows the recently 'granted' user to grant these privileges to other users.

I will show how it works with an example: 
Imagine you have a user 'U_VIEW', which creates a view with a query over objects of another user 'U_DATA'. So, 'U_DATA' only have to grant select privileges to 'U_VIEW' over these objects. 

U_DATA: 
SQL> GRANT SELECT ON TABLE TO U_VIEW;

The problem would come if we have a third user 'U_QUERY' that wants to use this view. You might think that if 'U_VIEW' grants access permissions to 'U_QUERY' over the view, and 'U_DATA' grants to 'U_QUERY' access permissions to the objects accessed by the query all will be all right: 

U_VIEW: 
SQL> GRANT SELECT ON VIEW TO U_QUERY; 

U_DATA: 
SQL> GRANT SELECT ON TABLE TO U_QUERY;

But no, because to access these data through the view must be the view owner who grants permission to a third party. We could say that privileges granting do not meet the transitive property.

If we only make previous grants, when 'U_QUERY' starts a select over the VIEW, the database will return an ORA-00942. To avoid this error, the owner of the objects (or a DBA user) has to grant to the other user privileges on those objects, but with the option of granting this privileges in turn to other users:  

U_DATA: 
SQL> GRANT SELECT ON TABLE TO U_VIEW WITH GRANT OPTION; 
U_VIEW: 
SQL> GRANT SELECT ON U_DATA.TABLE TO U_QUERY;

U_QUERY: 
SQL> SELECT * FROM VIEW;

And that's all, now U_QUERY can read data from the view.

Average: 3.6 (7 votes)