Oracle | Roles

Oracle Roles Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Notes

The maximum number of roles that can be enabled for a single session is 148. A smaller maximum value can be set with the init parameter MAX_ENABLED_ROLES
A common role must have a name that begins with C##
Roles can contain system privileges
Roles can contain object privileges
Roles can contain roles
Object privileges granted through roles do not work within PL/SQL objectsw unless those permissions must be granted explicitly to the user by defining the object with invoker (CURRENT_USER) rights

For guidance on Roles, Granted Roles, and Role Privileges that violate the Principle of Least Privilege (NIST 800-53) and Zero Trust Architecture (NIST 800-207) see the Security Concerns section at page bottom. As granted by Oracle at the time of installation there are no issues but any additional grants made by DBAs or applications should be an area of intense focus and concern.

Data Dictionary Objects

CDB_ROLES	ROLE_ROLE_PRIVS	USER_APPLICATION_ROLES
CDB_ROLE_PRIVS	ROLE_SYS_PRIVS	USER$
DBA_ROLES	ROLE_TAB_PRIVS	USER_ROLE_PRIVS
DBA_ROLE_PRIVS	SESSION_ROLES	V$PWFILE_USERS
DEFROLE$

Installation Roles: Roles created in the database at the time of installation

The integers in the parentheses indicate the number of (System Privs / Obj Privileges / Roles) granted to the role.

