How to Grant Schema Privileges in Oracle

Granting SELECT table by table is tedious, and every new table needs another grant. Granting SELECT ANY TABLE opens every schema in the database. Schema privileges, new in Oracle AI Database 26ai, sit in between: a system privilege such as SELECT ANY TABLE limited to one schema, covering its existing and future tables, and nothing else.

Code for This Guide

The main examples are in the examples/security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

grant system_privilege on schema schema to {user | role}
revoke system_privilege on schema schema from {user | role}

Typical schema privileges are SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, and EXECUTE ANY PROCEDURE, each limited to the named schema.

A Role for Reading the Schema

The examples use the application user NIMBUS_APP from how to create users with profiles in Oracle. First a role is created and granted to it.

Example:

create role nimbus_reader;
grant nimbus_reader to nimbus_app;

select granted_role, admin_option, default_role
from   dba_role_privs
where  grantee = 'NIMBUS_APP';

Output:

Role NIMBUS_READER created.

Grant succeeded.

GRANTED_ROLE     ADMIN_OPTION    DEFAULT_ROLE
________________ _______________ _______________
NIMBUS_READER    NO              YES

The role is a default role, enabled at every login.

Grant SELECT ANY TABLE on the Schema

Example:

grant select any table on schema nimbus to nimbus_reader;

select privilege, schema, grantee from dba_schema_privs where grantee = 'NIMBUS_READER';

Output:

Grant succeeded.

PRIVILEGE           SCHEMA    GRANTEE
___________________ _________ ________________
SELECT ANY TABLE    NIMBUS    NIMBUS_READER

DBA_SCHEMA_PRIVS lists schema privileges. The grant goes to the role, so every user with the role gets it.

The Effect

Connected as NIMBUS_APP, the role is active and the customers it could not see before are now visible.

Example:

connect nimbus_app/"Fly#Nimbus2026"@localhost:1521/FREEPDB1

select role from session_roles;
select count(*) as customers_now_visible from nimbus.customers;

Output:

ROLE
________________
NIMBUS_READER

   CUSTOMERS_NOW_VISIBLE
________________________
                     120

Tables created in NIMBUS later are covered automatically, while other schemas stay closed.

Things to Know

• Schema privileges cover views and future objects of the schema, unlike object grants.
• Prefer them to ANY privileges without ON SCHEMA, which apply to every schema in the database.
• Roles are not active inside definer's rights PL/SQL; code that needs the access requires direct grants.

Related Guides

• How to Grant Object Privileges in Oracle

Conclusion

Schema privileges grant a system privilege for one schema only, covering its current and future objects. Grant them to roles, and use them instead of per-table grants or database-wide ANY privileges for read-only and application access.