Users, roles and privileges in Oracle

Privileges

A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges:system privileges and object privileges. A privileges can be assigned to a user or a role

The set of privileges is fixed, that is, there is no SQL statement likecreate privilege xyz...

System privileges

There are quite a few system privileges: in Oracle 9.2, we count 157 of them, and 10g has even 173. Those can be displayed with

select name from system_privilege_map

Executing this statement, we find privileges like create session,drop user, alter database, see system privileges.

System privileges can be audited.

Arguably, the most important system privileges are:

• create session (A user cannot login without this privilege. If he tries, he gets an ORA-01045).
• create table
• create view
• create procedure
• sysdba
• sysoper

Object privileges

privileges can be assigned to the following types of database objects:

• Tables
  select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
• Views
  select, insert, update, delete, under, references, flashback, debug
• Sequence
  alter, select
• Packeges, Procedures, Functions (Java classes, sources...)
  execute, debug
• Materialized Views
  delete, flashback, insert, select, update
• Directories
  read, write
• Libraries
  execute
• User defined types
  execute, debug, under
• Operators
  execute
• Indextypes
  execute

For a user to be able to access an object in another user's schema, he needs the according object privilege.

Object privileges can be displayed using all_tab_privs_made oruser_tab_privs_made.

Public

If a privilege is granted to the special role public, this privilege can be executed by all other users. However, sysdba cannot be granted to public.

Users

to be finished ...

Roles

Predefined Roles

Along with the installation, more exactly with the creation of an oracle database, Oracle creates predefined roles. These are:

• connect, resource, dba
  These might not be created anymore in future versions of Oracle.
  Oracle 9.2 grants create session, alter session, create synonym, create view, create database link, create table,create cluster and create sequence to connect.
  It also grants create table , create cluster, create sequence,create trigger create procedure, create type, create indextype and create operator to resource.
  The role dba gets basically everything and that with admin option.
• delete_catalog_role, execute_catalog_role, select_catalog_role
  Accessing data dictionary views (v$ views and static dictionary views)
• exp_full_database, imp_full_database
  This role is needed to export objects found in another user's schema.
• aq_user_role, aq_administrator_role, global_aq_user_role(?)
• logstdby_administrator
• snmpagent
• recovery_catalog_owner
• hs_admin_role
• oem_monitor, oem_advisor
• scheduler_admin
• gather_system_statistics
• plustrace
• xdbadmin
• xdbwebservices
• ctxapp

Assigning privileges to users and roles

A privilege can be assigned to a user with the grant sql statment. On the other hand, revoke allows to take away such privileges from users and roles.

Oracle stores the granted privileges in its data dictionary.

Displaying the relationship between users, roles and privileges

Use this script to recursively list users, granted roles and privileges.

http://psoug.org/reference/object_privs.html