Schema-level privilege grants with Database 23ai

May 2, 2024 | 2 minute read
Russ Lowenthal
Vice President, Database Security
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 23ai to the rescue

To address this, Oracle Database 23ai 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.

Conclusion

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

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

Russ Lowenthal

Vice President, Database Security

Russ Lowenthal is the Vice President for Database Security, focused on database encryption, access control, audit, and monitoring.

 

Russ is based in Greenville, North Carolina, USA and has been with Oracle for over twenty-five years. Leveraging over thirty-five 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

Previous Post

SQL Firewall now built into Oracle Database 23ai

Vipin Samar | 5 min read

Next Post


The Achilles Heel of Cybersecurity: Lessons from a Recent Data Breach

Vipin Samar | 11 min read
Oracle Chatbot
Disconnected