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:
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.
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.
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 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 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).
Next Post