Schema-Level Privileges in Oracle 26ai: Granular Access Control Without Granting on Every Object

/ 26ai New Features / By

The Privilege Gap Oracle DBAs Have Endured for Decades

For years, Oracle DBAs faced an uncomfortable choice when managing access control: grant privileges object by object (tedious and brittle) or grant system-wide ANY privileges like SELECT ANY TABLE (dangerously broad). Every time a new table or package was created in a schema, someone had to remember to run additional GRANT statements — or maintain custom triggers and scheduled jobs to automate re-grants.

Oracle Database 26ai eliminates this pain with schema-level privileges. A single GRANT statement now covers all current and future objects within a target schema, giving DBAs the secure middle ground they’ve always needed.

How Schema-Level Privileges Work

The new syntax extends the familiar GRANT command with an ON SCHEMA clause. When you grant a privilege at the schema level, Oracle automatically applies it to every qualifying object in that schema — including objects created after the grant is issued. No triggers, no scripts, no maintenance.

Schema-level grants are tracked in the DBA_SCHEMA_PRIVS dictionary view, making auditing straightforward.

Practical Examples

1. Grant SELECT on All Objects in a Schema

Grant a reporting account read access to every table and view in the HR schema — now and in the future:

GRANT SELECT ON SCHEMA hr TO reporting_user;

This single statement replaces what previously required dozens of individual GRANT SELECT ON hr.employees TO reporting_user commands — plus a mechanism to handle new tables as they appear.

2. Grant DML Privileges for an Application Service Account

In a microservices architecture, an application account often needs full DML access to its owning schema. Previously this meant granting INSERT, UPDATE, and DELETE on every table individually:

GRANT INSERT, UPDATE, DELETE ON SCHEMA orders TO order_service_acct;
GRANT SELECT ON SCHEMA orders TO order_service_acct;

The order_service_acct now has complete DML access to the ORDERS schema without needing INSERT ANY TABLE or any other system-level privilege that would leak access to unrelated schemas.

3. Grant EXECUTE on All Packages and Procedures

APIs exposed through PL/SQL packages are a common pattern. Grant execute access to an integration account across all program units in the API schema:

GRANT EXECUTE ON SCHEMA api TO integration_user;

Any new packages, procedures, or functions created in the API schema are automatically accessible to integration_user — zero follow-up work required.

4. Audit Schema-Level Grants with Privilege Analysis

Schema-level privileges integrate with Oracle’s existing DBMS_PRIVILEGE_CAPTURE framework. You can audit which schema-level grants are actually being used and right-size accordingly:

-- Create a capture to analyze privileges used by reporting_user
BEGIN
  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name        => 'schema_priv_audit',
    type        => DBMS_PRIVILEGE_CAPTURE.G_DATABASE,
    condition   => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''REPORTING_USER'''
  );
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE('schema_priv_audit');
END;
/

-- After a representative workload period:
BEGIN
  DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('schema_priv_audit');
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT('schema_priv_audit');
END;
/

-- Review used vs. unused privileges
SELECT * FROM DBA_USED_PRIVS WHERE CAPTURE = 'schema_priv_audit';
SELECT * FROM DBA_UNUSED_PRIVS WHERE CAPTURE = 'schema_priv_audit';

This lets you verify that a schema-level SELECT grant is appropriate — or discover that an account only accesses a handful of tables and could be scoped down further.

Revoking Schema-Level Privileges

Revocation follows the same clean syntax:

REVOKE SELECT ON SCHEMA hr FROM reporting_user;

This removes access to all objects in the schema in a single operation.

Key Takeaways

  • One GRANT, full coverage: Schema-level privileges apply to all existing and future objects in a schema — no custom triggers or re-grant scripts needed.
  • Least-privilege alignment: They fill the critical gap between per-object grants (too granular) and ANY system privileges (too broad), supporting true least-privilege security.
  • Supports all common privileges: SELECT, INSERT, UPDATE, DELETE, EXECUTE, and more — all at the schema level, all visible in DBA_SCHEMA_PRIVS.
  • Audit-ready: Full integration with DBMS_PRIVILEGE_CAPTURE means you can continuously validate that schema-level grants are right-sized.
  • Ideal for modern architectures: Multi-tenant databases, microservices, and CI/CD pipelines all benefit from simplified, automatable privilege management.
Scroll to Top