GRANT Statement

Grant privileges to a user (or to a user role)

Syntax:

Roles:
   GRANT role TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

System Privs:
   GRANT system_priv(s) TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

   GRANT ALL TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

Objects:
   GRANT object_priv [(column, column,...)]
      ON [schema.]object
         TO [user], [role], [PUBLIC] [WITH GRANT OPTION] 

   GRANT ALL [(column, column,...)]
      ON [schema.]object
         TO [user], [role], [PUBLIC] [WITH GRANT OPTION]

   GRANT object_priv [(column, column,...)]
      ON DIRECTORY directory_name
         TO [user], [role], [PUBLIC] [WITH GRANT OPTION] 

   GRANT object_priv [(column, column,...)]
      ON JAVA [RE]SOURCE [schema.]object
         TO [user], [role], [PUBLIC] [WITH GRANT OPTION] 

key:
   object_privs
   ALTER, DELETE, EXECUTE,
   INDEX, INSERT, REFERENCES, SELECT, UPDATE

   system_privs
   ALTER ANY INDEX, BECOME USER, CREATE TABLE, DROP ANY VIEW
   RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE
   plus too many others to list here

   roles
   Standard Oracle roles -  
   EXP_FULL_DATABASE, IMP_FULL_DATABASE, OSOPER, OSDBA
   plus any user defined roles you have available

notes:
Several Object_Privs can be assigned in a single GRANT statement
e.g.
GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma

"GRANT ALL..." may also be written as "GRANT ALL PRIVILEGES..."

"A people that values its privileges above it's principles soon loses both" - Dwight D. Eisenhower

Related Commands:

AUDIT
CREATE ROLE
REVOKE

Related Views:

 DBA_COL_PRIVS        ALL_COL_PRIVS        USER_COL_PRIVS       COLUMN_PRIVILEGES 
                      ALL_COL_PRIVS_MADE   USER_COL_PRIVS_MADE 
                      ALL_COL_PRIVS_RECD   USER_COL_PRIVS_RECD 

 DBA_ROLE_PRIVS                            USER_ROLE_PRIVS      ROLE_ROLE_PRIVS 

 DBA_SYS_PRIVS                             USER_SYS_PRIVS       ROLE_SYS_PRIVS 
                                                                SESSION_PRIVS 

 DBA_TAB_PRIVS        ALL_TAB_PRIVS        USER_TAB_PRIVS       TABLE_PRIVILEGES 
                                                                ROLE_TAB_PRIVS 
                      ALL_TAB_PRIVS_MADE   USER_TAB_PRIVS_MADE 
                      ALL_TAB_PRIVS_RECD   USER_TAB_PRIVS_RECD 




Back to the Top

Simon Sheppard
SS64.com