Introduction
One of my favourite improvements in 23c is Schema level privileges. Over the years I have had so many use cases where I needed to give a user the ability to create a table or a procedure in another schema. In the past you needed to issue the grant "CREATE ANY TABLE" or "CREATE ANY PROCEDURE" to the user. Not only did this allow the user to create and drop the table/procedure in the schema required, it allowed the user to drop and create the table/procedure in ANY schema. This is quandary is something every Oracle DBA would have come across in the past. Do I grant the permission? Everything in your bones says no, but your manager and his manager says do it. The developers need the access to "help" them manage their code.
Well that quandary has now been eased somewhat with schema level privileges. I say somewhat, because it is still poor security practice to grant users system privileges to a schema, especially in production or live database. At least now as DBAs when forced down this route by managers and poor application design, we can limit the damage.
Grant a System Privilege to a Schema
GRANT SELECT ANY TABLE ON SCHEMA <my_schema> TO <some_other_user>;Revoke a System Privilege to a Schema
REVOKE SELECT ANY TABLE ON <my_schema> HR FROM <some_other_user>;System Privileges Excluded Form Schema Privileges
The following table details what system privileges are excluded from schema privileges
| System Privilege | Privilege |
|---|---|
| Advisor framework | ADVISOR |
| ADMINISTER SQL TUNING SET | |
| Application context | CREATE ANY CONTEXT |
| DROP ANY CONTEXT | |
| Application continuity | KEEP DATE TIME |
| KEEP SYSGUID | |
| Database change notification | CHANGE NOTIFICATION |
| Database links | CREATE DATABASE LINK |
| CREATE PUBLIC DATABASE LINK | |
| DROP PUBLIC DATABASE LINK | |
| Database triggers | ADMINISTER DATABASE TRIGGER |
| Debugging | DEBUG CONNECT SESSION |
| Dictionary protection | SELECT ANY DICTIONARY |
| ANALYZE ANY DICTIONARY | |
| Directories | CREATE ANY DIRECTORY |
| DROP ANY DIRECTORY | |
| READ | |
| WRITE | |
| Editions | CREATE ANY EDITION |
| DROP ANY EDITION | |
| Exports and imports | EXPORT FULL DATABASE |
| IMPORT FULL DATABASE | |
| Flashback | FLASHBACK ARCHIVE ADMINISTER |
| SELECT ANY TRANSACTION | |
| Key management | ADMINISTER KEY MANAGEMENT |
| Logminer | LOGMINING |
| Plan management | ADMINISTER SQL MANAGEMENT OBJECT |
| Pluggable databases | CREATE PLUGGABLE DATABASE |
| SET CONTAINER | |
| Profiles | CREATE PROFILE |
| ALTER PROFILE | |
| DROP PROFILE | |
| Public synonyms | CREATE PUBLIC SYNONYM |
| DROP PUBLIC SYNONYM | |
| Recycle bin | PURGE DBA_RECYCLEBIN |
| Resource management | ADMINISTRATE RESOURCE MANAGER |
| Resumable space allocation | RESUMABLE |
| Roles | CREATE ROLE |
| DROP ANY ROLE | |
| GRANT ANY ROLE | |
| ALTER ANY ROLE | |
| Rollback segment | CREATE ROLLBACK SEGMENT |
| ALTER ROLLBACK SEGMENT | |
| DROP ROLLBACK SEGMENT | |
| Sessions | CREATE SESSION |
| ALTER SESSION | |
| RESTRICT SESSION | |
| Stored outlines | CREATE ANY OUTLINE |
| ALTER ANY OUTLINE | |
| DROP ANY OUTLINE | |
| System | ALTER DATABASE |
| ALTER SYSTEM | |
| AUDIT SYSTEM | |
| ALTER RESOURCE COST | |
| Tablespaces | CREATE TABLESPACE |
| ALTER TABLESPACE | |
| MANAGE TABLESPACE | |
| DROP TABLESPACE | |
| UNLIMITED TABLESPACE | |
| Transactions | FORCE TRANSACTION |
| FORCE ANY TRANSACTION | |
| Users | CREATE USER |
| BECOME USER | |
| ALTER USER | |
| DROP USER |
Published 23rd June 2023
