Oracle Database 23c Schema Privileges

TL;DR

Oracle Database 23c introduces schema privileges to grant access at the schema level with a single command.

Briefing Cornell Notes

Briefing

Oracle Database 23c introduces “schema privileges,” a feature designed to make it easier—and safer—to grant an application user access to every table, view, and related objects inside a schema. Instead of issuing repetitive grants for each object, administrators can now grant privileges at the schema level using a single command. That shift matters because it reduces the common operational burden of tracking every table and view an application needs, and it prevents the frequent mistake of granting broad access table-by-table as schemas evolve.

Before 23c, granting access to an application’s data typically meant either granting SELECT on each individual table (a tedious approach that also increases the chance of missing objects) or manually repeating grants whenever new tables, views, or PL/SQL packages were added to the schema. The result was a maintenance cycle: object creation required follow-up privilege work to keep the application functioning. Schema privileges remove that friction by allowing one grant to cover the entire schema, so newly added objects automatically fall under the granted access.

A practical example shows the difference. Starting as the application user, the user has no privileges on the application schema, so attempts to query tables like PEOPLE and CITY fail in a way that even prevents the user from seeing the data. After the schema owner grants SELECT on the entire schema to the application user, the same application user can immediately query both PEOPLE and CITY and retrieve rows.

The example then adds a new table named COUNTRY to the schema owner’s schema, inserts rows such as “Toronto, Canada” and “Paris, France,” and switches back to the application user. With schema-level privileges in place, the application user can query the new COUNTRY table without any additional grants—demonstrating the core benefit: privileges stay aligned as the schema grows.

The transcript also clarifies operational boundaries. A schema owner can grant privileges within their own schema, but granting schema privileges to someone else’s schema requires additional system privileges: either “grant any schema privilege” or “grant any privilege.” For visibility and troubleshooting, Oracle provides views to check what schema privileges exist. “DBA_SCHEMA_PRIVS” and “ROLE”/user-focused counterparts help identify granted schema privileges, while “SESSION_SCHEMA_PRIVS” shows what the current session has.

Overall, schema privileges in Oracle Database 23c streamline access management for applications by replacing object-by-object grants with schema-level grants that automatically extend to new tables and views, reducing both administrative overhead and the risk of broken access when schemas change.

Cornell Notes

Oracle Database 23c adds “schema privileges,” letting administrators grant access to an entire schema with one command instead of granting privileges table-by-table. After a schema owner grants SELECT on the schema to an application user, that user can query any existing table or view in the schema. When the schema owner later creates a new table (example: COUNTRY) and inserts rows, the application user can immediately query the new table without additional grants. The feature reduces maintenance work and helps prevent missed privilege updates when new objects are added. Oracle also provides catalog views (e.g., DBA_SCHEMA_PRIVS and SESSION_SCHEMA_PRIVS) to inspect granted schema privileges and what the current session can access.

What problem do schema privileges in Oracle Database 23c solve compared with earlier privilege management?

Earlier approaches required granting privileges on each table and view individually (e.g., SELECT on table1, SELECT on table2, and so on). That became a maintenance burden because any newly added tables, views, or PL/SQL packages required additional grants to keep the application working. Schema privileges replace that object-by-object workflow with a single schema-level grant, so access automatically applies to all objects within the schema, including ones added later.

How does the example demonstrate the practical impact of schema privileges for an application user?

The application user initially has no privileges on the application schema, so queries against tables like PEOPLE and CITY fail to return data. After the schema owner grants SELECT on the entire schema to the application user, the same queries succeed and data becomes visible. The example then creates a new table named COUNTRY with rows like “Toronto, Canada” and “Paris, France.” When the application user queries COUNTRY afterward, it works immediately—showing that schema-level privileges extend to newly created objects.

What permissions are required to grant schema privileges on someone else’s schema?

The transcript notes that a schema owner can grant privileges within their own schema. To grant schema privileges on another schema, the user needs either “grant any schema privilege” or “grant any privilege,” reflecting the additional authority required to manage privileges outside one’s own schema.

Which database views help administrators understand what schema privileges exist and what a session has?

For identifying schema privileges, Oracle provides views such as DBA_SCHEMA_PRIVS and role/user schema privilege views (mentioned as DBA schema prevs role and user schema privs). To see what schema privileges apply to the current session, the transcript points to SESSION_SCHEMA_PRIVS.

Why is granting SELECT at the schema level considered operationally safer than granting it per table?

Granting per table forces administrators to remember every object and to repeat grants whenever new objects appear. Schema-level grants reduce the chance of missing a newly created table or view, because the privilege scope is tied to the schema rather than a fixed list of objects.

Review Questions

In the example workflow, what changes after the schema owner grants SELECT on the entire schema to the application user?
How does schema privileges affect access when a new table is created after the initial grant?
What system privileges are required to grant schema privileges on a different user’s schema?

Key Points

1
Oracle Database 23c introduces schema privileges to grant access at the schema level with a single command.
2
Schema-level grants remove the need to issue separate grants for each table or view in an application schema.
3
Privileges granted at the schema level automatically apply to new objects added later, such as newly created tables and views.
4
A schema owner can grant privileges within their own schema, but granting schema privileges on other schemas requires “grant any schema privilege” or “grant any privilege.”
5
Oracle provides catalog views like DBA_SCHEMA_PRIVS and SESSION_SCHEMA_PRIVS to inspect granted schema privileges and current session access.
6
Schema privileges reduce the risk of broken application access caused by forgetting to grant privileges on newly added objects.

Highlights

Schema privileges let administrators grant SELECT on an entire schema in one step, instead of repeating grants for every table and view.

After schema-level SELECT is granted, newly created tables (like COUNTRY) become queryable by the application user without additional privilege work.

Granting schema privileges outside one’s own schema requires elevated system privileges: “grant any schema privilege” or “grant any privilege.”

SESSION_SCHEMA_PRIVS provides a quick way to see what schema privileges apply to the current session.

Topics

Schema Privileges
Oracle Database 23c
Database Security
Privilege Management
Access Control

Mentioned

Russ Laurenthal