Written by Skant Gupta, Oracle Certified Cloud Professional and Database/Technical Consultant at Blue Crystal Solutions
Oracle Database 23ai
Schema Privileges Overview
System privileges can now be assigned at the schema level, simplifying privilege management and enhancing database security in Oracle Database 23ai. When a schema privilege is granted, the recipient gains system privileges over all objects within that schema, both current and future.
For instance, if you grant the system privilege CREATE ANY TABLE to a user like Mike for the APPS schema, Mike can create tables only within the APPS schema, not in any other schema where he lacks permissions. Schema privileges can be granted to users or roles. While many system privileges can be granted at the schema level, not all can. Additionally, schema privileges cannot be granted for the SYS schema. Since schema privileges confer significant access, they should be granted only to trusted users.
Advantages of Granting Schema Privileges
Granting schema privileges provides several benefits:
- Enforcing the Principle of Least Privilege: By granting schema privileges instead of broader system privileges, administrators avoid overly permissive access. A system privilege applies across all objects in the database, but a schema privilege restricts the user or role to only those objects within the specified schema. This minimizes unnecessary access and enhances security.
- Simplified Privilege Management: Assigning schema privileges eliminates the need to grant individual object or system privileges to each user. By granting a privilege at the schema level, the user gains access to all current and future objects in the schema, streamlining privilege management.
Finding Schema Privileges
To view schema privilege grants, you can query the following data dictionary views:
• DBA_SCHEMA_PRIVS
• ROLE_SCHEMA_PRIVS
• USER_SCHEMA_PRIVS
• SESSION_SCHEMA_PRIVS
• V$ENABLEDSCHEMAPRIVS
Granting a Schema Privilege
The GRANT statement can be used to assign a schema privilege to a user or role.
For example, if you grant the SELECT ANY TABLE privilege on the APPS schema to user Mike, Mike will have the ability to query both existing and future tables in the APPS schema.
GRANT SELECT ANY TABLE ON SCHEMA APPS TO mike;
Revoking a Schema Privilege
To revoke a schema privilege from a user or role, you can use the REVOKE statement. First, identify the schema privileges granted to the user or role with a query like this:
SELECT PRIVILEGE, SCHEMA FROM DBA_SCHEMA_PRIVS WHERE GRANTEE = ‘MIKE’;
The output will display like:
PRIVILEGE SCHEMA
–——————– –———–
SELECT ANY TABLE APPS
To revoke the privilege, you can use the REVOKE statement, as shown below:
REVOKE SELECT ANY TABLE ON SCHEMA APPS FROM mike;
Database security enhancements
The introduction of schema-level privileges provides several key security posture benefits for database management:
• Enhanced Security by Enforcing the Principle of Least Privilege (PoLP)
• Simplified Privilege Management
• Reduced Risk of Privilege Escalation
• Granular Control Over Access
• Compliance with Security Policies
Contact us to learn more about these new Oracle Database 23ai features.
About the author: Skant Gupta
Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g, and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on cloud, database, and high availability solutions, Oracle WebLogic Suite, and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and India. He is now a key member of our Oracle team here at Blue Crystal Solutions.
