For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.

Notes

The maximum number of roles that can be enabled for a single session is 148. A small 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

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

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;

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

Contains the CREATE SESSION and SET CONTAINER system privileges

CSW_USR_ROLE

Provides user privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial

Example Database Administrator role. Should not be used. Granted 19 other roles in an act of pure insanity.

DBFS_ROLE

Provides access to the DBFS (the Database Filesystem) packages and objects

DBJAVASCRIPT

DBMS_MDX_INTERNAL

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

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

DV_PUBLIC

Oracle Database Vault does not enable you to directly grant object privileges in the DVSYS schema to PUBLIC.
You must grant the object privilege on the DVSYS schema object the DV_PUBLIC role, and then grant DV_PUBLIC to PUBLIC.

DV_REALM_OWNER

Use the DV_REALM_OWNER role to manage database objects in multiple schemas that define a realm.
Grant this role to the database account owner who is responsible for managing one or more schema database accounts within a realm and the roles associated with the realm.

DV_REALM_RESOURCE

Use the DV_REALM_RESOURCE role for operations such as creating tables, views, triggers, synonyms, and other objects that a realm would typically use.

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

EM_EXPRESS_ALL

Enables users to connect to Oracle Enterprise Manager (EM) Express and use all the functionality provided by EM Express (read and write access to all EM Express features).
The EM_EXPRESS_ALL role includes the EM_EXPRESS_BASIC role.

EM_EXPRESS_BASIC

Enables users to connect to EM Express and to view the pages in read-only mode. The EM_EXPRESS_BASIC role includes the SELECT_CATALOG_ROLE role

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

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).

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.

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.

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 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.

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.

PLUSTRACE

Grants privlileges on V$ views required to use AUTOTRACE. Can be created in a PDB but not in the CDB.

Provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE
OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.
This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the 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

Allows the grantee to execute the procedures of the DBMS_SCHEDULER package. It 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

SYSUMF_ROLE

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.

XFILES_ADMINISTRATOR

XFILES_USER

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

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.