Managing object-level privileges in Oracle has always been granular — sometimes too granular. If you wanted a user to have SELECT on every table in a schema, you had to grant it table by table. Add a new table later? Grant again. Automate it? Write a script.
Oracle 23ai introduces schema-level privileges — the ability to grant a privilege on an entire schema with a single statement that automatically applies to all current and future objects in that schema.
Granting schema-level SELECT:
GRANT SELECT ANY TABLE ON SCHEMA hr TO reporting_user;reporting_user can now SELECT from any table (current or future) in the hr schema. No more individual grants per table.
Other supported schema-level privileges:
GRANT INSERT ANY TABLE ON SCHEMA app_data TO etl_user;
GRANT UPDATE ANY TABLE ON SCHEMA app_data TO etl_user;
GRANT DELETE ANY TABLE ON SCHEMA app_data TO etl_user;
GRANT EXECUTE ANY PROCEDURE ON SCHEMA hr TO app_user;
GRANT SELECT ANY SEQUENCE ON SCHEMA hr TO app_user;Revoking schema-level privileges:
REVOKE SELECT ANY TABLE ON SCHEMA hr FROM reporting_user;Key behavior: Schema privileges are dynamic. When a new table is created in hr, reporting_user immediately has SELECT on it — no additional grant required.
Checking existing schema-level grants:
SELECT grantee, privilege, schema
FROM dba_schema_privs
ORDER BY schema, grantee;Real-world impact
This dramatically simplifies privilege management for read-only reporting users, ETL processes, and microservice accounts that need access to a full schema rather than individual tables. It’s a long-requested feature that reduces the DBA overhead of schema management and eliminates entire categories of “who forgot to grant this?” support tickets.