ACCHK_READ (0,9,0)	JAVAUSERPRIV (0,0,0)
ADM_PARALLEL_EXECUTE_TASK (0,4,0)	JAVA_ADMIN (0,0,0)
APPLICATION_TRACE_VIEWER (0,7,0)	JMXSERVER (0,0,0)
AQ_ADMINISTRATOR_ROLE (6,26,0)	LBAC_DBA (0,1,0)
AQ_USER_ROLE (0,5,0)	LOGSTDBY_ADMINISTRATOR (0,2,1)
AUDIT_ADMIN (2,50,0)	MAINTPLAN_APP (0,1,0)
AUDIT_VIEWER (0,34,0)	NOTIFICATIONS_ADMIN (0,1,0)
AUTHENTICATEDUSER (0,0,0)	NOTIFICATIONS_USER (0,1,0)
AVTUNE_PKG_ROLE (5,53,1)	OEM_ADVISOR (3,0,0)
BDSQL_ADMIN (0,4,0)	OEM_MONITOR (5,15,1)
BDSQL_USER (0,1,0)	OGG_APPLY (9,24,0)
CAPTURE_ADMIN (0,49,0)	OGG_APPLY_PROCREP (CREATE_ANY_INDEX,17,1)
CDB_DBA (SET CONTAINER,3,0,0)	OGG_CAPTURE (12,34,0)
CONNECT (2,0,0)	OGG_SHARED_CAPTURE (3,6,1)
CTXAPP (CREATE SEQUENCE,0,0)	OLAP_DBA(27,9,0)
DATAPUMP_EXP_FULL_DATABASE (2,2,1)	OLAP_USER (8,9,0)
DATAPUMP_IMP_FULL_DATABASE (15,4,2)	OLAP_XS_ADMIN (0,3,0)
DBA (256,253,19)	OPTIMIZER_PROCESSING_RATE (0,4,0)
DBFS_ROLE (0,27,0)	OSAK_ADMIN_ROLE (3,9,0)
DBJAVASCRIPT (0,0,0)	PDB_DBA (0,53,0)
DBMS_MDX_INTERNAL (0,13,0)	PGX_SERVER_GET_INFO (0,0,0)
DB_DEVELOPER_ROLE (24,4,2)	PGX_SERVER_MANAGE (0,0,0)
DGPDB_ROLE (0,89,0)	PGX_SERVER_ADD_PUBLISHED_GRAPH (0,0,0)
DV_ACCTMGR (7,2,1)	PGX_SERVER_COMPILE_ALGORITHM (0,0,0)
DV_ADMIN (0,45,1)	PGX_SERVER_CREATE (0,0,0)
DV_AUDIT_CLEANUP (0,6,0)	PGX_SERVER_GET_PUBLISHED_GRAPH (0,0,0)
DV_DATAPUMP_NETWORK_LINK (0,3,0)	PGX_SERVER_MODIFY_MODEL (0,0,0)
DV_GOLDENGATE_ADMIN (0,0,0)	PGX_SERVER_NEW_GRAPH (0,0,0)
DV_GOLDENGATE_REDO_ACCESS (0,0,0)	PGX_SERVER_READ_MODEL (0,0,0)
DV_MONITOR (0,46,0)	PPLB_ROLE (0,276,0)
DV_OWNER (0,39,11)	PROVISIONER (0,0,0)
DV_PATCH_ADMIN (0,0,0)	RDFCTX_ADMIN (0,0,0)
DV_POLICY_OWNER (0,10,0)	RECOVERY_CATALOG_OWNER (12,2,0)
DV_SECANALYST (0,111,0)	RECOVERY_CATALOG_OWNER_VPD (5,2,2)
DV_STREAMS_ADMIN (0,39,0)	RECOVERY_CATALOG_USER (0,1,0)
DV_XSTREAM_ADMIN (0,0,0)	RESOURCE (15,0,1)
EJBCLIENT (0,0,0)	SAGA_ADM_ROLE (0,9,1)
EXECUTE_CATALOG_ROLE (0,115,1)	SAGA_CONNECT_ROLE (0,1,0)
EXP_FULL_DATABASE (17,71,2)	SAGA_PARTICIPANT_ROLE (0,1,1)
GATHER_SYSTEM_STATISTICS (0,8,0)	SCHEDULER_ADMIN (8,0,0)
GDS_CATALOG_SELECT (0,45,0)	SELECT_CATALOG_ROLE (0,5241,1)
GGSYS_ROLE (0,0,0)	SHARDED_SCHEMA_OWNER (0,2,0)
GLOBAL_AQ_USER_ROLE (0,0,0)	SODA_APP (0,3,0)
GRAPH_ADMINISTRATOR (0,0,3)	SQL_FIREWALL_ADMIN (1,1,1)
GRAPH_DEVELOPER (0,0,5)	SQL_FIREWALL_VIEWER (0,12,0)
GRAPH_USER (0,0,2)	SYSUMF_ROLE (2,874,0)
GSMADMIN_ROLE (ALTER SYSTEM,110,3)	WM_ADMIN_ROLE (0,13,0)
GSMCATUSER_ROLE (CREATE_SESSION,52,3)	XDBADMIN (0,11,0)
GSMROOTUSER_ROLE (0,4,1)	XDB_SET_INVOKER (0,0,0)
GSMUSER_ROLE (5,73,2)	XDB_WEBSERVICES (0,0,0)
GSM_POOLADMIN_ROLE (0,37,1)	XDB_WEBSERVICES_OVER_HTTP (0,0,0)
HS_ADMIN_EXECUTE_ROLE (0,1,0)	XDB_WEBSERVICES_WITH_PUBLIC (0,0,0)
HS_ADMIN_ROLE (0,0,2)	XSTREAMS_APPLY (5,19,1)
HS_ADMIN_SELECT_ROLE (0,14,0)	XSTREAMS_CAPTURE (6,20,1)
IMP_FULL_DATABASE (84,27,2)	XS_CACHE_ADMIN (0,13,0)
JAVADEBUGPRIV (2,0,0)	XS_CONNECT (CREATE_SESSION,0,0)
JAVAIDPRIV (0,0,0)	XS_NAMESPACE_ADMIN (0,0,0)
JAVASYSPRIV (0,0,1)	XS_SESSION_ADMIN (0,1,0)

Installation roles not found in USER$

ADVISOR

Related System Privileges

ALTER ANY ROLE

