14.3 Roles & Privilege Management

14.3 Roles & Privilege Management

Directly granting individual system and object privileges to hundreds or thousands of individual user accounts is administratively unmanageable, error-prone, and a severe security risk. To establish scalable, maintainable, and secure access governance, Oracle Database utilizes Roles.

A Role is a named collection of related system privileges, object privileges, and other roles. Instead of managing privileges on a per-user basis, administrators grant privileges to roles, and subsequently grant those roles to appropriate users or other roles.

---

Architecture and Administrative Benefits of Roles

Roles act as privilege containers and intermediate access abstractions in the database security model.

+-------------------------------------------------------------------------+
|                    ORACLE ROLE-BASED ACCESS CONTROL                     |
+-------------------------------------------------------------------------+
|                                                                         |
|  [ SYSTEM PRIVILEGES ]          [ OBJECT PRIVILEGES ]    [ OTHER ROLES ]|
|  - CREATE SESSION               - SELECT ON hr.emp       - reader_role  |
|  - CREATE TABLE                 - UPDATE ON hr.orders                   |
|  - CREATE VIEW                  - INSERT ON hr.items                    |
|          \                              |                             / |
|           +-----------------------------+----------------------------+  |
|                                         |                               |
|                                         v                               |
|                             [ ROLE: APP_DEVELOPER ]                     |
|                             (Named Privilege Container)                 |
|                                         |                               |
|                   +---------------------+---------------------+         |
|                   |                                           |         |
|                   v                                           v         |
|             [ User: ALICE ]                             [ User: BOB ]   |
|                                                                         |
+-------------------------------------------------------------------------+

Core Benefits of Role-Based Security:

1. Simplified Privilege Administration: Modifying the privileges assigned to a role instantly updates the effective permissions of all users assigned that role without modifying individual user accounts.
2. Dynamic Privilege Management: When a privilege is granted to or revoked from a role, all active sessions possessing that role immediately receive or lose that privilege.
3. Encapsulation & Least Privilege: Roles allow organizations to model real-world job functions (e.g., CLERK_ROLE, PAYROLL_ADMIN_ROLE, AUDITOR_ROLE), ensuring users receive only the authorizations necessary for their responsibilities.
4. Selective Role Activation: Users can enable or disable specific roles within a database session using passwords, ensuring heightened security for sensitive administrative operations.

---

Creating and Securing Roles

To create a role, a user must hold the CREATE ROLE system privilege. Roles are database-wide objects and are not contained within any user's private schema.

Syntax

CREATE ROLE role_name
    [ NOT IDENTIFIED
    | IDENTIFIED BY password
    | IDENTIFIED EXTERNALLY
    | IDENTIFIED GLOBALLY
    | IDENTIFIED USING [schema.]package_name ];

Role Authentication Methods:

• NOT IDENTIFIED: (Default) No password is required to enable the role. The role can be enabled by any authorized user via SET ROLE role_name.
• IDENTIFIED BY password: The role is protected by a password. Users must provide the correct password when issuing SET ROLE role_name IDENTIFIED BY password; to activate it.
• IDENTIFIED EXTERNALLY: Role authorization is verified by the operating system or network service.
• IDENTIFIED GLOBALLY: Role authorization is managed centrally by an enterprise directory service (e.g., LDAP or Oracle Enterprise User Security).
• IDENTIFIED USING package: (Secure Application Role) The role can only be enabled by a designated PL/SQL package, preventing direct SQL activation.

Role Creation Examples:

-- 1. Create a standard unauthenticated role
CREATE ROLE junior_developer;

-- 2. Create a password-protected administrative role
CREATE ROLE payroll_manager IDENTIFIED BY SecretMgr2026;

---

Granting Privileges to Roles and Roles to Users

Once created, a role can receive system privileges, object privileges, and other roles.

-- 1. Grant system privileges to a role
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE 
TO junior_developer;

-- 2. Grant object privileges to a role
GRANT SELECT, INSERT, UPDATE ON hr.employees TO junior_developer;

-- 3. Nested Role Grants (Granting a role to another role)
CREATE ROLE senior_developer;
GRANT junior_developer TO senior_developer;
GRANT ALTER ANY TABLE, DROP ANY VIEW TO senior_developer;

-- 4. Grant roles to users
GRANT junior_developer TO alice, bob;
GRANT senior_developer TO claire WITH ADMIN OPTION;

Rules for Role Grants:

• Circular Grants Prohibited: A role cannot be granted to itself, nor can circular inheritance loops be created (e.g., Role A -> Role B -> Role C -> Role A). Oracle detects and blocks circular role dependencies.
• WITH ADMIN OPTION on Roles: When granting a role to a user or another role, WITH ADMIN OPTION can be specified. The grantee can then grant or revoke that role to/from others or drop the role.
• WITH GRANT OPTION Prohibition: You cannot grant object privileges to a role with WITH GRANT OPTION (ORA-01931).

---

Session Role Lifecycle & The SET ROLE Command

