14.1 System Privileges

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:

1. Delegation: The grantee can grant the specific system privilege to any other database user or role.
2. Propagating the Admin Option: The grantee can choose whether to grant the privilege with or without WITH ADMIN OPTION to downstream grantees.
3. 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.
4. 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:

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

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

3. Step 3: BOB grants CREATE TABLE to CHARLIE without administrative rights:

   -- Executed by BOB:
   GRANT CREATE TABLE TO charlie;
   

   State: CHARLIE can create tables.

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

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