A Long-Awaited Capability: Granting Schema-Level Privileges

Oracle 23c introduces a powerful and much-requested feature: the ability to grant privileges at the schema level. This is a significant shift in how database security and access management can be handled, simplifying what has historically been a tedious and error-prone process. In past I witnessed a significant level of Client frustration caused by the lack of this feature.

The Old Way: Object-by-Object Grants

In previous versions of Oracle, before the release that introduced the feature, granting access to objects within a schema required explicit privileges on each individual object—tables, views, procedures, and so on. If you wanted a user to have read or write access across an entire schema, you had two main options:

1. Manual Grants: Issue GRANT statements for every object. This approach risks to quickly become unmanageable in schemas with hundreds or thousands of objects, specially with projects under development which change existing database objects or deploy new ones.
2. Scripts or Automation: Use PL/SQL scripts to dynamically generate and execute grants. While more scalable, this added complexity and required ongoing maintenance as new objects were created. One extra-task for application DBa’s !!

Because of this overhead, DBAs often leaned toward broader privileges (like SELECT ANY TABLE), which introduced unnecessary security risks by extending access beyond the intended schema.

Oracle 23c: Schema-Level Privileges

With Oracle 23c, you can now grant privileges directly on a schema. This allows you to provide access to all current and future objects within that schema using a single statement.

For example from Oracle documentation:

GRANT SELECT ANY TABLE ON SCHEMA HR TO psmith;

This command grants the reporting_user the ability to query all tables and views within the hr schema—without needing to grant access object by object. Even better, this privilege automatically applies to new objects created in the schema, eliminating the need for additional grant management.

If you want the user only to be able to query tables, views, materialized views, or synonyms, then you should grant the READ object privilege. Another example:

CONNECT SYSTEM@hrpdb
Enter password: password
Connected.
GRANT READ ON employees TO c##hr_admin CONTAINER=CURRENT;

Key Benefits

1. Simplified Administration
Application DBAs no longer need to maintain complex scripts or manually track object-level privileges. A single grant can cover an entire schema.

2. Improved Security Posture
Instead of over-granting system privileges, access can now be scoped precisely to a schema. This aligns better with the principle of least privilege.

3. Reduced Operational Overhead
As schemas evolve, new objects are automatically covered under existing schema-level grants. This removes the need for continuous privilege updates.

4. Cleaner Role Design
Roles can now encapsulate schema-level access, making privilege models easier to understand and audit.

Things to Consider

While schema-level privileges are a major improvement, they should still be used thoughtfully:

• They apply broadly to all object types within a schema—ensure this aligns with your access requirements.
• Fine-grained control (e.g., excluding specific tables) still requires object-level grants or additional design considerations.
• Auditing and monitoring remain essential to ensure privileges are not misused.

References:

1. Database Security Guide: Configuring Privilege and Role Authorization
2. AskTOM discussion: Schema Privileges and Grant option

Final Thoughts

The introduction of schema-level privilege grants in Oracle 23c is a welcome enhancement that addresses a long-standing gap in Oracle’s security model. It strikes a strong balance between ease of administration and adherence to security best practices.

For teams managing large and dynamic schemas, this feature alone can significantly reduce complexity while improving control. It’s a clear example of Oracle evolving to meet the practical needs of modern database environments.