Schema level privilege grants

Many modern applications separate the data owning schema from application service / run-time account used to access the data. This provides for separation of duty and supports the least privileged model and lowers the risk if the accessing account is compromised. But there is only one difficulty, how to manage the list of privileges for that run time account. Further as the application schema changes over the time, how do we keep up the list current? prior to Oracle 23c, developers had only two choices
 

• Grant individual privilege on each table and view in the application schema.
• Grant “ANY” privileges.

 
The first choice is inconvenient, because we need to identity every single table / view and do individual grant to every run time or service user. We can develop a script, but it is inconvenient. It is also a sub optimal way to deal with application schema changes such as adding new tables or views, because we must now remember to make corresponding privilege grants.
 
The second choice of granting ANY privilege while convenient is sub-optimal from security angle, because with that grant you gave the user the ability to select from any table in the database. If that user account is compromised, then your entire database is exposed.
 
To address these problems, Oracle 23c introduced a new SCHEMA level grants – that is if you say GRANT SELECT ANY TABLE ON SCHEMA SCOTT TO DEMO, then the user DEMO could see all tables / views in SCOTT schema, if any new table got created in SCOTT schema then DEMO user instantly has access to that new table. No additional management is needed, and we can continue to support the least privilege security model with appropriate separation of duties.
 
First let’s create users and few tables to test.
 
demo@FREEPDB1> grant create session,create table, unlimited tablespace
  2  to app_schema
  3  identified by "app_schema";
 
Grant succeeded.
 
demo@FREEPDB1> grant create session
  2  to app_user
  3  identified by "app_user";
 
Grant succeeded.
 
demo@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> create table t1( name varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t1 values ('Tom'),('Kyte');
 
2 rows created.
 
app_schema@FREEPDB1>
app_schema@FREEPDB1> create table t2( city varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t2 values ('Minnesota'),('Chicago');
 
2 rows created.
 
app_schema@FREEPDB1> commit;
 
Commit complete.
 
 
Now connect as APP_USER and verify if you can see the tables in APP_SCHEMA user account.
 
app_user@FREEPDB1> select * from session_schema_privs;
 
no rows selected
 
app_user@FREEPDB1> select * from app_schema.t1;
select * from app_schema.t1
                         *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
app_user@FREEPDB1> select * from app_schema.t2;
select * from app_schema.t2
                         *
ERROR at line 1:
ORA-00942: table or view does not exist
 
APP_USER could not select from APP_SCHEMA tables, because the user has no privileges on the objects / schema. Now lets grant schema privilege to APP_USER, so that APP_USER will get required permissions to view the data in APP_SCHEMA tables.
 
app_user@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> grant select any table on schema app_schema to app_user;
 
Grant succeeded.
 
Post the schema privilege grants the APP_USER can now be able to see all the data in APP_SCHEMA.
 
app_schema@FREEPDB1> conn app_user/app_user@freepdb1
Connected.
app_user@FREEPDB1> select * from app_schema.t1;
 
NAME
------------------------------
Tom
Kyte
 
app_user@FREEPDB1> select * from app_schema.t2;
 
CITY
--------------------
Minnesota
Chicago
 
app_user@FREEPDB1> select * from session_schema_privs;
 
PRIVILEGE                                SCHEMA
---------------------------------------- ---------------
SELECT ANY TABLE                         APP_SCHEMA
 
Here comes the good part, when APP_SCHEMA add a new table APP_USER should automatically have access to new tables.
 
app_user@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> create table t3( country varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t3 values ('USA'),('UK');
 
2 rows created.
 
app_schema@FREEPDB1> commit;
 
Commit complete.
 
app_schema@FREEPDB1> conn app_user/app_user@freepdb1
Connected.
app_user@FREEPDB1> select * from app_schema.t3;
 
COUNTRY
--------------------
USA
UK
 
app_user@FREEPDB1>
 
As you have seen, the new schema-level privileges make it easy to grant access to ALL of an applications data and objects, and as new objects are added to the schema there is no need to update grants for those new objects.