ExamFinch.com My Study

Study guide

14.1 System Privileges

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.1 System Privileges

Key Takeaways Explain This Topic AI

System privileges grant database-wide or schema-level authorization to perform specific DDL or administrative actions (e.g., CREATE SESSION, CREATE TABLE, CREATE VIEW, DROP ANY TABLE).

The CREATE SESSION system privilege is the mandatory foundation required for any database user to establish a connection and log in to an Oracle Database instance.

The WITH ADMIN OPTION clause enables the grantee to delegate the system privilege to other users or roles, grant it with or without ADMIN OPTION, and revoke it across the database.

System privileges and the WITH ADMIN OPTION clause can be granted to both individual users and database roles.

The Critical 1Z0-071 No-Cascade Rule: Revoking a system privilege from a user who delegated it to other users using WITH ADMIN OPTION does NOT revoke the privilege from downstream grantees.

Last updated: August 2026

14.1 System Privileges

Database security in Oracle is built upon a granular, two-tiered privilege model: System Privileges and Object Privileges . Understanding the operational boundaries, delegation mechanics, and revocation rules of each tier is essential for database administrators, developers, and candidates preparing for the Oracle Database SQL (1Z0-071) examination.

A System Privilege confers the right to perform a specific administrative action or to execute a class of Data Definition Language (DDL) or Data Manipulation Language (DML) statements across the entire database or across all objects of a specified type within a schema.

Overview of Oracle Privilege Architecture

Privileges represent authorizations granted by the database engine to users or roles. Oracle strictly separates system-level capabilities from object-level actions: +-------------------------------------------------------------------------+

  • | ORACLE DATABASE PRIVILEGE HIERARCHY |
  • +-------------------------------------------------------------------------+
  • | |
  • | [ SYSTEM PRIVILEGES ] [ OBJECT PRIVILEGES ] |
  • | - Database-wide actions - Specific object actions |
  • | - Schema-level DDL rights - Target: Tables, Views, etc. |
  • | - Controlled by DBA / ADMIN OPTION - Controlled by Object Owner |
  • | - Examples: - Examples: |
  • | * CREATE SESSION * SELECT ON hr.employees |
  • | * CREATE TABLE * INSERT ON hr.orders |
  • | * CREATE VIEW * UPDATE (salary) ON emp |
  • | * DROP ANY TABLE * EXECUTE ON hr.calc_tax |
  • | - Delegation: WITH ADMIN OPTION - Delegation: WITH GRANT OPTION |
  • | - Revocation: DOES NOT CASCADE - Revocation: CASCADES! |
  • | |
  • +-------------------------------------------------------------------------+

Unlike object privileges—which are tied to a specific named table, view, sequence, or stored procedure—system privileges authorize actions that either affect the database instance as a whole or apply across arbitrary objects.

Core System Privileges Reference List

Oracle provides more than 200 distinct system privileges. For the 1Z0-071 examination, candidates must master the standard categories described below.

1. Connection and Session Privileges

CREATE SESSION : The fundamental prerequisite system privilege required to establish a connection (logon) to an Oracle database instance. Without CREATE SESSION , a user cannot connect, regardless of what other privileges or roles they hold.

ALTER SESSION : Enables the user to modify session-level environment settings using the ALTER SESSION statement (such as changing NLS_DATE_FORMAT or enabling SQL trace).

RESTRICTED SESSION : Allows a user to log in when the database has been started in restricted mode ( STARTUP RESTRICT or ALTER SYSTEM ENABLE RESTRICTED SESSION ).

2. Schema Object Creation Privileges (Owner Schema)

These privileges allow users to create and manage objects within their own schema:

CREATE TABLE : Allows the creation of physical tables in the user's schema. (Note: To successfully populate the table with data, the user also requires a quota allocation on a tablespace).

CREATE VIEW : Authorizes creation of views in the user's schema.

CREATE SEQUENCE : Authorizes creation of sequence generators in the user's schema.

CREATE SYNONYM : Authorizes creation of private synonyms pointing to objects in the user's or other schemas.

CREATE PROCEDURE : Authorizes creation of standalone stored procedures, functions, and packages in the user's schema.

