ExamFinch.com My Study

Study guide

14.3 Roles & Privilege Management

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

14.3 Roles & Privilege Management

Key Takeaways Explain This Topic AI

A role is a named database object that encapsulates system privileges, object privileges, and other roles to simplify scalable privilege administration.

Roles are database-wide entities created with CREATE ROLE and are not owned by or stored within any user's private schema.

When a user connects, their designated default roles are enabled automatically, whereas non-default or password-protected roles must be explicitly activated using SET ROLE.

In modern Oracle releases (10gR2+), the predefined CONNECT role contains only the CREATE SESSION privilege, and the RESOURCE role contains creation privileges without UNLIMITED TABLESPACE.

Dropping a role using DROP ROLE immediately revokes the role and its associated privileges from all users and roles to which it was assigned.

Last updated: August 2026

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:

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.

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.

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.

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:

Immediate Revocation: Dropping a role immediately and automatically revokes that role from all users and roles to which it was granted.

Privilege Loss: Users who derived privileges exclusively through the dropped role instantly lose those privileges.

Object Preservation: Dropping a role has zero impact on database objects (tables, views) previously created by users while the role was active.

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

View Name Scope and Contents

USER_ROLE_PRIVS Lists all roles granted to the current user (includes DEFAULT_ROLE , ADMIN_OPTION , OS_GRANTED ).

SESSION_ROLES Lists all roles currently enabled and active in the user's current session.

ROLE_SYS_PRIVS Lists system privileges granted to roles accessible by the user.

ROLE_TAB_PRIVS Lists object privileges granted to roles accessible by the user.

ROLE_ROLE_PRIVS Lists roles that are granted to other roles.

DBA_ROLES Lists all roles defined in the entire database (requires DBA privileges).

DBA_ROLE_PRIVS Lists all role assignments to all users and roles database-wide.

  • 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

Error Code Error Message Text Root Cause on 1Z0-071

ORA-01924 role '<role_name>' not granted or does not exist Attempted to set a default role using ALTER USER ... DEFAULT ROLE for a role not yet granted to that user.

ORA-01931 cannot grant WITH GRANT OPTION to a role Attempted to grant an object privilege to a role using WITH GRANT OPTION .

ORA-01950 no privileges on tablespace '<tablespace_name>' User holding the RESOURCE role attempted to insert data into a newly created table without having an explicit tablespace quota.

ORA-01927 cannot REVOKE privileges you did not grant User attempted to revoke an object privilege they did not personally grant.

ORA-01934 circular role grant detected Attempted to grant a role to itself or to build a circular role dependency (Role A -> Role B -> Role A).