DROP ANY ROLE

GRANT ANY ROLE

Page Sections

Activate-Deactivate Role	Create CDB Role	Installation Roles
Assign-Revoke Role Privileges	Create PDB Role	PLUSTRACE Role
Assign-Revoke User Role	Drop Role	Role Related Queries

Creating CDB Roles

Create Role CREATE ROLE <role_name> [NOT IDENTIFIED];

CREATE ROLE read_only;

      or

      CREATE ROLE read_only NOT IDENTIFIED;

Create Password Protected Role CREATE ROLE <role_name> IDENTIFIED BY <password>;

CREATE ROLE read_only IDENTIFIED BY "S0^Sorry";

Create Package Protected Role CREATE ROLE <role_name> IDENTIFIED USING <schema_name>.<package_name>.<procedure_name>;

conn c##uwclass/c##uwclass@pdbdev

      

      CREATE OR REPLACE PACKAGE db_security AUTHID CURRENT_USER IS

        PROCEDURE enable_role;

      END db_security;

      /

      

      CREATE OR REPLACE PACKAGE BODY db_security IS

       PROCEDURE enable_role IS

       BEGIN

         dbms_session.set_role('read_only');

       END enable_role;

      END db_security;

      /

      

      col schema format a20

      col package format a30

      

      SELECT * FROM dba_application_roles;

      

      CREATE ROLE read_only IDENTIFIED USING db_security;

      

      SELECT * FROM dba_application_roles;

      

      GRANT select on uwclass.airplanes TO  read_only;

      GRANT execute ON db_security TO  uwclass;

      GRANT x2dba TO scott;

      ALTER USER scott DEFAULT ROLE connect, resource;

      

      conn scott/tiger

      

      SELECT * FROM user_role_privs;

      

      SELECT * FROM session_roles;

      

      -- this time it will fail

      SELECT * FROM uwclass.airplanes;

      

      exec uwclass.db_security.enable_role;

      

      -- this time it will succeed

      SELECT * FROM uwclass.airplanes;

Identified Externally CREATE ROLE <role_name> IDENTIFIED EXTERNALLY;

CREATE ROLE dba IDENTIFIED EXERNALLY;

Identified Globally CREATE ROLE <role_name> IDENTIFIED GLOBALLY;

CREATE ROLE dba IDENTIFIED GLOBALLY;

Creating PDB Roles

Create Role CREATE ROLE <role_name> [NOT IDENTIFIED] CONTAINER = <ALL | CURRENT>;

CREATE ROLE read_only CONTAINER = CURRENT;

      or

      CREATE ROLE c##read_only NOT IDENTIFIED;

Create Password Protected Role CREATE ROLE <role_name> IDENTIFIED BY <password>;

CREATE ROLE dba IDENTIFIED BY "S0^Sorry";

Create Package Protected Role CREATE ROLE <role_name> IDENTIFIED USING <schema_name>.<package_name>.<procedure_name>;

CREATE OR REPLACE PACKAGE db_security AUTHID CURRENT_USER IS

       PROCEDURE enable_role;

      END db_security;

      /

      

      CREATE OR REPLACE PACKAGE BODY db_security IS

       PROCEDURE enable_role IS

       BEGIN

         dbms_session.set_role('x2dba');

       END enable_role;

      END db_security;

      /

      

      SELECT * FROM dba_application_roles;

      

      CREATE ROLE x2dba IDENTIFIED USING uwclass.db_security;

      

      SELECT * FROM dba_application_roles;

      

      GRANT select on uwclass.airplanes TO x2dba;

      GRANT execute ON db_security TO scott;

      GRANT x2dba TO scott;

      ALTER USER scott DEFAULT ROLE connect, resource;

      

      conn scott/tiger

      

      SELECT * FROM user_role_privs;

      

      SELECT * FROM session_roles;

      

      -- this time it will fail

      SELECT * FROM uwclass.airplanes;

      

      exec uwclass.db_security.enable_role;

      

      -- this time it will succeed

      SELECT * FROM uwclass.airplanes;

