GRANT Statement

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

Syntax:

Grant System-wide Privs:

   GRANT system_priv(s) TO grantee 
      [IDENTIFIED BY password] [WITH ADMIN OPTION]

   GRANT role TO grantee 
      [IDENTIFIED BY password] [WITH ADMIN OPTION]

   GRANT ALL PRIVILEGES TO grantee 
      [IDENTIFIED BY password] [WITH ADMIN OPTION]

Grant privs on specific objects:

   GRANT object_priv [(column, column,…)]
      ON [schema.]object
         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT ALL PRIVILEGES [(column, column,…)]
      ON [schema.]object
         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT object_priv [(column, column,…)]
      ON DIRECTORY directory_name
         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT object_priv [(column, column,…)]
      ON JAVA [RE]SOURCE [schema.]object
         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

grantee:
   user 
   role
   PUBLIC

system_privs:
   CREATE SESSION - Allows user to connect to the database
   UNLIMITED TABLESPACE  - Use an unlimited amount of any tablespace.
   SELECT ANY TABLE - Query tables, views, or mviews in any schema
   UPDATE ANY TABLE - Update rows in tables and views in any schema
   INSERT ANY TABLE - Insert rows into tables and views in any schema
   Also System Admin rights to CREATE, ALTER or DROP:
     cluster, context, database, link, dimension, directory, index,
     materialized view, operator, outline, procedure, profile, role,
     rollback segment, sequence, session, synonym, table, tablespace,
     trigger, type, user, view. (full list of system privs)

object_privs:
   SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES 
   
roles:
   SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE
   SELECT_CATALOG_ROLE,  EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
   AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - advanced queuing
   SNMPAGENT - Enterprise Manager/Intelligent Agent.
   RECOVERY_CATALOG_OWNER - rman
   HS_ADMIN_ROLE - heterogeneous services

   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

WITH HIERARCHY OPTION will grant the object privilege on all subobjects, including any created after the GRANT statement is issued.

WITH GRANT OPTION will enable the grantee to grant those object privileges to other users and roles.

"GRANT ALL PRIVILEGES…" can also be written as "GRANT ALL…"

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

The roles CONNECT, RESOURCE and DBA are now deprecated (supported only for backwards compatibility) unless you are still running Oracle 6.0 use CREATE ROLE instead. Using deprecated roles is likely to result in a database installation that is not secure. Also beware of 3rd party packages that issue GRANT CONNECT instead of GRANT CREATE SESSION.

• Create a Role
• Grant object and system priviliges to the role
• Grant the role to individual Users.

When using roles, parse performance is faster (one role vs multiple permissions)

Changes in a grant applied to a role will be immediately picked up by all logged-in users.
Changes in a grant applied directly to a user require a logoff and re-login.

Grants of UNLIMITED TABLESPACE must be direct.
Grants to users running background jobs (dbms_job.submit) have to be direct; when the job starts running no roles are active.

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,
      DROP table, DROP view, DROP procedure,DROP synonym,
      TO MyRole;

Example - granting all privileges (Pete Finnigan)

“A people that values its privileges above its principles soon loses both” ~ Dwight D. Eisenhower

Related Oracle Commands:

AUDIT
CREATE ROLE
CREATE USER
REVOKE
ORA-01031 - Insufficient privileges
To grant permissions in bulk to the objects owner by a user, see the USER_OBJECTS view (with example script.)

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