Schema Privileges in Oracle Database 23ai

In version 23ai, the new schema privilege feature allows administrators to grant access to all existing and future objects within a schema with a single command. This simplifies privilege management and enables users to create new objects within the schema. Below are the key features of this functionality, along with examples for practical usage:

Key Features of Schema Privilege:

• Simplified Access Granting: Instead of using object privileges to grant access to each object individually, Schema Privilege allows you to grant access to all current and future objects of a schema with a single command.

• Integration with System Privileges: Schema Privilege works in combination with system privileges. For example, you can grant the SELECT ANY TABLE privilege (a system privilege) for a specific schema to a user. This limits access to only the objects within the specified schema, preventing excessive privileges.

• Granularity of Control: By utilizing Schema Privileges, you avoid granting unnecessary permissions, which enhances security and simplifies privilege management.

Exampls

1. Granting Select and Insert Privileges on a Schema:
Using the following command, the user VAHID is granted the ability to select data from and insert data into the tables of schema BEHNAM:

SQL> GRANT SELECT ANY TABLE,INSERT ANY TABLE ON SCHEMA behnam TO VAHID;
Grant succeeded.

When logged in as VAHID, the granted privileges can be verified:

SQL> show user
USER is "VAHID"
SQL> select * from SESSION_SCHEMA_PRIVS;
PRIVILEGE            SCHEMA
-------------------- ----------
INSERT ANY TABLE     BEHNAM
SELECT ANY TABLE     BEHNAM

SQL> select count(*) from behnam.tbl1;
  COUNT(*)
----------
        80

SQL> insert into behnam.tbl1 select * from behnam.tbl1;
80 rows created.

SQL> delete behnam.tbl1;
ORA-41900: missing DELETE privilege on "BEHNAM"."TBL1"

Result:
The user can select and insert data into tables in the BEHNAM schema but cannot delete data without additional privileges.

2. Granting Privileges with WITH ADMIN OPTION:
To allow the user VAHID to grant the same privileges to other users, use the WITH ADMIN OPTION clause:

SQL> show user
USER is "SYS"
SQL> GRANT SELECT ANY TABLE,INSERT ANY TABLE ON SCHEMA behnam TO VAHID WITH ADMIN OPTION;
Grant succeeded.

This allows VAHID to grant privileges to others, such as NIMA:

SQL> show user
USER is "VAHID"
SQL> GRANT SELECT ANY TABLE,INSERT ANY TABLE ON SCHEMA behnam TO NIMA;
Grant succeeded.

The privileges can then be reviewed:

SQL> select GRANTEE,PRIVILEGE,SCHEMA,ADMIN_OPTION from DBA_SCHEMA_PRIVS;
GRANTEE    PRIVILEGE            SCHEMA     ADM
---------- -------------------- ---------- ---
NIMA        INSERT ANY TABLE     BEHNAM     NO
NIMA        SELECT ANY TABLE     BEHNAM     NO
VAHID       INSERT ANY TABLE     BEHNAM     YES
VAHID       SELECT ANY TABLE     BEHNAM     YES

3. Granting the Privilege to Create Objects in a Schema:
To allow a role (r1) to create tables in the BEHNAM schema, use the following command:

SQL>  GRANT CREATE ANY TABLE ON SCHEMA behnam TO r1;
Grant succeeded.

The granted privilege can be verified:

SQL> select ROLE,PRIVILEGE,SCHEMA,ADMIN_OPTION from ROLE_SCHEMA_PRIVS;
ROLE       PRIVILEGE            SCHEMA     ADM
---------- -------------------- ---------- ---
R1         CREATE ANY TABLE     BEHNAM     NO

4. Limitations with the SYS Schema:
Schema Privileges cannot be effectively applied to the SYS schema. For example:

SQL> show user
USER is "SYS"
SQL> create table systbl(id number);
Table created.
SQL> insert into systbl values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sys.systbl;
        ID
----------
         1
SQL> GRANT SELECT ANY TABLE ON SCHEMA sys TO VAHID;
Grant succeeded.

Although the privilege is granted, attempting to access a table in the SYS schema results in an error:

SQL> show user
USER is "VAHID"
SQL> select * from SESSION_SCHEMA_PRIVS;
PRIVILEGE                      SCHEMA
------------------------------ ----------
SELECT ANY TABLE               SYS

SQL> select * from sys.systbl;
ORA-00942: table or view does not exist

Important Note:
Some system privileges are not applicable or meaningful for Schema Privileges. For instance:

CREATE TABLESPACE
ALTER TABLESPACE
CREATE PROFILE
ALTER PROFILE
ADMINISTER SQL TUNING SET
And others.
This feature enhances privilege management by allowing more efficient and secure control over schema-level permissions.

Vahid Yousefzadeh
Oracle Database Administrator
Telegram channel :https://t.me/oracledb