"Sometimes I find the best way of getting from one place to another is simply to erase everything and begin again." -- Norton Juster, The Phantom Tollbooth
In one of the Oracle forums a question was asked regarding revoking selected privileges from the DBA role. Unfortunately for the person posting the question the answer is a resounding “No”; granting a role grants all privileges assigned to that role and there is no “picking and choosing” as if you were in a cafeteria. Roles are designed (or should be, at least) to grant all necessary privileges a user would need to access objects that role will use. And roles supplied by Oracle are designed for the jobs they are named after, such as DBA. Changing the role affects functionality and can seriously impact those granted that role. Let’s look at that in a bit more detail.
Oracle provides pre-configured roles with every installation of the database, and the list can vary based on the options you choose to install. A partial list of these roles from 188.8.131.52 is shown below (remember this is not a complete and exhaustive list):
ROLE O ------------------------------ - CONNECT Y RESOURCE Y DBA Y AUDIT_ADMIN Y AUDIT_VIEWER Y SELECT_CATALOG_ROLE Y EXECUTE_CATALOG_ROLE Y DELETE_CATALOG_ROLE Y CAPTURE_ADMIN Y EXP_FULL_DATABASE Y IMP_FULL_DATABASE Y CDB_DBA Y PDB_DBA Y RECOVERY_CATALOG_OWNER Y LOGSTDBY_ADMINISTRATOR Y DBFS_ROLE Y GSMUSER_ROLE Y AQ_ADMINISTRATOR_ROLE Y AQ_USER_ROLE Y DATAPUMP_EXP_FULL_DATABASE Y DATAPUMP_IMP_FULL_DATABASE Y ADM_PARALLEL_EXECUTE_TASK Y PROVISIONER Y XS_RESOURCE Y XS_SESSION_ADMIN Y XS_NAMESPACE_ADMIN Y XS_CACHE_ADMIN Y GATHER_SYSTEM_STATISTICS Y OPTIMIZER_PROCESSING_RATE Y GSMADMIN_ROLE Y RECOVERY_CATALOG_USER Y EM_EXPRESS_BASIC Y EM_EXPRESS_ALL Y SCHEDULER_ADMIN Y HS_ADMIN_SELECT_ROLE Y HS_ADMIN_EXECUTE_ROLE Y HS_ADMIN_ROLE Y GLOBAL_AQ_USER_ROLE Y OEM_ADVISOR Y OEM_MONITOR Y XDBADMIN Y XDB_SET_INVOKER Y AUTHENTICATEDUSER Y XDB_WEBSERVICES Y XDB_WEBSERVICES_WITH_PUBLIC Y XDB_WEBSERVICES_OVER_HTTP Y GSM_POOLADMIN_ROLE Y GDS_CATALOG_SELECT Y WM_ADMIN_ROLE Y JAVAUSERPRIV Y JAVAIDPRIV Y JAVASYSPRIV Y JAVADEBUGPRIV Y EJBCLIENT Y JMXSERVER Y JAVA_ADMIN Y JAVA_DEPLOY Y CTXAPP Y ORDADMIN Y OLAP_XS_ADMIN Y OLAP_DBA Y OLAP_USER Y SPATIAL_WFS_ADMIN Y WFS_USR_ROLE Y SPATIAL_CSW_ADMIN Y CSW_USR_ROLE Y LBAC_DBA Y APEX_ADMINISTRATOR_ROLE Y APEX_GRANTS_FOR_NEW_USERS_ROLE Y DV_SECANALYST Y DV_MONITOR Y DV_ADMIN Y DV_OWNER Y DV_ACCTMGR Y DV_PUBLIC Y DV_PATCH_ADMIN Y DV_STREAMS_ADMIN Y DV_GOLDENGATE_ADMIN Y DV_XSTREAM_ADMIN Y DV_GOLDENGATE_REDO_ACCESS Y DV_AUDIT_CLEANUP Y DV_DATAPUMP_NETWORK_LINK Y DV_REALM_RESOURCE Y DV_REALM_OWNER Y
The ‘O’ header is for the ORACLE_MAINTAINED column which indicates the role is supplied by Oracle. [This is a new column in the DBA_ROLES view for 12.1; earlier releases do not have this column in the view definition.] That list has 84 different roles all created when your database was created. What privileges do these roles have? That’s a question answered by the ROLE_SYS_PRIVS and ROLE_TAB_PRIVS views; let’s look at the DBA role and see what Oracle deems as necessary system privileges to be an effective DBA:
PRIVILEGE ---------------------------------------- CREATE SESSION ALTER SESSION DROP TABLESPACE BECOME USER DROP ROLLBACK SEGMENT SELECT ANY TABLE INSERT ANY TABLE UPDATE ANY TABLE DROP ANY INDEX SELECT ANY SEQUENCE CREATE ROLE EXECUTE ANY PROCEDURE ALTER PROFILE CREATE ANY DIRECTORY CREATE ANY LIBRARY EXECUTE ANY LIBRARY ALTER ANY INDEXTYPE DROP ANY INDEXTYPE DEQUEUE ANY QUEUE EXECUTE ANY EVALUATION CONTEXT EXPORT FULL DATABASE CREATE RULE ALTER ANY SQL PROFILE ADMINISTER ANY SQL TUNING SET CHANGE NOTIFICATION DROP ANY EDITION DROP ANY MINING MODEL ALTER ANY MINING MODEL ALTER ANY CUBE DIMENSION CREATE CUBE DROP ANY CUBE BUILD PROCESS USE ANY SQL TRANSLATION PROFILE CREATE PLUGGABLE DATABASE ALTER ROLLBACK SEGMENT DELETE ANY TABLE ALTER DATABASE FORCE ANY TRANSACTION ALTER ANY PROCEDURE DROP ANY TRIGGER DROP ANY MATERIALIZED VIEW UNDER ANY TYPE ALTER ANY LIBRARY CREATE DIMENSION DEBUG ANY PROCEDURE CREATE RULE SET ALTER ANY RULE SET ANALYZE ANY DICTIONARY ALTER ANY EDITION CREATE ANY ASSEMBLY ALTER ANY CUBE SELECT ANY CUBE DROP ANY MEASURE FOLDER RESTRICTED SESSION CREATE TABLESPACE ALTER TABLESPACE CREATE USER ALTER USER LOCK ANY TABLE CREATE VIEW DROP ANY VIEW GRANT ANY ROLE CREATE TRIGGER CREATE TYPE EXECUTE ANY OPERATOR CREATE ANY DIMENSION ALTER ANY DIMENSION CREATE ANY OUTLINE ADMINISTER DATABASE TRIGGER RESUMABLE FLASHBACK ANY TABLE CREATE ANY RULE SET EXECUTE ANY RULE SET IMPORT FULL DATABASE EXECUTE ANY RULE EXECUTE ANY PROGRAM CREATE ANY EDITION CREATE ASSEMBLY ALTER ANY ASSEMBLY CREATE CUBE DIMENSION CREATE ANY CUBE BUILD PROCESS UPDATE ANY CUBE DIMENSION EM EXPRESS CONNECT SET CONTAINER ALTER ANY MEASURE FOLDER CREATE ANY TABLE CREATE ANY INDEX CREATE ANY SEQUENCE ALTER ANY ROLE ANALYZE ANY DROP ANY LIBRARY CREATE ANY OPERATOR CREATE INDEXTYPE UNDER ANY TABLE DROP ANY DIMENSION SELECT ANY DICTIONARY GRANT ANY OBJECT PRIVILEGE CREATE EVALUATION CONTEXT CREATE ANY EVALUATION CONTEXT DROP ANY EVALUATION CONTEXT CREATE ANY RULE CREATE JOB CREATE ANY JOB CREATE MINING MODEL INSERT ANY CUBE DIMENSION DROP ANY CUBE UPDATE ANY CUBE BUILD PROCESS EXEMPT DML REDACTION POLICY READ ANY TABLE ALTER SYSTEM AUDIT SYSTEM CREATE ROLLBACK SEGMENT DROP ANY TABLE COMMENT ANY TABLE REDEFINE ANY TABLE CREATE CLUSTER ALTER ANY INDEX DROP PUBLIC DATABASE LINK CREATE PROFILE ALTER ANY MATERIALIZED VIEW ALTER ANY TYPE DROP ANY TYPE UNDER ANY VIEW EXECUTE ANY INDEXTYPE DROP ANY CONTEXT ALTER ANY OUTLINE ADMINISTER RESOURCE MANAGER MANAGE SCHEDULER MANAGE FILE GROUP CREATE ANY MINING MODEL SELECT ANY MINING MODEL CREATE ANY MEASURE FOLDER DELETE ANY MEASURE FOLDER CREATE ANY SQL TRANSLATION PROFILE CREATE ANY CREDENTIAL EXEMPT DDL REDACTION POLICY SELECT ANY MEASURE FOLDER SELECT ANY CUBE BUILD PROCESS ALTER ANY CUBE BUILD PROCESS CREATE TABLE BACKUP ANY TABLE CREATE ANY CLUSTER DROP ANY SYNONYM DROP PUBLIC SYNONYM CREATE ANY VIEW CREATE SEQUENCE ALTER ANY SEQUENCE FORCE TRANSACTION CREATE PROCEDURE CREATE ANY PROCEDURE ALTER RESOURCE COST DROP ANY DIRECTORY CREATE ANY TYPE ALTER ANY OPERATOR CREATE ANY INDEXTYPE ENQUEUE ANY QUEUE ON COMMIT REFRESH DEBUG CONNECT SESSION DROP ANY RULE SET EXECUTE ANY CLASS MANAGE ANY FILE GROUP EXECUTE ANY ASSEMBLY EXECUTE ASSEMBLY COMMENT ANY MINING MODEL CREATE ANY CUBE DIMENSION DELETE ANY CUBE DIMENSION SELECT ANY CUBE DIMENSION DROP ANY SQL TRANSLATION PROFILE CREATE CREDENTIAL ALTER ANY TABLE DROP ANY CLUSTER CREATE SYNONYM CREATE PUBLIC SYNONYM DROP ANY SEQUENCE DROP ANY ROLE AUDIT ANY DROP ANY PROCEDURE CREATE ANY TRIGGER ALTER ANY TRIGGER DROP PROFILE GRANT ANY PRIVILEGE CREATE LIBRARY CREATE OPERATOR DROP ANY OUTLINE MERGE ANY VIEW ADMINISTER SQL TUNING SET UPDATE ANY CUBE INSERT ANY MEASURE FOLDER ADMINISTER SQL MANAGEMENT OBJECT CREATE SQL TRANSLATION PROFILE LOGMINING MANAGE TABLESPACE DROP USER ALTER ANY CLUSTER CREATE ANY SYNONYM CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK CREATE MATERIALIZED VIEW CREATE ANY MATERIALIZED VIEW EXECUTE ANY TYPE DROP ANY OPERATOR QUERY REWRITE GLOBAL QUERY REWRITE MANAGE ANY QUEUE CREATE ANY CONTEXT ALTER ANY EVALUATION CONTEXT ALTER ANY RULE DROP ANY RULE ADVISOR SELECT ANY TRANSACTION DROP ANY SQL PROFILE CREATE ANY SQL PROFILE READ ANY FILE GROUP CREATE EXTERNAL JOB DROP ANY ASSEMBLY DROP ANY CUBE DIMENSION CREATE ANY CUBE CREATE MEASURE FOLDER CREATE CUBE BUILD PROCESS ALTER ANY SQL TRANSLATION PROFILE FLASHBACK ARCHIVE ADMINISTER
220 system privileges are required to be an effective DBA for an Oracle database, an impressive list, indeed. Of course once a user is granted the DBA role he or she gets ALL of those system privileges and since the role is the only direct grant that gives those privileges that list cannot be modified by selectively revoking one or more of those privileges:
SQL> grant DBA to blorpo identified by gussyflorp; Grant succeeded. SQL> revoke select any transaction from blorpo; revoke select any transaction from blorpo * ERROR at line 1: ORA-01952: system privileges not granted to 'BLORPO' SQL>
Yes, the user DOES have that privilege, albeit indirectly. It’s indirect because it’s the ROLE that was granted that privilege, among others, and no attempt was made to revoke the role from the user. It’s a ‘package deal’; you grant a role to a user and it’s all or nothing, and even though it behaves like the user has the privileges granted directly that’s not the case.
You could, of course, get all of the privileges the DBA role has (both system and table) and create a script to grant each individual privilege to the desired user. It would be a LONG script and such grants require attention from the DBA granting them to ensure they are both current and not being abused. In that case individual privileges can be revoked which would be a maintenance nightmare for the DBA having keep track of which user has which set of privileges. Another option presents itself, creating a new role with only the privileges the DBA wants to assign to a user. The privilege list for DBA could be shortened to create, say, a DB_OPERATOR or DBO role. Such privileges would depend upon the job description; creating such a role would make granting such access easier and make maintenance simpler since when the role grants are changed those who are granted that role have there privileges adjusted the next time they login.
Roles make granting privileges very easy and straightforward, provided the role is properly created and maintained. Roles also make it impossible to “pick and choose” privileges a user should have. It’s an “all or nothing” proposition and there’s no way around that when using a pre-defined role.
Sometimes you just need to begin again.