Introduction:
In the world of database management, developers used to face a tough decision – either spend time granting specific permissions for each table or take the risky route of giving broad access to everything. The first option was a hassle, needing scripts and causing headaches with schema changes. The second option, while easy, put the whole database at risk if an account got compromised.
But here comes Oracle Database 23c with a game-changing solution! It introduces a new way of granting access at the schema level. This means you can give someone access to all tables and views within a specific schema without the risk of compromising the entire database. No more headaches when new tables are added – access is automatically granted.
In the past, developers faced two main choices:
- Grant individual privileges for each table and view in the application schema.
- Grant ANY privileges, allowing the user to select from any table, update any table, and so forth.
The first option is inconvenient as it requires identifying each table or view and granting permissions individually, potentially necessitating the development of scripts. It also becomes cumbersome when dealing with changes in the application schema, such as the addition of new tables or views.
The second choice of granting ANY privileges is convenient but compromises security by providing the user access to every table in the entire database. A compromised account could lead to a breach of the entire database.
Enter Oracle Database 23c, which introduces a solution in the form of a new schema-level grant. With this enhancement, if you GRANT SELECT ANY TABLE ON SCHEMA HR TO ALEX, the user gains access to all tables and views exclusively within the HR schema. Should a new table be added to the schema, access is automatically granted without additional management steps. This ensures a least-privilege security model with effective separation of duties.
Users can grant schema-level privileges on their own schema without requiring special privileges. To grant such privileges on another person’s schema, one needs either the GRANT ANY SCHEMA or GRANT ANY PRIVILEGE system privilege.
For visibility into granted schema privileges, consult the DBA_SCHEMA_PRIVS view. Additionally, the ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, and SESSION_SCHEMA_PRIVS views offer insights into schema-level privileges within the Oracle Database 23c environment. This enhancement simplifies schema management, ensuring security and efficiency as applications evolve.
