Schema-level privilege grants with Database 23ai

Oracle Database 23c introduces a new schema-level privilege system aimed at simplifying and securing application access to database objects. Traditionally, developers could either assign privileges on individual tables and views or grant broader ANY privileges, each with drawbacks. Granting individual privileges is tedious and error-prone, requiring ongoing updates when new tables or views are added. Meanwhile, ANY privileges offer excessive access, posing security risks if the account is compromised.

With Oracle 23c, a more secure and manageable option is available. The new schema-level privilege allows developers to grant access to an entire schema’s tables and views in one command, such as GRANT SELECT ANY TABLE ON SCHEMA HR TO BOB. This approach maintains access strictly within the specified schema, supporting a least-privilege model that auto-updates access rights when new tables are added—without further intervention. This advancement strengthens security by ensuring that only relevant data within a specified schema is accessible, reducing potential risks.

-- The OLD way to do this...
GRANT SELECT ANY TABLE TO HR;
-- or
GRANT SELECT ON
    PROD.CUSTOMERS,
    PROD.SALES,
    PROD.ADDRESSES,
    PROD.PAYMENTS,
    ...
TO HR;

-- With 23c schema-level permissions
GRANT SELECT ANY TABLE ON SCHEMA PROD TO HR;

In Oracle Database 23c, users can grant schema-level privileges within their own schema without needing special permissions. However, to grant schema-level privileges on another user’s schema, the user must possess either the GRANT ANY SCHEMA or GRANT ANY PRIVILEGE system privilege.

To monitor and review schema-level privileges granted, Oracle provides several views:

• DBA_SCHEMA_PRIVS: Shows all schema-level privileges across the database.
• ROLE_SCHEMA_PRIVS: Lists schema privileges granted to roles.
• USER_SCHEMA_PRIVS: Displays schema privileges granted to specific users.
• SESSION_SCHEMA_PRIVS: Indicates schema-level privileges active in the current session.

These views help administrators track access and ensure compliance with security policies.