my notes...

Note: Initialization parameter 07_DIRECTORY_ACCESSIBILITY is used to prevent regular users with 'UPDATE ANY TABLE' privilege from accessing the data dictionary.

[, {system_privilege|role}]...
TO user|role|public [, {user | role, PUBLIC}]...
[WITH ADMIN OPTION];

[, obj_privilege [(column_list)]]...
| ALL [PRIVILEGES]
ON [schema.]object
TO {user|role|PUBLIC} [, {user | role|PUBLIC}]
[WITH GRANT OPTION]

[, {system_privilege|role}]...
FROM user|role|public [, {user | role, PUBLIC}]...
;

[, obj_privilege ]...
| ALL [PRIVILEGES]}
ON [schema.]object
FROM {user|role|PUBLIC} [, {user | role|PUBLIC}]...
[CASCADE CONSTRAINTS]

Admin functions

alter system, audit system, audit any, alter database, analyze any, SYSDBA, SYSOPER, grant any privilege.

Database access

Create session, alter session, restricted session

Tablespaces

Create | alter | manage | drop | unlimited tablespace.

Note : You can't grant unlimited tablespace to a role.

Users

Create user, become user, alter user, drop user

Undo Segments

Create rollback segment, alter rollback segment, drop rollback segment.

Note : These privileges are not needed if automatic undo management is used.

Tables

Create table, create | alter | backup | insert | update | delete any table

Note :

• Create privilege also enables drop privilege.

• Create table also enables creation of indexes & run analyze command on the table.

• To truncate a table you must have drop any table privilege.

Clusters

Create cluster, create any cluster, alter any cluster, drop any cluster

Note :create privilege also enables u to alter or drop those clusters.

Indexes

Create any index, alter any index, drop any index.

Synonyms

Create synonym, create | drop any synonym, create | drop public synonym

Views

Create view, create any view, drop any view

Note : create privilege also enables u to alter or drop views that you own.

Sequences

Create sequence, create | alter | drop | select any sequence

Note :Create privilege also enables you to drop the sequences you own.

Database links

Create database link, create | drop public database link

Note :Create privilege also enables you to drop private database links that you own.

(These are objects in Oracle which allows you to reference tables in another database without need to make a separate connection.)

Roles

Create role, create| drop | grant | alter any role

Transactions

Force transaction, force any transaction

(These privileges are used to resolve in-doubt, distributed transactions.)

PL/SQL

Create procedure, create | alter | drop | execute any procedure

Note : create privilege also enables u to alter and drop PL/SQL blocks that you own.

Triggers

Create trigger, create | alter | drop any trigger

Note : create privilege also enables u to alter and drop triggers that you own.

Profiles

Create | alter | drop profile, alter resource cost

(These are objects in Oracle that allows you to impose limits on resources for users.)

Snapshots and Materialized views

Create snapshot, create | alter | drop any snapshot

(Snapshots are objects in Oracle that enables you to replicate data from a table in one database to a copy of the table in another.)

Directories

Create | drop any directory

(Directories refer to directories on the host machine and are used to identify a directory that contains Objects Oracle keeps track of that are external to Oracle, such as objects of BFILE type.)

Types

Create type, create | alter | drop | execute any type

Note : create privilege also enables u to alter and drop types that you own.

(Types are the user defined types you can create in the Oracle Objects Option.)

Libraries

Create library, create | alter | drop | execute any library

(A library is an object that enables you to reference a set of procedures external to Oracle. Currently on C procedures are supported.)

• STARTUP, SHUTDOWN

• ALTER DATABASE OPEN | MOUNT

• ALTER DATABASE BACKUP CONTROLFILE

• RECOVER DATABASE

• ALTER DATABASE ARCHIVELOG

• RESTRICTED SESSION?

• All SYSOPER privileges

• CREATE DATABASE

• ALTER DATABASE [BEGIN|END] BACKUP

• RESTRICTED SESSION

• RECOVER DATABASE

Select

Object privilege to access data in a table, sequence, view or snapshot.

insert

Object privilege to insert data into a table or in a view in some cases.

update

Object privilege to update data into a table or view.

delete

Object privilege to delete data into a table or view.

alter

Object privilege to alter the definition of a table & sequence only (view, procedure?).

Note : The alter privileges on all other database objects is considered System privileges.

index

Object privilege to create an index on a table (or View) already defined.

references

Object privilege to reference data in the referenced table while defining (create or alter) a table with a foreign key constraint.

execute

Object privilege to run a stored procedure or function.

SESSION_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

USER_ROLE_PRIVS

:

:

:

:

List of privileges that are currently available to the user

System privileges granted to roles

Table privileges granted to roles

Role accessible to user

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

USER_COL_PRIVS_MADE

USER_COL_PRIVS_RECD

USER_SYS_PRIVS

:

:

:

:

:

Object privileges granted on the user's Objects

Object privileges granted to the user

Object privileges granted on the columns of user's Objects

Object privileges granted to the user on specific columns

System privileges granted to the user

DBA_TAB_PRIVS

DBA_COL_PRIVS

DBA_SYS_PRIVS

:

:

:

Lists all granted on all objects in the database

Describes all object-column grants in the database

System privileges granted to the users & roles