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

Term: GRANT

Definition:
In Oracle PL/SQL, the term GRANT refers to a specific privilege or permission given to a user (or a role) so that they may perform a particular action. The opposite of granting a privilege is revoking the privilege. Simply put, grants allow users the ability to do specific things.

For example, the CREATE TABLE privilege is required before a user is allowed create new database tables. Similarly, a user must be granted the DROP privilege before he or she is allowed to drop a table.


Example Usage:

Granting the CREATE TABLE privilege to a specific user:

GRANT CREATE TABLE TO John;


Granting an object privilege:

GRANT SELECT ON sales TO public;


Oracle provides a very fine-grained approach to permissions, allowing nearly any system function to be allowed or denied individually by user and/or role. Listed below are the most commonly used privileges, or grants, that may be applied or revoked. Below this section is a table listing all Oracle grants.

System Privileges
  • Create session
  • Create table
  • Create view
  • Create procedure
  • Sysdba
  • Sysoper
Object Privileges Privileges can be assigned to any of the following types of database objects:
  • Tables: all, alter, debug, delete, flashback, insert, on commit refresh, query rewrite, references, select, update
  • Views: debug, delete, insert, flashback, references, select, under, update
  • Sequence: alter, select
  • Packages, Procedures, Functions: debug, execute
  • Materialized Views: delete, flashback, insert, select, update
  • Directories: read, write
  • Libraries: execute
  • User Defined Types: debug, execute, under
  • Operators: execute
  • Indextypes: execute

Full List Of Oracle Grants

System Privilege Name Operations Authorized
Advisor Framework Privileges: All of the advisor framework privileges are part of the DBA role.
ADVISOR Access the advisor framework through PL/SQL packages such as DBMS_ADVISOR and DBMS_SQLTUNE.
ADMINISTER SQL TUNING SET Create, drop, select (read), load (write), and delete a SQL tuning set owned by the grantee through the DBMS_SQLTUNE package
ADMINISTER ANY SQL TUNING SET Create, drop, select (read), load (write), and delete a SQL tuning set owned by any user through the DBMS_SQLTUNE package
CREATE ANY SQL PROFILE Accept a SQL Profile recommended by the SQL Tuning Advisor, which is accessed through Enterprise Manager or by the DBMS_SQLTUNE package.
DROP ANY SQL PROFILE Drop an existing SQL Profile
ALTER ANY SQL PROFILE Alter the attributes of an existing SQL Profile
CLUSTERS:
CREATE CLUSTER Create clusters in the grantee's schema
CREATE ANY CLUSTER Create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER Alter clusters in any schema
DROP ANY CLUSTER Drop clusters in any schema
CONTEXTS:
CREATE ANY CONTEXT Create any context namespace
DROP ANY CONTEXT Drop any context namespace
DATABASE:
ALTER DATABASE Alter the database
ALTER SYSTEM Issue ALTER SYSTEM statements
AUDIT SYSTEM Issue AUDIT statements
DATABASE LINKS:
CREATE DATABASE LINK Create private database links in the grantee's schema
CREATE PUBLIC DATABASE LINK Create public database links
DROP PUBLIC DATABASE LINK Drop public database links
DEBUGGING:
DEBUG CONNECT SESSION Connect the current session to a debugger.
DEBUG ANY PROCEDURE Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application.

Note: Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.

DIMENSIONS:
CREATE DIMENSION Create dimensions in the grantee's schema
CREATE ANY DIMENSION Create dimensions in any schema
ALTER ANY DIMENSION Alter dimensions in any schema
DROP ANY DIMENSION Drop dimensions in any schema
DIRECTORIES
CREATE ANY DIRECTORY Create directory database objects
DROP ANY DIRECTORY Drop directory database objects
INDEXTYPES:
CREATE INDEXTYPE Create an indextype in the grantee's schema
CREATE ANY INDEXTYPE Create an indextype in any schema
ALTER ANY INDEXTYPE Modify indextypes in any schema
DROP ANY INDEXTYPE Drop an indextype in any schema
EXECUTE ANY INDEXTYPE Reference an indextype in any schema
INDEXES:
CREATE ANY INDEX Create in any schema a domain index or an index on any table in any schema
ALTER ANY INDEX Alter indexes in any schema
DROP ANY INDEX Drop indexes in any schema
JOB SCHEDULER OBJECTS: The following privileges are needed to execute procedures in the DBMS_SCHEDULER package.
CREATE JOB Create jobs, schedules, or programs in the grantee's schema
CREATE ANY JOB Create, alter, or drop jobs, schedules, or programs in any schema

Note: This extremely powerful privilege allows the grantee to execute code as any other user. It should be granted with caution.