Identified Externally CREATE ROLE <role_name> IDENTIFIED EXTERNALLY;

CREATE ROLE dba IDENTIFIED EXERNALLY;

Identified Globally CREATE ROLE <role_name> IDENTIFIED GLOBALLY;

CREATE ROLE dba IDENTIFIED GLOBALLY;

Assign and Revoke Role Privileges

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 Hierarchy 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;

Revoke Privilege REVOKE <privilege_name> FROM <role_name>;

REVOKE select any table FROM ap_manager;

Assign and Revoke User Roles

Assigning Roles To Users GRANT <roles_name> TO <user_name>;

GRANT read_only TO jbiden CONTAINER=CURRENT;

      

      GRANT ap_clerk  TO jstough CONTAINER=CURRENT;

      GRANT ap_clerk  TO ckeizer CONTAINER=CURRENT;

      GRANT ap_clerk  TO rallen CONTAINER=CURRENT;

      

      GRANT ap_manager  TO escott CONTAINER=CURRENT;

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

Activate A Session 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;

Activate All Available Roles SET ROLE all;

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

Deactivate All Roles SET ROLE none;

SET ROLE none;

Drop Role

Drop A Role DROP ROLE <role_name>;

DROP ROLE manager_role;

Code Based Access Controls

Introduced in version 12c makes possible granting roles to PL/SQL objects: functions, packages, procedures, and types GRANT <role_name> TO <pl/sq/ object type> <schema_name>.<object_name>;

CREATE TABLE t AS

      SELECT * FROM all_tables;

      

      CREATE ROLE cbac_role;

      

      GRANT read ON t TO cbac_role;

      

      CREATE OR REPLACE PROCEDURE readt AUTHID DEFINER IS

      BEGIN

        NULL;

      END readt;

      /

      

      GRANT cbac_role TO 
      PROCEDURE readt;

PLUSTRACE Role

Creating And Assigning The PLUSTRACE Role For AUTOTRACE

This role must be created by SYS in the PDB (not the CDB) and grants SELECT on the following v_$ views:

V_$SESSTAT
V_$STATNAME
V_$MYSTAT

conn sys@orabase as sysdba

      

      SQL> SELECT sys_context('USERENV', 'CON_NAME') FROM dual;

      

      SYS_CONTEXT('USERENV','CON_NAME')

      ----------------------------------

      ORABASE

      

      SQL> @?/sqlplus/admin/plustrce.sql

      

      GRANT plustrace TO uwclass;

Role Related Queries

Roles are treated like users in the data dictionary -- users SELECT name USER_NAMES, DECODE(spare1, 0, 'USER', 'SYSTEM') CREATED_BY, spare6 CREATE_DATE FROM user$ WHERE type# = 1 ORDER BY 1; -- roles SELECT name ROLE_NAMES, DECODE(spare1, 0, 'USER', 'SYSTEM') CREATED_BY FROM user$ WHERE type# = 0 ORDER BY 1;

Roles Granted to the Current User col granted_role format a30 SELECT granted_role, admin_option, default_role, os_granted, common FROM user_role_privs ORDER BY 2;

Privileges Granted to a Role col role format a30 SELECT * FROM role_sys_privs ORDER BY 1;

Grant SELECT On All Tables in a Schema to a Role CREATE OR REPLACE PROCEDURE GRANT_SELECT AUTHID CURRENT_USER IS 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 dm216q'; 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 ORDER BY 1,2;

Tables And Columns That Can Be Modified by a User -- add a WHERE clause to this query to limit the rows returned for relevancy SELECT * FROM all_updatable_columns ORDER BY 1;

Roles Created by Database Installation

You may not find all of these roles in your database depending on edition and feature's chosen

