23c is Finally Here! Mustafa Kalaycฤฑ, 2023-04-06 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). Oracle PL/SQL 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; 1234567891011121314151617181920212223 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE NOSQL> 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: Oracle PL/SQL SQL> grant connect to developer1; Grant succeeded. SQL> grant create any table on schema app_user1 to developer1; Grant succeeded. 1234567 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: Oracle PL/SQL 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 1234567891011 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. Administration Development 23c23c new featuredeveloper editionnew featuresschema level privilege