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…" may 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;

Related 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