Role Name	Description
ACCHK_READ	Provides privileges to use Application Continuity Protection Check (ACCHK), which includes the ability to query the following related dictionary view. Database and PDB administrators may grant this role to developers to read their results from ACCHK in non-production environments.
ADM_PARALLEL_EXECUTE_TASK	Provides privileges to update table data in parallel by using the DBMS_PARALLEL_EXECUTE PL/SQL package
APPLICATION_TRACE_VIEWER	Required for viewing application trace records
AQ_ADMINISTRATOR_ROLE	Privilege to administer Advanced Queuing
AQ_USER_ROLE	De-supported but maintained for backward compatibility to version 8.0
AUDIT_ADMIN	Provides privileges to create unified and fine-grained audit policies, use the AUDIT and NOAUDIT SQL statements, view audit data, and manage the audit trail administration
AUDIT_VIEWER	Provides privileges to view and analyze audit data
AUTHENTICATEDUSER	Used by the XDB protocols to define any user who has logged in to the system
AVTUNE_PKG_ROLE	Analytic View Caching Support
BDSQL_ADMIN	BD is Big Data Cluster Access
BDSQL_USER	BD is Big Data Cluster Access
CAPTURE_ADMIN	Provides the privileges necessary to create and manage privilege analysis policies
CDB_DBA	Provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS, and SELECT ON CDB_LOCAL_ADMIN_PRIVS. If your site requires additional privileges, then you can create a role (either common or local) to cover these privileges, and then grant this role to the CDB_DBA role
CONNECT	This role was created by Oracle before the concepts of the Principle of Least Privilege and Zero Trust Architecture, NIST 800-53, NIST 800-171, and other requirements existed and for reasons of backward compatibility it has not been dropped. We recommend not granting this role to any user or service. In 11g Oracle dropped all of the excessive privilege and, unfortunately, with the 12c container database someone at Oracle decided to add an excessive privilege to it again We recommend never granting this role to any user or service but instead grant the CREATE SESSION privilege. 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.
CTXAPP	Enables developers create Oracle Text indexes and index preferences, and to use PL/SQL packages
DATAPUMP_EXP_FULL_DATABASE	Grants EXP_FULL_DATABASE role
DATAPUMP_IMP_FULL_DATABASE	Grants EXP_FULL_DATABASE and IMP_FULL_DATABASE roles
DBA	This role was created by Oracle before the concepts of the Principle of Least Privilege and Zero Trust Architecture, NIST 800-53, NIST 800-171, and other requirements existed and for reasons of backward compatibility it has not been dropped. We recommend never granting this role to any user or service but instead granting only the privileges the user actually requires and protecting them with a password or other form of MFA. 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.
DBFS_ROLE	Provides access to the DBFS (the Database Filesystem) packages and objects
DBJAVASCRIPT	Administration of in-database JavaScript
DBMS_MDX_INTERNAL	Granting the DBMS_MDX_INTERNAL role to users fails with ORA-01924: role 'DBMS_MDX_INTERNAL' not granted or does not exist. This role cannot be granted.
DB_DEVELOPER_ROLE	New in 23c, this role was created by Product Managers that think the Principle of Least Privilege and Zero Trust Architecture, NIST 800-53, NIST 800-171, and other requirements do not apply to Oracle's customers. We recommend not granting this role to any user or service.
DGPDB	Pluggable Databases in Data Guard Physical Standbys
DV_ACCTMGR	Use the DV_ACCTMGR role to create and maintain database accounts and database profiles. In this manual, the example DV_ACCTMGR role is assigned to a user named amalcolm_dvacctmgr.
DV_ADMIN	The DV_ADMIN role controls the Oracle Database Vault PL/SQL packages.
DV_AUDIT_CLEANUP	Grant to any user who is responsible for purging the Database Vault auit trail in a non-unified auditing environment
DV_DATAPUMP_NETWORK_LINK	Database Vault support for Network Links
DV_GOLDENGATE_ADMIN	Intended for any user with responsibility for GoldenGate configuration by default it contains no privileges
DV_GOLDENGATE_REDO_ACCESS	For any user who is responsible for using the Oracle GoldenGate TRANLOGOPTIONS DBLOGREADER method to access redo logs in an Oracle Database Vault environment
DV_MONITOR	Enables the Oracle Enterprise Manager Grid Control agent to monitor Oracle Database Vault for attempted violations and configuration issues with realm or command rule definitions. This enables Grid Control to read and propagate realm definitions and command rule definitions between databases.
DV_OWNER	The DV_OWNER role has the administrative capabilities that the DV_ADMIN role provides, and the reporting capabilities the DV_SECANALYST role provides.
DV_PATCH_ADMIN	Temporarily grant the DV_PATCH_ADMIN role to any database administrator who is responsible for performing database patching or adding languages to Database Vault. After the patch operation or language addition is complete, you should immediately revoke this role. The role does not provide access to any secured data.
DV_POLICY_OWNER	Grants SELECT on 9 DVSYS tables and EXECUTE on DBMS_MACADM
DV_SECANALYST	DV_SECANALYST can query DVSYS schema objects through Oracle Database Vault-supplied views only.
DV_STREAMS_ADMIN	Grant to a user who is responsible for configuring Streams replication in an Oracle Database Vault environment.
DV_XSTREAM_ADMIN	Grant to a user who is responsible for configuring XStreams replication in an Oracle Database Vault environment.
EJBCLIENT	Provides privileges to connect to EJBs from a Java stored procedure
EXECUTE_CATALOG_ROLE	Allow users EXECUTE privileges for packages and procedures in the data dictionary. Granted HS_ADMIN_EXECUTE_ROLE role
EXP_FULL_DATABASE	Provides the privileges required to perform full and incremental database export. Granted EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE roles.
GATHER_SYSTEM_STATISTICS	To update the dictionary system statistics a user must have DBA privileges or the GATHER_SYSTEM_STATISTICS role.
GDS_CATALOG_SELECT	Provides access to 10 objects owned by GSMADMIN_INTERNAL
GGSYS_ROLE	This role has no assigned system, object, or role privileges and appears to exist solely as a placeholder for the GGSYS database user.
GLOBAL_AQ_USER_ROLE	Required to register through LDAP using JDBC connection parameters as this requires the ability to write access to the connection factory entries in the LDAP server (which requires the LDAP user to be either the database itself or be granted GLOBAL_AQ_USER_ROLE).
GRAPH_ADMINISTRATOR	Graph Database administrator
GRAPH_DEVELOPER	Graph Database developer
GRAPH_USER	Graph Database user
GSMADMIN_ROLE	Granted AQ_ADMINISTRATOR_ROLE and CONNECT roles: Inlcudes EXECUTE on DBMS_GSM_UTILITY and related resources
GSMROOTUSER_ROLE	New 20c grants EXECUTE on 4 PL/SQL packages
GSMUSER_ROLE	Granted CONNECT role: Includes EXECUTE on DBMS_GSM_DBADMIN
GSM_POOLADMIN_ROLE	Granted CONNECT role: Inlcudes EXECUTE on DBMS_GSM_POOLADMIN
HS_ADMIN_EXECUTE_ROLE	Provides the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages
HS_ADMIN_ROLE	Provides privileges for DBAs who need to use the DBA role using Oracle Database Heterogeneous Services to access appropriate tables in the data dictionary. Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data dictionary access also can access the HS data dictionary.
HS_ADMIN_SELECT_ROLE	Provides privileges to query the Heterogeneous Services data dictionary views
IMP_FULL_DATABASE	Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE. This role is provided for convenience in using the export and import utilities.
JAVADEBUGPRIV	Grants permissions to run the Java debugger
JAVAIDPRIV	Deprecated
JAVASYSPRIV	Grants permissions for Java administrators including updating JVM-protected packages. Granted the JAVAUSERPRIV role.
JAVAUSERPRIV	Grants permissions for Java users such as examining properties
JAVA_ADMIN	Java administration privileges including permission to modify PolicyTable.
JAVA_DEPLOY	Undocumented
JMXSERVER	Provides permissions to start and maintain a JMX agent in a session. The procedure dbms_java.start_jmx_agent starts the agent in a specific session that generally remains active for the duration of the session.
LBAC_DBA	Provides permissions to use the SA_SYSDBA PL/SQL package
LOGSTDBY_ADMINISTRATOR	A prototype role created by default with the RESOURCE role. It is advised that you 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.
OEM_ADVISOR	Provides privileges to create, drop, select (read), load (write), and delete a SQL tuning set through the DBMS_SQLTUNE PL/SQL package, and to access to the Advisor framework using the ADVISOR PL/SQL package
OEM_MONITOR	Provides privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage a database
OLAP_DBA	Provides privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database
OLAP_USER	Provides application developers privileges to create dimensional objects in their own schemas for Oracle OLAP
OLAP_XS_ADMIN	Administer OLAP data security. Granted the XS_RESOURCE role
OPTIMIZER_PROCESSING_RATE	Provides privileges to execute the GATHER_PROCESSING_RATE, SET_PROCESSING_RATE, and DELETE_PROCESSING_RATE procedures in the DBMS_STATS package. These procedures manage the processing rate of a system for automatic degree of parallelism (Auto DOP). Auto DOP uses these processing rates to determine the optimal degree of parallelism for a SQL statement.
ORDADMIN	After installing Oracle Multimedia DICOM, the ORDADMIN role is created, with the database system privileges required for administration of the DICOM data model repository. The ORDADMIN role must be assigned to the administrator of the DICOM data model repository.
PDB_DBA	Granted automatically to the local user that is created when you create a new pluggable database (PDB) from the seed PDB. No privileges are provided with this role.
PPLB_ROLE	New in 20c and undocumented this role has 276 object privileges assigned. It likely relates to the export of backup metadata.
PLUSTRACE	Grants privlileges on V$ views required to use AUTOTRACE. Can be created in a PDB but not in the CDB.
PROVISIONER	Provides privileges to register and update global callbacks for Oracle Database Real Application sessions and to provision principals.
PUBLIC	-
RDFCTX_ADMIN	This role is undocumented has no system, object or role privileges assigned.
RECOVERY_CATALOG_OWNER	Provides privileges 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, and CREATE PROCEDURE
RECOVERY_CATALOG_OWNER_VPD	Support for RMAN Virtual Private Catalog feature
RECOVERY_CATALOG_USER	RMAN Recovery Catalog access
RESOURCE	This role was created by Oracle before the concepts of the Principle of Least Privilege and Zero Trust Architecture, NIST 800-53, NIST 800-171, and other requirements existed and for reasons of backward compatibility it has not been dropped. We recommend never granting this role to any user or service but instead granting only the privileges the user actually requires and protecting them with a password or other form of MFA. 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	Allows the grantee to execute the procedures of the DBMS_SCHEDULER package. The role includes all of the job scheduler system privileges and is included in the DBA role.
SELECT_CATALOG_ROLE	Provides SELECT privilege on objects in the data dictionary. Granted the HS_ADMIN_SELECT_ROLE role.
SODA_APP	Provides privileges to use the SODA (Simple Oracle Document Access) APIs, to create, drop, and list document collections
SYSUMF_ROLE	Privileges related to Universal Message Format (UMF). This role containers 2 system privileges and 874 object privileges.
TKPROFER	To grant SELECT on dynamic views for TKPROF, run utltkprf.sql in a PDB. TKPROF needs this to dereference wait events.
WM_ADMIN_ROLE	Contains all Workspace Manager privileges with the grant option. By default, the database administrator (DBA role) is granted the WM_ADMIN_ROLE role.
XDBADMIN	Allows the grantee to register an XML schema globally, as opposed to registering it for use or access only by its owner. It also lets the grantee bypass access control list (ACL) checks when accessing Oracle XML DB Repository.
XDB_SET_INVOKER	Allows the grantee to define invoker's rights handlers and to create or update the resource configuration for XML repository triggers. By default, Oracle Database grants this role to the DBA role but not to the XDBADMIN role.
XDB_WEBSERVICES	Allows the grantee to access Oracle Database Web services over HTTPS. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role. For a user to use these Web services, SYS must enable the Web service servlets.
XDB_WEBSERVICES_OVER_HTTP	Allows the grantee to access Oracle Database Web services over HTTP. However, it does not provide the user access to objects in the database that are public. To allow public access, you need to grant the user the XDB_WEBSERVICES_WITH_PUBLIC role.
XDB_WEBSERVICES_WITH_PUBLIC	Allows the grantee access to public objects through Oracle Database Web services.
XS_CACHE_ADMIN	the mid-tier cache. It is required for caching the security policy at the mid-tier level for the checkAcl (authorization) method of the XSAccessController class. Grant this role to the application connection user or the Real Application Security dispatcher.
XS_CONNECT	Grants CREATE_SESSION
XS_NAMESPACE_ADMIN	In Oracle Database Real Application Security, enables the grantee to manage and manipulate the namespace and attribute for a session. Grant this role to the Real Application Security session user.
XS_SESSION_ADMIN	In Oracle Database Real Application Security, enables the grantee to manage the life cycle of a session, including the ability to create, attach, detach, and destroy the session. Grant this role to the application connection user or Real Application Security dispatcher.

