GRANT [SYSTEM | OBJECT PRIVILEGE] TO [USER] WITH [GRANT | ADMIN] OPTION
User Bob owns a Procedure named 'ProcessEmail'. User Alice needs access privileges on the 'ProcessEmail' procedure from Bob. Bob would grant the EXECUTE privilege on the table with GRANT OPTION as shown below:
SQL> GRANT EXECUTE ON ProcessEmail TO Alice WITH GRANT OPTION
Now Alice can grant the EXECUTE privilege on the ProcessEmail procedure to other users too. In the following command, Alice grants the EXECUTE ON ProcessEmail privilege to Charles, but not with GRANT OPTION
SQL> GRANT EXECUTE ON ProcessEmail TO Charles
With ADMIN option, a grantor can grant a System privilege to another user or role. Only a DBA or a user with the GRANT ANY PRIVILEGE system privilege can grant a system privilege to other users.
For example, a DBA grants the CREATE INDEX privilege to user 'Mike' with the admin option
SQL> GRANT CREATE INDEX TO Mike WITH ADMIN OPTION
Now user Mike can grant CREATE INDEX system privilege to another user, for example, 'Jim'.
SQL> GRANT CREATE INDEX TO Jim
Note that Jim has been granted the CREATE INDEX system privilege without the ADMIN OPTION. Because of this, Jim cannot grant this privilege to other users.
Related Code Snippets: