| General |
| Data Dictionary Objects Related to Roles |
user$
defrole$
| dba_roles |
session_roles |
| dba_role_privs |
user_application_roles |
| role_role_privs |
user_role_privs |
| role_sys_privs |
v$pwfile_users |
| role_tab_privs |
|
|
| System Privileges Related To Roles |
alter any role
create role
drop any role
grant any role |
Installation roles |
| Role Name |
Description |
| AQ_ADMINISTRATOR_ROLE |
Advanced Queueing Administrator |
|
AQ_USER_ROLE |
Advanced Queueing User |
| AUTHENTICATEDUSER |
- |
| CONNECT |
create session privilege (only) |
| CTXAPP |
- |
| DBA |
Database Administrator |
|
DELETE_CATALOG_ROLE |
- |
| DMUSER_ROLE |
- |
| DM_CATALOG_ROLE |
- |
|
EJBCLIENT |
- |
|
EXECUTE_CATALOG_ROLE |
- |
|
EXP_FULL_DATABASE |
- |
|
GATHER_SYSTEM_STATISTICS |
- |
|
GLOBAL_AQ_USER_ROLE |
- |
|
HS_ADMIN_ROLE |
- |
|
IMP_FULL_DATABASE |
- |
| JAVADEBUGPRIV |
- |
| JAVAIDPRIV |
- |
| JAVASYSPRIV |
- |
| JAVAUSERPRIV |
- |
| JAVA_ADMIN |
- |
| JAVA_DEPLOY |
- |
| LOGSTDBY_ADMINISTRATOR |
- |
| MGMT_USER |
- |
| OEM_ADVISOR |
- |
|
OEM_MONITOR |
- |
| OLAP_DBA |
- |
| OLAP_USER |
- |
| OWBR_OWB |
- |
| OWB_OWB |
- |
| PLUSTRACE |
Run AUTOTRACE |
| PUBLIC |
- |
|
RECOVERY_CATALOG_OWNER |
- |
| RESOURCE |
Oracle provided role for
developers |
| SCHEDULER_ADMIN |
- |
|
SELECT_CATALOG_ROLE |
Select from data dictionary tables |
| WKUSER |
- |
|
WM_ADMIN_ROLE |
- |
|
XDBADMIN |
- |
|
_NEXT_USER |
- |
|
| Roles are treated like users in the data dictionary |
SELECT name USER_NAMES
FROM user$
WHERE type# = 1;
SELECT name ROLE_NAMES
FROM user$
WHERE type# = 0; |
| Controlling The Number Of Roles
With An init.ora Parameter |
max_enabled_roles = <integer> |
| max_enabled_roles = 100 |
| NOTE: |
- Roles can contain system privileges
- Roles can contain object privileges
- 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 ROLE <role_name>; |
| CREATE ROLE read_only; |
| Create Password Protected Role |
CREATE ROLE <role_name> IDENTIFIED BY
<password>; |
| CREATE ROLE dba
IDENTIFIED BY
"S0^Sorry"; |
| |
| Assigning
Privileges And Roles To Roles |
| Assign Privilege To A Role |
GRANT <privilege_name> TO <role_name>; |
| GRANT
create session TO read_only |
| Create A Role Heirarchy |
GRANT <role_name> TO <role_name>; |
CREATE ROLE ap_clerk;
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; |
| Add Another Layer To The Heirarchy |
GRANT <roles and privileges> TO
<role_name>; |
CREATE ROLE ap_manager IDENTIFIED
BY appwd;
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 |
| Assigning Roles To Users |
GRANT <roles_name> TO <user_name>; |
GRANT read_only
TO jcline;
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 <role_name>; |
| REVOKE
select any table FROM
ap_manager; |
| |
| Revoking Roles |
| Revoke a role from a user |
REVOKE <role_name> FROM <user_name>; |
| REVOKE ap_manager
FROM escott; |
| Revoke A Role And Drop Any Invalidated Constraints |
REVOKE ALL ON <table_name>
FROM <schema_name>
CASCADE CONSTRAINTS; |
REVOKE
ALL ON invoices
FROM abc
CASCADE CONSTRAINTS; |
| |
| Activating &
Decactivating Roles |
| Activating A Role |
SET ROLE <role_name>; |
| SET ROLE ap_clerk; |
| Activating A Password Protected Role |
SET ROLE <role_name> IDENTIFIED BY
<role_password>; |
| SET ROLE
ap_manager IDENTIFIED BY
appwd; |
| Activating All Roles |
SET ROLE all; |
| Activating All Roles Except One |
SET ROLE all EXCEPT <role_name>; |
| 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 <role_name>; |
| DROP ROLE manager_role; |
| |
| PLUSTRACE Role |
| Creating And Assigning The PLUSTRACE Role
Used By AUTOTRACE |
This role must be created by SYS and grants SELECT on
the following v_$ views:
- V_$SESSTAT
- V_$STATNAME
- V_$MYSTAT
|
SQL>
@c:\oracle\product\ora10\sqlplus\admin\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; |