EXECUTE ANY PROGRAM Use any program in a job in the grantee's schema
EXECUTE ANY CLASS Specify any job class in a job in the grantee's schema
MANAGE SCHEDULER Create, alter, or drop any job class, window, or window group
LIBRARIES:
CREATE LIBRARY Create external procedure or function libraries in the grantee's schema
CREATE ANY LIBRARY Create external procedure or function libraries in any schema
DROP ANY LIBRARY Drop external procedure or function libraries in any schema
MATERIALIZED VIEWS:
CREATE MATERIALIZED VIEW Create a materialized view in the grantee's schema
CREATE ANY MATERIALIZED VIEW Create materialized views in any schema
ALTER ANY MATERIALIZED VIEW Alter materialized views in any schema
DROP ANY MATERIALIZED VIEW Drop materialized views in any schema
QUERY REWRITE This privilege has been deprecated. No specify privileges are needed for a user to enable rewrite for a materialized view that references tables or views in the user's own schema.
GLOBAL QUERY REWRITE Enable rewrite using a materialized view when that materialized view references tables or views in any schema
ON COMMIT REFRESH Create a refresh-on-commit materialized view on any table in the database

Alter a refresh-on-demand materialized on any table in the database to refresh-on-commit

FLASHBACK ANY TABLE Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the DBMS_FLASHBACK procedures.
OPERATORS:
CREATE OPERATOR Create an operator and its bindings in the grantee's schema
CREATE ANY OPERATOR Create an operator and its bindings in any schema
ALTER ANY OPERATOR Modify an operator in any schema
DROP ANY OPERATOR Drop an operator in any schema
EXECUTE ANY OPERATOR Reference an operator in any schema
OUTLINES:
CREATE ANY OUTLINE Create public outlines that can be used in any schema that uses outlines
ALTER ANY OUTLINE Modify outlines
DROP ANY OUTLINE Drop outlines
PROCEDURES:
CREATE PROCEDURE Create stored procedures, functions, and packages in the grantee's schema
CREATE ANY PROCEDURE Create stored procedures, functions, and packages in any schema
ALTER ANY PROCEDURE Alter stored procedures, functions, or packages in any schema
DROP ANY PROCEDURE Drop stored procedures, functions, or packages in any schema
EXECUTE ANY PROCEDURE Execute procedures or functions, either standalone or packaged

Reference public package variables in any schema

PROFILES:
CREATE PROFILE Create profiles
ALTER PROFILE Alter profiles
DROP PROFILE Drop profiles
ROLES:
CREATE ROLE Create roles
ALTER ANY ROLE Alter any role in the database
DROP ANY ROLE Drop roles
GRANT ANY ROLE Grant any role in the database
ROLLBACK SEGMENTS:
CREATE ROLLBACK SEGMENT Create rollback segments
ALTER ROLLBACK SEGMENT Alter rollback segments
DROP ROLLBACK SEGMENT Drop rollback segments
SEQUENCES:
CREATE SEQUENCE Create sequences in the grantee's schema
CREATE ANY SEQUENCE Create sequences in any schema
ALTER ANY SEQUENCE Alter any sequence in the database
DROP ANY SEQUENCE Drop sequences in any schema
SELECT ANY SEQUENCE Reference sequences in any schema
SESSIONS:
CREATE SESSION Connect to the database
ALTER RESOURCE COST Set costs for session resources
ALTER SESSION Issue ALTER SESSION statements
RESTRICTED SESSION Logon after the instance is started using the SQL*Plus STARTUP RESTRICT statement
SNAPSHOTS: See MATERIALIZED VIEWS
SYNONYMS:
CREATE SYNONYM Create synonyms in the grantee's schema
CREATE ANY SYNONYM Create private synonyms in any schema
CREATE PUBLIC SYNONYM Create public synonyms
DROP ANY SYNONYM Drop private synonyms in any schema
DROP PUBLIC SYNONYM Drop public synonyms
TABLES:

Note: For external tables, the only valid privileges are CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, and SELECT ANY TABLE.

