23c is Finally Here!

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).

this developer edition 23c has default FREEPDB1 pdb, so I connected to it (well, I set my container) and create users. now grant:

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:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *