Hello everyone,
Well, it is just developer version so don’t try to install on your environments 🙂 My twitter is full of 23c tweets about these days. I also downloaded immediately and tested what I wanted the most for 4-5 years! “Schema Level Privileges” and I am so excited about it.
Preliminary Information
if you are a DBA or experienced developer, you already know about “privileges”. As a reminder, if a user wants to create a table on its own schema then, user must have “CREATE TABLE” privileges and that is fine. if a user wants to (must do) create a table on another users schema then, user must have “CREATE ANY TABLE” privilege. with this privilege user can create tables in other schemas too.
A simple example is, your developers need to create/drop/alter tables/packages/indexes on application user schemas. My problem starts at that point! Let’s say we have different applications and they have their own schemas (APP_USER1 and APP_USER2). these two application schemas are isolated in all aspects like they don’t use their objects, don’t connect to other schema and even so their developer teams are different.
I think you see where this is going. a developer (lets call him DEVELOPER1) in APP_USER1 development team must have “CREATE ANY TABLE” or “SELECT ANY TABLE” privilege to create new tables or select tables on APP_USER1 schema but this ANY privileges are too strong. when I grant those privileges, DEVELOPER1 is able to create and select ANY table in any schema the database (except SYS schema). So, developer1 must be creating/dropping/selecting objects in only APP_USER1 but now developer can do this on APP_USER2 schema too (on other schemas too).
For me that is a big security issue. a developer can access a highly sensitive data even if he/she shouldn’t! Until 23c 🙂 before 23c you could do this Oracle Database Vault which is a licensed option (extra cost) for Enterprise Edition.
I posted this idea around 4 years ago: https://forums.oracle.com/ords/apexds/post/grant-all-on-a-specific-schema-instead-of-any-privileges-0577 and finally it is arrived.
On 23c we now have SCHEMA LEVEL PRIVILEGES which allows grant a privilege on a specific schema.
Quick Demo
let’s create two application schemas (APP_USER1 and APP_USER2) and one developer user (DEVELOPER1).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE NO SQL> alter session set container = FREEPDB1; Session altered. SQL> create user app_user identified by app_user1; User created. SQL> create user app_user2 identified by app_user2; User created. SQL> create user developer1 identified by dev1; User created. SQL> grant resource to app_user1, app_user2; |
this developer edition 23c has default FREEPDB1 pdb, so I connected to it (well, I set my container) and create users. now grant:
|
1 2 3 4 5 6 7 |
SQL> grant connect to developer1; Grant succeeded. SQL> grant create any table on schema app_user1 to developer1; Grant succeeded. |
as you see we can use ON SCHEMA <schema name> structure anymore. One thing you should be careful is privilege is not “create table”, it is still CREATE ANY TABLE since user needs to create table in another schema. check:
|
1 2 3 4 5 6 7 8 9 10 11 |
sqlplus developer1/dev1@localhost:1521/freepdb1 SQL> create table app_user1.table1(id number); Table created. SQL> create table app_user2.table1(id number); create table app_user2.table1(id number) * ERROR at line 1: ORA-01031: insufficient privileges |
here it is. my developer can access only the schemas that she/he granted.
There are so many new features (as always) in new version but this one is something I wait for years. I didn’t even finish to learn everything on 19c and 21c. Oracle is faster than me 🙂 hope you enjoy it.
Wish you all healthy good days.