CREATE TABLE Create tables in the grantee's schema
CREATE ANY TABLE Create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
ALTER ANY TABLE Alter any table or view in any schema
BACKUP ANY TABLE Use the Export utility to incrementally export objects from the schema of other users
DELETE ANY TABLE Delete rows from tables, table partitions, or views in any schema
DROP ANY TABLE Drop or truncate tables or table partitions in any schema
INSERT ANY TABLE Insert rows into tables and views in any schema
LOCK ANY TABLE Lock tables and views in any schema
SELECT ANY TABLE Query tables, views, or materialized views in any schema
FLASHBACK ANY TABLE Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the DBMS_FLASHBACK procedures.
UPDATE ANY TABLE Update rows in tables and views in any schema
TABLESPACES:
CREATE TABLESPACE Create tablespaces
ALTER TABLESPACE Alter tablespaces
DROP TABLESPACE Drop tablespaces
MANAGE TABLESPACE Take tablespaces offline and online and begin and end tablespace backups
UNLIMITED TABLESPACE Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, then the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.
TRIGGERS:
CREATE TRIGGER Create a database trigger in the grantee's schema
CREATE ANY TRIGGER Create database triggers in any schema
ALTER ANY TRIGGER Enable, disable, or compile database triggers in any schema
DROP ANY TRIGGER Drop database triggers in any schema
ADMINISTER DATABASE TRIGGER Create a trigger on DATABASE. You must also have the CREATE TRIGGER or CREATE ANY TRIGGER system privilege.
TYPES:
CREATE TYPE Create object types and object type bodies in the grantee's schema
CREATE ANY TYPE Create object types and object type bodies in any schema
ALTER ANY TYPE Alter object types in any schema
DROP ANY TYPE Drop object types and object type bodies in any schema
EXECUTE ANY TYPE Use and reference object types and collection types in any schema, and invoke methods of an object type in any schema if you make the grant to a specific user. If you grant EXECUTE ANY TYPE to a role, then users holding the enabled role will not be able to invoke methods of an object type in any schema.
UNDER ANY TYPE Create subtypes under any nonfinal object types.
USERS:
CREATE USER Create users. This privilege also allows the creator to:
  • Assign quotas on any tablespace

  • Set default and temporary tablespaces

  • Assign a profile as part of a CREATE USER statement

ALTER USER Alter any user. This privilege authorizes the grantee to:
  • Change another user's password or authentication method

  • Assign quotas on any tablespace

  • Set default and temporary tablespaces

  • Assign a profile and default roles

DROP USER Drop users
VIEWS:
CREATE VIEW Create views in the grantee's schema
CREATE ANY VIEW Create views in any schema
DROP ANY VIEW Drop views in any schema
UNDER ANY VIEW Create subviews under any object views
FLASHBACK ANY TABLE Issue a SQL Flashback Query on any table, view, or materialized view in any schema. This privilege is not needed to execute the DBMS_FLASHBACK procedures.
MISCELLANEOUS:
ANALYZE ANY Analyze any table, cluster, or index in any schema
AUDIT ANY Audit any object in any schema using AUDIT schema_objects statements
COMMENT ANY TABLE Comment on any table, view, or column in any schema
EXEMPT ACCESS POLICY Bypass fine-grained access control

Caution: This is a very powerful system privilege, as it lets the grantee bypass application-driven security policies. Database administrators should use caution when granting this privilege.

FORCE ANY TRANSACTION Force the commit or rollback of any in-doubt distributed transaction in the local database

Induce the failure of a distributed transaction

FORCE TRANSACTION Force the commit or rollback of the grantee's in-doubt distributed transactions in the local database
GRANT ANY OBJECT PRIVILEGE Grant any object privilege

Revoke any object privilege that was granted by the object owner or by some other user with the GRANT ANY OBJECT PRIVILEGE privilege

GRANT ANY PRIVILEGE Grant any system privilege
RESUMABLE Enable resumable space allocation
SELECT ANY DICTIONARY Query any data dictionary object in the SYS schema. This privilege lets you selectively override the default FALSE setting of the O7_DICTIONARY_ACCESSIBILITY initialization parameter.
SYSDBA Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, back up, or change character set

CREATE DATABASE

ARCHIVELOG and RECOVERY

CREATE SPFILE

Includes the RESTRICTED SESSION privilege

SYSOPER Perform STARTUP and SHUTDOWN operations

ALTER DATABASE: open, mount, or back up

ARCHIVELOG and RECOVERY

CREATE SPFILE

Includes the RESTRICTED SESSION privilege

CONNECT, RESOURCE, and DBA These roles are provided for compatibility with previous versions of Oracle Database. You can determine the privileges encompassed by these roles by querying the DBA_SYS_PRIVS data dictionary view.

Note: Oracle recommends that you design your own roles for database security rather than relying on these roles. These roles may not be created automatically by future versions of Oracle Database.

DELETE_CATALOG_ROLE EXECUTE_CATALOG_ROLE SELECT_CATALOG_ROLE These roles are provided for accessing data dictionary views and packages.
EXP_FULL_DATABASE
IMP_FULL_DATABASE

These roles are provided for convenience in using the import and export utilities.
AQ_USER_ROLE
AQ_ADMINISTRATOR_ROLE

You need these roles to use Oracle Advanced Queuing.
SNMPAGENT This role is used by the Enterprise Manager Intelligent Agent.
RECOVERY_CATALOG_OWNER You need this role to create a user who owns a recovery catalog.

Oracle Database Predefined Roles
Predefined Role Purpose
HS_ADMIN_ROLE A DBA using Oracle Database heterogeneous services needs this role to access appropriate tables in the data dictionary.
SCHEDULER_ADMIN This role allows the grantee to execute the procedures of the DBMS_SCHEDULER package. It includes all of the job scheduler system privileges and is included in the DBA role.



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 302 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?