CREATE TRIGGER : Authorizes creation of database table triggers in the user's schema.

CREATE TYPE : Authorizes creation of user-defined data types and object types.

3. Cross-Schema "ANY" Privileges

Privileges containing the keyword ANY grant omnipotent authority across all schemas in the database (with the exception of internal data dictionary objects owned by SYS ):

CREATE ANY TABLE : Allows creating tables in any schema (e.g., CREATE TABLE hr.payroll (...) ).

ALTER ANY TABLE : Allows modifying table structures across all schemas.

DROP ANY TABLE : Allows dropping tables belonging to any user.

SELECT ANY TABLE : Authorizes querying any table, view, or materialized view across all schemas.

INSERT ANY TABLE , UPDATE ANY TABLE , DELETE ANY TABLE : Authorizes data manipulation across all schemas.

CREATE ANY VIEW , DROP ANY VIEW : Authorizes view creation/removal in any schema.

CREATE PUBLIC SYNONYM , DROP PUBLIC SYNONYM : Authorizes creation and removal of public synonyms visible to all database users.

4. Storage and User Management Privileges

UNLIMITED TABLESPACE : Authorizes unlimited storage consumption across all tablespaces in the database, overriding any explicit tablespace quota limits. (Critical Exam Rule: UNLIMITED TABLESPACE is a system privilege that cannot be granted to a role; it can only be granted to individual users).

CREATE USER , ALTER USER , DROP USER : Authorizes the creation, modification, and removal of database user accounts.

Syntax for Granting System Privileges

System privileges are granted to users, roles, or the universal user group PUBLIC using the GRANT statement: GRANT system_privilege [, system_privilege2, ...]

  • TO { user_name | role_name | PUBLIC } [, ...]
  • [WITH ADMIN OPTION];

Examples of System Privilege Grants -- 1. Grant basic connection and object creation privileges to a new developer

  • GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE
  • TO developer_claire;
  • - 2. Grant system privilege to a role
  • GRANT CREATE TABLE, CREATE VIEW
  • TO dev_team_role;
  • - 3. Grant system privilege to all database users via PUBLIC
  • GRANT CREATE SYNONYM
  • TO PUBLIC;
  • - 4. Grant administrative user management with delegation option
  • GRANT CREATE USER, ALTER USER, DROP USER
  • TO security_admin
  • WITH ADMIN OPTION;
  • The WITH ADMIN OPTION Clause & Administrative Delegation

When a system privilege is granted with the WITH ADMIN OPTION clause, the grantee receives significant administrative authority regarding that privilege.

Rights Conferred by WITH ADMIN OPTION :

Delegation: The grantee can grant the specific system privilege to any other database user or role.

Propagating the Admin Option: The grantee can choose whether to grant the privilege with or without WITH ADMIN OPTION to downstream grantees.

Revocation Authority: The grantee can revoke the system privilege from any other user or role in the database, even if the grantee was not the original administrator who granted the privilege to that user.

Role Assignment: Unlike object privileges ( WITH GRANT OPTION ), WITH ADMIN OPTION can be granted to database roles (e.g., GRANT CREATE TABLE TO junior_dba_role WITH ADMIN OPTION; ). +-------------------------------------------------------------------------+

  • | WITH ADMIN OPTION DELEGATION & REVOCATION FLOW |
  • +-------------------------------------------------------------------------+
  • | |
  • | [ Security DBA ] |
  • | | |
  • | | GRANT CREATE TABLE TO Alice WITH ADMIN OPTION; |
  • | v |
  • | [ User ALICE ] (Has CREATE TABLE + ADMIN OPTION) |
  • | | |
  • | | GRANT CREATE TABLE TO Bob; (Without ADMIN OPTION) |
  • | v |
  • | [ User BOB ] (Has CREATE TABLE only) |
  • | | |
  • | | Cannot grant CREATE TABLE to anyone else |
  • | v |
  • | ..................................................................... |
  • | REVOCATION EVENT: |
  • | Security DBA executes: |
  • | REVOKE CREATE TABLE FROM Alice; |
  • | |
  • | AFTERMATH: |
  • | - Alice loses CREATE TABLE privilege. |
  • | - Bob RETAINS CREATE TABLE privilege! (NO CASCADE RULE) |
  • | |
  • +-------------------------------------------------------------------------+
  • Revoking System Privileges
  • System privileges are revoked using the REVOKE statement: REVOKE system_privilege [, system_privilege2, ...]
  • FROM { user_name | role_name | PUBLIC } [, ...];

