Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL OPTION Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: OPTION

Definition:
In Oracle PL/SQL, OPTION is a reserved word used in the Data Control Language. It is used to grant extended privileges to a user. Extension options available with Oracle are ADMIN and GRANT.

Using the GRANT option, a user can grant an Object privilege to another user or to PUBLIC. The grantor must meet one or more of the below criteria. The grantor

  1. must be object owner,
  2. If not the object owner, then the grantor must have object access with GRANT OPTION from the user.
  3. must have GRANT ANY OBJECT PRIVILEGE system privilege and an object privilege on the object

Example Syntax:

GRANT [SYSTEM | OBJECT PRIVILEGE] TO [USER] WITH [GRANT | ADMIN] OPTION


Example Usage

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:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 218 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?