General |
Data Dictionary Objects Related to Roles |
defrole$ | user$ | 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 | Privilege to administer Advanced Queuing | AQ_USER_ROLE | Deprecated | AUTHENTICATEDUSER | DBUriServlet Security | CONNECT | Contains the create session privilege (only) | CSW_USR_ROLE | Not documented | CTXAPP | Enables developers create Oracle Text indexes and index preferences, and to use PL/SQL packages. | CWM_USER | Undocumented | DATAPUMP_EXP_FULL_DATABASE | Undocumented | DATAPUMP_IMP_FULL_DATABASE | Undocumented | DBA | Example Database Administrator role. Should not be used | DELETE_CATALOG_ROLE | Allow users to delete records from the system audit table (AUD$) | DMUSER_ROLE | Undocumented | DM_CATALOG_ROLE | Undocumented | EJBCLIENT | Undocumented | EXECUTE_CATALOG_ROLE | Allow users EXECUTE privileges for packages and procedures in the data dictionary | EXP_FULL_DATABASE | Providesthe privileges required to perform full and incremental databaseexports, and includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANYPROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT,DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, andSYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE andSELECT_CATALOG_ROLE. | GATHER_SYSTEM_STATISTICS | To update the dictionary system statistics a user must have DBA privileges or the GATHER_SYSTEM_STATISTICS role. | GLOBAL_AQ_USER_ROLE | Requiredto register through LDAP using JDBC connection parameters as thisrequires the ability to write access to the connection factory entriesin the LDAP server (which requires the LDAP user to be either thedatabase itself or be granted GLOBAL_AQ_USER_ROLE). | HS_ADMIN_ROLE | Providesprivileges for DBAs who need to use the DBA role using Oracle DatabaseHeterogeneous Services to access appropriate tables in the datadictionary.
Used to protect access to theHeterogeneous Services (HS) data dictionary tables (grants SELECT) andpackages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE andEXECUTE_CATALOG_ROLE such that users with generic data dictionaryaccess also can access the HS data dictionary. | IMP_FULL_DATABASE | Providesthe privileges required to perform full database imports. Includes anextensive list of system privileges (use view DBA_SYS_PRIVS to viewprivileges) and the following roles: EXECUTE_CATALOG_ROLE andSELECT_CATALOG_ROLE.
This role is provided for convenience in using the export and import utilities. | JAVADEBUGPRIV | Grants permissions to run the Java debugger | JAVAIDPRIV | Undocumented | JAVASYSPRIV | Grants permissions for Java administrators including updating JVM-protected packages | JAVAUSERPRIV | Grants permissions for Java users such as examining properties | JAVA_ADMIN | Java administration privileges including permission to modify PolicyTable. | JAVA_DEPLOY | Undocumented | JMXSERVER | Providespermissions to start and maintain a JMX agent in a session. Theprocedure dbms_java.start_jmx_agent starts the agent in a specificsession that generally remains active for the duration of the session. | LOGSTDBY_ADMINISTRATOR | A prototype role created by default with RESOURCE, and EXECUTE on DBMS_LOGSTDBY privileges.
It is advisable to not use this role but rather to craft your own specific to your needs. Read Oracle's comments, in red with respect to RESOURCE. They apply here too. | MGMT_USER | Undocumented | OEM_ADVISOR | Required to run the Segment Advisor manually with Enterprise Manager. | OEM_MONITOR | Undocumented | OLAPI_TRACE_USER | Undocumented | OLAP_DBA | To create dimensional objects in any schema | OLAP_USER | Create dimensional objects | OLAP_XS_ADMIN | Administer OLAP data security | ORDADMIN | Afterinstalling Oracle Multimedia DICOM, the ORDADMIN role is created, withthe database system privileges required for administration of the DICOMdata model repository.
The ORDADMIN role must be assigned to the administrator of the DICOM data model repository. | OWB$CLIENT | Privileges granted to PUBLIC are available to all sessions. | OWB_DESIGNCENTER_VIEW | Undocumented | OWB_USER | With Oracle Warehouse builder enables a remote Oracle WorkFlow instance to connect to the services provided by the Control Center. | PLUSTRACE | Traditionally required to use AUTOTRACE but in 11gR1 it seems to function without this role being required. | PUBLIC | - | RECOVERY_CATALOG_OWNER | Providesprivileges for owner of the recovery catalog. Includes: CREATE SESSION,ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, andCREATE PROCEDURE | RESOURCE | Providesthe following system privileges: CREATE CLUSTER, CREATE INDEXTYPE,CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE,CREATE TRIGGER, CREATE TYPE.
This role isprovided for compatibility with previous releases of Oracle Database.You can determine the privileges encompassed by this role by queryingthe DBA_SYS_PRIVS data dictionary view.
Note: Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database. | SCHEDULER_ADMIN | Allowsthe grantee to execute the procedures of the DBMS_SCHEDULER package. Itincludes all of the job scheduler system privileges and is included inthe DBA role. | SELECT_CATALOG_ROLE | Provides SELECT privilege on objects in the data dictionary. Also provides the HS_ADMIN_ROLE privilege. | SPATIAL_CSW_ADMIN | Privilegesgranted the Catalog Services for the Web (CSW) account used by theOracle Spatial CSW cache manager to load all record type metadata, andrecord instances from the database into the main memory for the recordtypes that are cached. | SPATIAL_WFS_ADMIN | Privilegesgranted the Web Feature Service (WFS) account used by the OracleSpatial WFS cache manager to load all feature type metadata, andfeature instances from the database into main memory for the featuretypes that are cached. | WFS_USR_ROLE | Privileges granted a Web Feature Service (WFS) user | WKUSER | Privileges that must be granted to database users hosting new Oracle Ultra Search instances. | WM_ADMIN_ROLE | Containsall Workspace Manager privileges with the grant option. By default, thedatabase administrator (DBA role) is granted the WM_ADMIN_ROLE role. | XDBADMIN | Allowsthe grantee to register an XML schema globally, as opposed toregistering it for use or access only by its owner. It also lets thegrantee bypass access control list (ACL) checks when accessing OracleXML DB Repository. | XDB_SET_INVOKER | Allowsthe grantee to define invoker's rights handlers and to create or updatethe resource configuration for XML repository triggers. By default,Oracle Database grants this role to the DBA role but not to theXDBADMIN role. | XDB_WEBSERVICES | Allowsthe grantee to access Oracle Database Web services over HTTPS. However,it does not provide the user access to objects in the database that arepublic. To allow public access, you need to grant the user theXDB_WEBSERVICES_WITH_PUBLIC role. For a user to use these Web services,SYS must enable the Web service servlets. | XDB_WEBSERVICES_OVER_HTTP | Allowsthe grantee to access Oracle Database Web services over HTTP. However,it does not provide the user access to objects in the database that arepublic. To allow public access, you need to grant the user theXDB_WEBSERVICES_WITH_PUBLIC role. | XDB_WEBSERVICES_WITH_PUBLIC | Allows the grantee access to public objects through Oracle Database Web services. |
|
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; |