Oracle Roles

Oracle Roles Version 10.2

General

Data Dictionary Objects Related to Roles

defrole$

user$

dba_roles

session_roles

dba_role_privs

user_application_rol es

role_role_privs user_role_privs role_sys_privs v$pwfile_users role_tab_privs

System Privileges Related To Roles

alter create any role role

drop any role

grant any role

Installation roles

Role Description 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 DBA

Example Database Administrator role. Should not 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

Roles are treated like users in the data dictionary

Controlling The Number Of Roles With An init.ora Parameter

SELECT name USER_NAMES FROM user$ WHERE type# = 1; SELECT name ROLE_NAMES FROM user$ WHERE type# = 0; max_enabled_roles = max_enabled_roles = 100 Roles can contain system privileges Roles can contain object privileges

NOTE:

Roles can contain roles 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

Create Password Protected Role

CREATE ROLE ; CREATE ROLE read_only; CREATE ROLE IDENTIFIED BY <password>; CREATE ROLE dba IDENTIFIED BY "S0^Sorry";

Assigning Privileges And Roles To Roles GRANT <privilege_name> TO  Assign Privilege To A Role ; GRANT create session TO read_only

GRANT TO ; CREATE ROLE ap_clerk;

Create A Role Heirarchy

GRANT read_only TO ap_clerk; GRANT select ON general_ledger TO  ap_clerk; 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  TO  ; CREATE ROLE ap_manager IDENTIFIED BY  appwd;

Add Another Layer To The Heirarchy

GRANT ap_clerk TO ap_manager; 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  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

REVOKE <privilege_name> FROM ; REVOKE select any table FROM ap_manager;

Revoking Roles REVOKE  FROM  Revoke a role from a user <user_name>; REVOKE ap_manager FROM escott;

Revoke A Role And Drop Any Invalidated Constraints

REVOKE ALL ON  FROM <schema_name>  CASCADE CONSTRAINTS; REVOKE ALL ON invoices FROM abc  CASCADE CONSTRAINTS;

Activating & Decactivating Roles Activating A Role

Activating A Password Protected Role

Activating All Roles Activating All Roles Except One

SET ROLE ; SET ROLE ap_clerk; SET ROLE  IDENTIFIED BY  ; SET ROLE ap_manager IDENTIFIED BY appwd; SET ROLE all; SET ROLE all EXCEPT ; SET ROLE all EXCEPT ap_manager;

Deactivating A Role

Can not be done on an individual  basis

Deactivating All Roles

SET ROLE none;

Drop Role Dropping A Role

DROP 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 PLUSTRACE Role Used By AUTOTRACE

V_$STATNAME V_$MYSTAT SQL> @c:\oracle\product\ora10\sqlplus\adm in\plustrce.sql GRANT plustrace TO uwclass;

Role Related Queries All Roles Available In The Database

SELECT name FROM user$ WHERE type# = 0;

Roles Granted To A User

SELECT * FROM user_role_privs;

Privileges Granted To A Role

SELECT * FROM role_sys_privs;

System Privileges

SELECT DISTINCT privilege FROM dba_sys_privs;

Grant SELECT On All Tables In A Schema

CREATE OR REPLACE PROCEDURE 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; Roles Granted To Schemas

SELECT grantee, granted_role FROM dba_role_privs;

Tables And Columns That Can Be Modified by a User

SELECT * 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