Oracle Roles

Version 10.2

General

defrole$ user$

dba_roles session_roles

user_application_rol
dba_role_privs
Data Dictionary Objects es
Related to Roles
role_role_privs user_role_privs

role_sys_privs v$pwfile_users

role_tab_privs

alter
System Privileges Related create drop any grant any
any
To Roles role role role
role
 Role
Description
Installation roles Name

AQ_AD
MINIST
Privilege to administer Advanced Queuing
RATOR
_ROLE

AQ_US
ER_RO Deprecated
LE

AUTHE
NTICAT
DBUriServlet Security
EDUSE
R

CONNE
Contains the create session privilege (only)
CT

CSW_U
SR_RO Not documented
LE

Enables developers create Oracle Text indexes

CTXAP
and index preferences, and to use PL/SQL
P
packages.

CWM_
Undocumented
USER

DATAP
UMP_E
XP_FU Undocumented
LL_DAT
ABASE

DATAP
UMP_I
MP_FU Undocumented
LL_DAT
ABASE

Example Database Administrator role. Should not

DBA
be used

DELET
E_CAT Allow users to delete records from the system
ALOG_ audit table (AUD$)
ROLE

DMUSE
R_ROL Undocumented
E

DM_CA
TALOG Undocumented
_ROLE

EJBCLI
Undocumented
ENT

EXECU
TE_CATAllow users EXECUTE privileges for packages
 SELECT name USER_NAMES
FROM user$
WHERE type# = 1;
Roles are treated like users
in the data dictionary
SELECT name ROLE_NAMES
FROM user$
WHERE type# = 0;

Controlling The Number Of max_enabled_roles = <integer>

Roles With An init.ora
Parameter max_enabled_roles = 100

Roles can contain system

privileges
Roles can contain object
privileges
Roles can contain roles
NOTE:
Object privileges granted through
roles do not work within
procedures, functions, and
packages. Those permissions
must be granted explicitly to
the user.

Creating Roles

CREATE ROLE <role_name>;

Create Role
CREATE ROLE read_only;

CREATE ROLE <role_name> IDENTIFIED

BY <password>;
Create Password Protected
Role
CREATE ROLE dba IDENTIFIED BY
"S0^Sorry";

Assigning Privileges And Roles To Roles

GRANT <privilege_name> TO 
Assign Privilege To A Role <role_name>;

GRANT create session TO read_only
 GRANT <role_name> TO <role_name>;

CREATE ROLE ap_clerk;

GRANT read_only TO ap_clerk;

GRANT select ON general_ledger TO 
ap_clerk;
Create A Role Heirarchy GRANT insert ON ap_master TO 
ap_clerk;
GRANT update ON ap_master TO 
ap_clerk;
GRANT insert ON ap_detail TO 
ap_clerk;
GRANT update ON ap_detail TO 
ap_clerk;

GRANT <roles and privileges> TO 
<role_name>;

CREATE ROLE ap_manager IDENTIFIED BY 
appwd;

Add Another Layer To The GRANT ap_clerk TO ap_manager;

Heirarchy
GRANT delete ON ap_master TO 
ap_manager;
GRANT delete ON ap_detail TO 
ap_manager;
GRANT select any table TO 
ap_manager;

Assigning Roles

GRANT <roles_name> TO <user_name>;

GRANT read_only TO jcline;

Assigning Roles To Users GRANT ap_clerk TO jstough;

GRANT ap_clerk TO ckeizer;
GRANT ap_clerk TO rallen;

GRANT ap_manager TO escott;

Revoking Privileges From Roles

 REVOKE <privilege_name> FROM
<role_name>;
Revoke Privilege
REVOKE select any table FROM
ap_manager;

Revoking Roles

REVOKE <role_name> FROM 
Revoke a role from a user <user_name>;

REVOKE ap_manager FROM escott;

REVOKE ALL ON <table_name>
FROM <schema_name> 
CASCADE CONSTRAINTS;
Revoke A Role And Drop
Any Invalidated Constraints
REVOKE ALL ON invoices
FROM abc 
CASCADE CONSTRAINTS;

Activating & Decactivating Roles

SET ROLE <role_name>;
Activating A Role
SET ROLE ap_clerk;

SET ROLE <role_name> IDENTIFIED BY 
<role_password>;
Activating A Password
Protected Role
SET ROLE ap_manager IDENTIFIED BY
appwd;

Activating All Roles SET ROLE all;

SET ROLE all EXCEPT <role_name>;
Activating All Roles Except
One
SET ROLE all EXCEPT ap_manager;

Can not be done on an individual 
Deactivating A Role
basis

Deactivating All Roles SET ROLE none;

Drop Role

DROP ROLE <role_name>;
Dropping A Role
DROP ROLE manager_role; 
PLUSTRACE Role

This role must be created by SYS

and grants SELECT on the
following v_$ views:
V_$SESSTAT

Creating And Assigning The

V_$STATNAME
PLUSTRACE Role Used By V_$MYSTAT
AUTOTRACE
SQL>
@c:\oracle\product\ora10\sqlplus\adm
in\plustrce.sql

GRANT plustrace TO uwclass;

Role Related Queries

SELECT name
All Roles Available In The
Database
FROM user$
WHERE type# = 0;

SELECT *
Roles Granted To A User
FROM user_role_privs;

Privileges Granted To A SELECT *

Role FROM role_sys_privs;

SELECT DISTINCT privilege

System Privileges
FROM dba_sys_privs;
Grant SELECT On All CREATE OR REPLACE PROCEDURE
Tables In A Schema
GRANT_SELECT AS

CURSOR ut_cur IS
SELECT table_name
FROM user_tables;

RetVal NUMBER;
sCursor INT;
sqlstr VARCHAR2(250);

BEGIN
FOR ut_rec IN user_tabs_cur;
LOOP
sqlstr := 'GRANT SELECT ON '||
ut_rec.table_name
 || ' TO jwc7675';
sCursor := dbms_sql.open_cursor;
dbms_sql.parse(sCursor,sqlstr, 
dbms_sql.native);

RetVal := dbms_sql.execute(sCursor); 

dbms_sql.close_cursor(sCursor);
END LOOP; 
END grant_select;

SELECT grantee, granted_role
Roles Granted To Schemas
FROM dba_role_privs;

Tables And Columns That SELECT *

Can Be Modified by a User FROM all_updatable_columns;

Other Related Topics

Autotrace

Consumer Groups

Object Privileges

Profiles

System Privileges

Users

Contact Us ? Legal Notices and Terms of Use ? Privacy Statement