Rules for Revoking System Privileges:

To revoke a system privilege, the revoking user must either possess the GRANT ANY PRIVILEGE system privilege or hold that specific system privilege WITH ADMIN OPTION .

You cannot revoke WITH ADMIN OPTION independently while leaving the base system privilege intact. To remove ADMIN OPTION , you must revoke the system privilege entirely and then re-grant it without WITH ADMIN OPTION .

The Critical 1Z0-071 Rule: Non-Cascading Revocation of System Privileges

One of the most frequently tested concepts on the Oracle 1Z0-071 exam is the Non-Cascading Revocation Rule for system privileges.

Core Rule: Revoking a system privilege from a user who granted that same system privilege to other users (via WITH ADMIN OPTION ) DOES NOT CASCADE to those other users. Each downstream grantee retains their system privilege.

Step-by-Step Scenario Analysis:

Step 1: The DBA grants CREATE TABLE to ALICE with administrative rights: -- Executed by DBA:

GRANT CREATE TABLE TO alice WITH ADMIN OPTION;

State: ALICE can create tables and grant CREATE TABLE to others.

Step 2: ALICE grants CREATE TABLE to BOB with administrative rights: -- Executed by ALICE:

GRANT CREATE TABLE TO bob WITH ADMIN OPTION;

State: BOB can create tables and grant CREATE TABLE to others.

Step 3: BOB grants CREATE TABLE to CHARLIE without administrative rights: -- Executed by BOB:

GRANT CREATE TABLE TO charlie;

State: CHARLIE can create tables.

Step 4: The DBA revokes CREATE TABLE from ALICE : -- Executed by DBA:

REVOKE CREATE TABLE FROM alice;

Result on 1Z0-071:

ALICE loses CREATE TABLE and can no longer create tables.

BOB retains CREATE TABLE and retains WITH ADMIN OPTION .

CHARLIE retains CREATE TABLE .

Objects previously created by ALICE , BOB , or CHARLIE are not dropped .

Why Do System Privileges Not Cascade?

System privilege grants are recorded directly against each user's security descriptor in the data dictionary without maintaining a parental dependency tree. Once granted, a system privilege belongs to that user independently of who granted it.

Data Dictionary Views for Auditing System Privileges

Oracle provides several static data dictionary views to inspect system privilege grants:

Data Dictionary View Scope and Description

USER_SYS_PRIVS Lists all system privileges granted directly to the current user (includes ADMIN_OPTION flag).

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

SESSION_PRIVS Lists all system privileges currently active and enabled in the user's current session (includes privileges inherited via active roles).

DBA_SYS_PRIVS Lists all system privileges granted to all users and roles across the entire database (requires DBA or catalog privileges).

  • Querying Active Session System Privileges -- Query all system privileges currently in effect for your session
  • SELECT privilege
  • FROM session_privs
  • ORDER BY privilege;
  • Querying Directly Granted System Privileges -- Query system privileges granted directly to your user account
  • SELECT username, privilege, admin_option
  • FROM user_sys_privs;
  • Oracle 1Z0-071 Exam Traps & Error Reference Matrix

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

ORA-01045 user <name> lacks CREATE SESSION privilege; logon denied User account was created using CREATE USER but has not been granted CREATE SESSION .

ORA-01950 no privileges on tablespace '<name>' User possesses CREATE TABLE system privilege but lacks quota on the default or target tablespace. Resolved via ALTER USER <name> QUOTA <size> ON <tablespace> .

ORA-01031 insufficient privileges User attempted to execute a DDL command without holding the required system privilege (e.g., executing CREATE TABLE without CREATE TABLE privilege).

ORA-01919 role '<name>' does not exist Attempted to grant a privilege to a non-existent role name.