When a user connects to an Oracle Database instance, only their designated Default Roles are activated automatically. During an active database session, a user can dynamically enable or disable roles using the SET ROLE statement.

Syntax & Variations of SET ROLE:

-- 1. Enable a specific role (disables all other roles not listed!)
SET ROLE junior_developer;

-- 2. Enable a password-protected role
SET ROLE payroll_manager IDENTIFIED BY SecretMgr2026;

-- 3. Enable multiple specific roles
SET ROLE junior_developer, reporting_role;

-- 4. Enable ALL granted roles (except password-protected roles)
SET ROLE ALL;

-- 5. Enable ALL granted roles EXCEPT designated roles
SET ROLE ALL EXCEPT payroll_manager, auditing_role;

-- 6. Disable ALL roles for the current session
SET ROLE NONE;

Critical Exam Rule on SET ROLE: The SET ROLE statement replaces the currently active roles in the session with the new set specified in the statement. For example, if a user currently has Role_A active and executes SET ROLE Role_B;, Role_A is disabled and only Role_B becomes active.

---

Configuring User Default Roles (ALTER USER ... DEFAULT ROLE)

An administrator can configure which roles are automatically enabled when a user logs in using the ALTER USER command:

-- 1. Make all currently granted roles default
ALTER USER alice DEFAULT ROLE ALL;

-- 2. Make specific roles default (other granted roles become non-default)
ALTER USER alice DEFAULT ROLE junior_developer, reporting_role;

-- 3. Make all roles default EXCEPT sensitive/password roles
ALTER USER alice DEFAULT ROLE ALL EXCEPT payroll_manager;

-- 4. Set NO default roles (user logs in with only direct privileges)
ALTER USER alice DEFAULT ROLE NONE;

Mandatory Requirement for Default Roles:

To set a role as a default role for a user, the role must already be granted directly to that user. If an administrator attempts to set a default role that has not been granted, Oracle raises error ORA-01924: role '<role_name>' not granted or does not exist.

Predefined Oracle Database Roles: Historical vs. Modern State

Oracle Database provides several built-in predefined roles. Understanding the evolution of these roles is critical for 1Z0-071 candidates:

+-------------------------------------------------------------------------+
|               PREDEFINED ORACLE ROLES EVOLUTION (1Z0-071)               |
+-------------------------------------------------------------------------+
|                                                                         |
|  ROLE: CONNECT                                                          |
|  - Historical (Legacy Oracle 9i and earlier):                           |
|    Contained: CREATE SESSION, CREATE TABLE, CREATE VIEW,                |
|               CREATE SEQUENCE, CREATE SYNONYM, CREATE CLUSTER, etc.     |
|  - Modern (Oracle 10gR2, 11g, 12c, 19c, 21c, 23c):                      |
|    CONTAINS ONLY: CREATE SESSION                                        |
|                                                                         |
|  ROLE: RESOURCE                                                         |
|  - Contains creation privileges:                                        |
|    CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER,     |
|    CREATE CLUSTER, CREATE TYPE, CREATE INDEXTYPE, CREATE OPERATOR.      |
|  - Modern Behavior:                                                     |
|    DOES NOT contain UNLIMITED TABLESPACE!                               |
|    Users need explicit quota: ALTER USER u QUOTA 100M ON users;         |
|                                                                         |
|  ROLE: DBA                                                              |
|  - Contains virtually ALL system privileges WITH ADMIN OPTION.          |
|                                                                         |
|  SPECIAL GROUP: PUBLIC                                                  |
|  - Every database user is automatically a member of PUBLIC.             |
|  - Privileges granted to PUBLIC are instantly available to all users.   |
|                                                                         |
+-------------------------------------------------------------------------+

---

Dropping Roles

A role is removed from the database using the DROP ROLE statement:

DROP ROLE junior_developer;

Privileges Required:

• The executing user must possess the DROP ANY ROLE system privilege or have been granted the role WITH ADMIN OPTION.

Impact of Dropping a Role on the Database:

1. Immediate Revocation: Dropping a role immediately and automatically revokes that role from all users and roles to which it was granted.
2. Privilege Loss: Users who derived privileges exclusively through the dropped role instantly lose those privileges.
3. Object Preservation: Dropping a role has zero impact on database objects (tables, views) previously created by users while the role was active.
4. Contrast with Dropping Users: Unlike dropping a user containing objects (which requires DROP USER ... CASCADE), dropping a role never requires a cascade clause.

---

Data Dictionary Views for Auditing Roles and Privileges

Diagnostic Queries for Role Inspection:

-- 1. Check which roles are currently ACTIVE in your session
SELECT role FROM session_roles;

-- 2. Check all roles granted to your user account and if they are default
SELECT granted_role, admin_option, default_role, os_granted 
FROM user_role_privs;

-- 3. Check system privileges provided by a specific role
SELECT privilege, admin_option 
FROM role_sys_privs 
WHERE role = 'JUNIOR_DEVELOPER';

---

Oracle 1Z0-071 Role Management Error Reference Matrix