Security Concerns

This section contains guidance on Roles, Granted Roles, and Role Privileges that violate the Principle of Least Privilege (NIST 800-53) and Zero Trust Architecture (NIST 800-207) see the Security Concerns section at page bottom. As granted by Oracle at the time of installation there are no issues but any additional grants made by DBAs or applications should be an area of intense focus and concern.

Note that none of these Oracle defined roles are subject to MFA or Decision Point controls as required by Zero Trust architecture. The DBA role is a gross violation of the Principle of Least Privilege. At installation Oracle grants this role to only SYS and SYSTEM and the DBA role should NEVER be granted to any user, service or application account.

The DB_DEVELOPER_ROLE is a gross violation of the Principle of Least Privilege. At installation Oracle grants this role to only SYS and which is not a security violation but is a violation of common sense as all privilege in this role are already granted to SYS.

Th GSMADMIN_ROLE contains the ALTER SYSTEM system privilege and thus should NEVER be granted to any user, service or application account without understanding that by virtue of granting this role you have granted, unrestricted, user of ALTER SYSTEM plus 110 object privileges plus AQ_ADMINISTRATOR, CONNECT, and XDBADMIN roles.

The GSMADMIN_ROLE is only granted to SYS which is not in and of itself an issue except that it is a double grant of the ALTER SYSTEM system privilege

The RESOURCE role, is granted at installation to SYS and the LOGSTDBY_ADMINISTRATOR role which is in the case of the grant to SYS a second granting of privilege SYS already has and in the case of LOGSTDBY_ADMINISTRATOR a violation of the Principle of Least Privilege. Never grant this role to any user, service, or application.

The SELECT_CATALOG_ROLE, at installation, is granted to AVTUNE_PKG_ROLE, EXP_FULL_DATABASE, IMP_FULL_DATABASE, OEM_MONITOR, OGG_SHARED_CAPTURE, XSTREAM_APPLY, and the XSTREAM_CAPTURE roles. Oracle's grants are appropriate but none of these roles should be granted to a user, service, or application.

Be sure that you are completely familiar with the requirements of the Principle of Least Privilege and careful that you do not violate it. Further, all roles created or granted should be subject to MFA using the native abilities contained in Oracle's CREATE ROLE syntax which can be found at https://docs.oracle.com.