Schema-level privilege grants with Database 23c

December 8, 2023 | 2 minute read
Russ Lowenthal
Product Manager, Database Security
This is a syndicated post, view the original post
Text Size 100%:

Many modern applications separate the data-owning schema from the application service or run-time account used to access that data. This schema provides for a separation of duty and the least-privilege model and can help lower the risk if the accessing account is compromised. But how do you manage this list? As application schemas change over time, how do you keep that list current?

Previously, developers had the following options:

  • Grant individual privileges on each table and view in the application schema
  • Grant ANY privileges: Select any table, update any table, and so on.

The first choice may be inconvenient because you need to identify every single table or view and then grant every run-time or service user permission individually. You could develop a script, but that’s an extra step. This option is also a suboptimal way to deal with application schema changes, such as adding new tables or views, because you must now remember to make corresponding privilege grants. Also, visualizing and verifying such detailed lists can be daunting.

The second choice of granting ANY privileges, while convenient, is suboptimal from a security angle because you grant that user the ability to select from every table in the database! If this user account is compromised, your entire database can be compromised.

Oracle Database 23c to the rescue

To address this, Oracle Database 23c introduces a new schema-level grant. If you GRANT SELECT ANY TABLE ON SCHEMA HR TO BOB, that user can see all the tables and views in the HR schema - and only in the HR schema. If a new table is added to the schema, they instantly have access to that new table. No extra management is needed, and you continue to support a least-privilege security model with appropriate separation of duties.

A gif showing the old process of granting privileges to a user versus the new process with 23c.

Users can grant schema-level privileges on their own schema without having any special privileges. To grant schema-level privileges on someone else’s schema, you need either the GRANT ANY SCHEMA or GRANT ANY PRIVILEGE system privilege.

To see which schema privileges have been granted, use the DBA_SCHEMA_PRIVS view. You can also use ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, and SESSION_SCHEMA_PRIVS views.


Try it out today! Oracle Database 23c Free is available now. If you have questions or suggestions, join the Database 23c Free forum.

For a scripted tutorial to walk you through the feature, see SchemaPrivileges on GitHub.

Russ Lowenthal

Product Manager, Database Security

  Russ Lowenthal is the Senior Director of Product Management for Database Security, focused on database encryption, access control, audit, and monitoring.
  Russ is based in Orlando, Florida, USA and has been with Oracle for over twenty years. Leveraging over thirty years of experience in IT including database, UNIX systems and network administration, he now advises Oracle's customers on secure implementations of information systems technology.
  Russ' certifications include Certified Information System Security Professional (CISSP), Certified Information Systems Auditor (CISA), Certified Information Systems Manager (CISM), Oracle Certified Master (OCM),  Microsoft Certified Systems Engineer (MCSE) and Certified Technical Trainer (CTT). 


Show more