Up until 23c we can either make direct grants on a schemas objects such as tables, views or procedures to another schema or user OR we can grant say SELECT ANY TABLE or EXECUTE ANY PROCEDURE but the issue then is that the grantee gets access to all other schemas objects in the database except SYS (provided the database supports o7_dictionary_accessibility and its turned on). Since 23c we now have dictionary protection that can be used to protect other Oracle managed users from system %ANY% privileges.
So, this is a new right that allows the equivalence of a SYSTEM %ANY% grant but only to one schema. Lets test this:
C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 14:06:36 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> grant select any table on schema va to vc;
Grant succeeded.
SQL>
We can also make a PL/SQL grant as well with this syntax:
SQL> grant execute any procedure on schema va to vc;
Grant succeeded.
SQL>
So, in this example VC can now use SELECT ANY TABLE and EXECUTE ANY PROCEDURE on VA objects. as a security auditor I want to know how I can check which grants have been made in a database and review and advise on them. For normal direct grants they are visible in DBA_TAB_PRIVS and role grants in DBA_ROLE_PRIVS and system grants in DBA_SYS_PRIVS. We can also use ALL_%, CDB_% as well in some circumstances but the DBA_% views are more consistent at the database level.
Let's first check these standard views for the new grants made above:
SQL> select * from dba_sys_privs where grantee='VC';
no rows selected
SQL> select * from dba_tab_privs where grantee='VC';
no rows selected
SQL> select * from dba_role_privs where grantee='VC';
GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF COM INH
--- --- --- --- ---
VC
CONNECT
NO NO YES NO NO
SQL>
The grants are not stored in the standard views BUT we didn't expect that really!. We will come back to the recording of the grants in a few minutes.
So, we granted SELECT ANY TABLE to VC on VA's tables and at the time of the grant VA had one table called TEST. What if we connect to VA and create two more tables, are they now also granted?
SQL> connect va/va@//192.168.56.18:1521/freepdb1
Connected.
SQL> create table test3 (col01 number);
Table created.
SQL> create table test4 (col01 number);
Table created.
SQL>
Show tables now for the VA user:
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
TEST3
TEST4
SQL>
Only TEST was available when the grant made on VA objects to VC for SELECT ANY TABLE. Can VC access TEST4 created after the new schema level grant:
SQL> connect vc/vc@//192.168.56.18:1521/freepdb1
Connected.
SQL> select * from va.test4;
no rows selected
SQL>
Yes, we can access the new table. So it is better in one sense than direct grants BUT is it better than direct grants? There is no future control as every table created under VA in this case is available to VC.
In the old way we would create grants as a script maybe or manually for all VA's tables and grant to VC. In that case it is controlled as we make individual grants but we need to remember to make the grants specifically as the scheme is extended.
If sure we will always allow access to a scheme then this new right is OK but what about least rights?
This new privilege is useful and more fine grained than SELECT ANY TABLE generally BUT if we want to have a database that is designed to least rights then it is highly likely that these new grants are not least rights; in other words a grantee will inherit access to excess tables not needed or PL/SQL or...
Another example
C:\scripts>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 14:16:12 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
Make Direct grants the old way but from a DBA:
SQL> grant select on va.test to vd;
Grant succeeded.
SQL> grant select on va.test4 to vd;
Grant succeeded.
SQL>
We can script this if necessary to achieve the same results as the new schema level grants:
SQL> select 'grant select on '||owner||'.'||table_name||' to vd;'||chr(10)
2 from dba_tables where owner='VA' and table_name like 'TEST%';
'GRANTSELECTON'||OWNER||'.'||TABLE_NAME||'TOVD;'||CHR(10)
--------------------------------------------------------------------------------
grant select on VA.TEST to vd;
grant select on VA.TEST3 to vd;
grant select on VA.TEST4 to vd;
SQL>
We can then run the generated script.
Lets go back to the issue of finding where the new grants are stored
SQL> select view_name from dba_views where owner='SYS' and view_name like 'DBA_%SCHEMA%PRIV%';
VIEW_NAME
--------------------------------------------------------------------------------
DBA_SCHEMA_PRIVS
DBA_UNUSED_SCHEMA_PRIVS
DBA_UNUSED_SCHEMA_PRIVS_PATH
DBA_USED_SCHEMA_PRIVS
DBA_USED_SCHEMA_PRIVS_PATH
SQL>
Lets look at DBA_SCHEMA_PRIVS:
SQL> col grantee for a30
SQL> col privilege for a30
SQL> col schema for a30
SQL> col admin_option for a3
SQL> col common for a3
SQL> col inherited for a3
SQL> set lines 220
SQL> select * from dba_schema_privs where grantee='VC';
GRANTEE PRIVILEGE SCHEMA ADM COM INH
------------------------------ ------------------------------ ------------------------------ --- --- ---
VC EXECUTE ANY PROCEDURE VA NO NO NO
VC SELECT ANY TABLE VA NO NO NO
SQL>
These are the grants that I made at the start of this example.
We don’t need SELECT ANY TABLE on the whole database. Do we need SELECT ANY TABLE on a whole schema? Probably not. Yes it’s a saving to have this BUT it probably reduces the chance of a least privilege. Maybe an enhancement to this new privilege is an exception clause, i.e. GRANT SELECT ANY TABLE ON SCHEMA VA TO VC EXCLUDE ('TEST','TEST4'); or we should do it the old way and grant exactly what is needed only.
#23c #dbsec #oracleace #grants #oracle #database #security
