23ai New Feature: GRANT SELECT ANY TABLE ON SCHEMA

Another 23c or 23ai feature that you will be happy embracing!
In previous Oracle versions, what did you do when someone requested read only access to a schema?
Most likely, as a good DBA, you created roles that would give users read only access. Here is an example. Let’s say I have the HR schema in my database, with tables, views and other objects. I created the HR_RO, for read only access role. This role contains all the select grants on existing HR tables and views. Each time a new table is added to the HR schema, the HR_RO role needs to be maintained and updated. If a user requires read only access to HR schema, then the user is granted the HR_RO role. This method is what I use and apply in my existing databases.

23ai makes the DBA’s life easier, by introducing the SELECT ANY TABLE ON SCHEMA privilege! If a user requires read only access to a schema, then you no longer need to create a role and maintain it.
You grant select access once to the schema and you are set, for all existing tables and new tables that will be created in the future!

grant select any table on schema HR to diana;

I think this feature is great for the following two reasons:

1. you no longer need to maintain a role to be able to grant read only access on a schema.
2. some DBAs are granting SELECT ANY TABLE privilege, which would give user access not only to the schema that is required, but to all the schemas. With this new feature, you no longer have to grant SELECT ANY TABLE privilege.

This feature might work against the least privileged access requirement. If a user needs access to only 5 tables out of the 10 that a schema owns, then do not use this type of grant, since the user will gain access to the whole schema.

Once you issue the grant, where can you see it in the data dictionary?

The grants will not show up in the well known dictionary views: DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS. Instead use the following dictionary view, that was also introduced with this feature: DBA_SCHEMA_PRIVS

select grantee, privilege, schema
from DBA_SCHEMA_PRIVS 
where grantee='DIANA';

GRANTEE    PRIVILEGE		  SCHEMA
---------- -----------------  ------- 
DIANA	   SELECT ANY TABLE    HR

Does the SELECT ANY TABLE ON SCHEMA privilege give access to views as well? How about sequences? Let’s find out by testing it:

SQL> grant select any table on schema hr to diana;

Grant succeeded.

-- check what views exist under HR schema
SQL> select view_name from dba_views where owner ='HR';

VIEW_NAME
-------------------------------------------------------
EMP_DETAILS_VIEW

SQL> conn diana/***@PDB1

-- it works on views
SQL> select count(*) from hr.EMP_DETAILS_VIEW;

  COUNT(*)
----------
       106

-- it doesn't work for sequences

SQL> select hr.DEPARTMENTS_SEQ.nextval from dual;
select hr.DEPARTMENTS_SEQ.nextval from dual
          *
ERROR at line 1:
ORA-41900: missing READ privilege on "HR"."DEPARTMENTS_SEQ"

The results of the test above show us that grant on views are included in the select any table on schema privilege, but selects (read) on sequences are not.

In conclusion, I think this is a great feature. It can simplify the maintenance of users and roles!

What do you think? do you like this feature?