This is clearly a replacement for the o7_dictionary_accessibility parameter that used to protect the SYS schema from system "ANY" privileges. SELECT ANY DICTIONARY was added to bypass this to some level to allow access to dictionary tables BUT some tables are excluded even from SELECT ANY DICTIONARY such as SYS.USER$. I wrote a blog about the difference between SELECT ANY DICTIONARY and the SELECT_CATALOG_ROLE some time ago that looks what is actually granted to SELECT ANY DICTIONARY and what is excluded from this system privilege. Oracle does not document either.
What does dictionary protection mean and how do we turn it on. Note this was written before Oracle released the limited security documentation.
So let us see if there are any system privileges related to this new protection or is it syntax based:
SQL> select name from system_privilege_map where name like '%PROT%';
no rows selected
SQL> select name from system_privilege_map where name like '%DIC%';
NAME
----------------------------------------
ANALYZE ANY DICTIONARY
SELECT ANY DICTIONARY
SQL>
Nothing, must be syntax based. Which users already have DICTIONARY PROTECTION enabled:
SQL> select username from dba_users where dictionary_protected='YES';
USERNAME
--------------------------------------------------------------------------------------------------------------------------------
XS$NULL
LBACSYS
CTXSYS
DVF
DVSYS
AUDSYS
GSMADMIN_INTERNAL
GGSHAREDCAP
XDB
SYSRAC
SYSBACKUP
USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SYSKM
SYSDG
13 rows selected.
SQL>
A very interesting observation is that SYS is not included in this list from the PDB. Lets check the O7_dictionary_accessibilty parameter?
C:\scripts>sqlplus sys/oracle@192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 2 13:48:13 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> sho parameter o7
SQL> sho parameter 07
SQL> sho parameter O7
This parameter has gone of course in 23c. But if we create a user with SELECT ANY TABLE can it access a table in the SYS schema:
SQL> create user vj identified by vj;
User created.
SQL> grant create session to vj;
Grant succeeded.
SQL> connect vj/vj@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> grant select any table to vj;
Grant succeeded.
SQL> connect vj/vj@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
This is only a test to access a single SYS table but it should be representative. So, it seems that even with out O7_dictionary_accessibility and SYS not having DICTIONARY PROTETION that SYS tables cannot be accessed with ANY system privileges. Just for completeness just check the new value for SYS in the root container.
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select username from dba_users where dictionary_protected='YES';
USERNAME
--------------------------------------------------------------------------------
SYSRAC
XS$NULL
LBACSYS
CTXSYS
DVF
DVSYS
AUDSYS
GSMADMIN_INTERNAL
GGSHAREDCAP
XDB
SYSBACKUP
USERNAME
--------------------------------------------------------------------------------
SYSKM
SYSDG
13 rows selected.
SQL>
We have to assume SYS is handled differently now.
So how do we use this new setting. Lets search $ORACLE_HOME for it:
[oracle@localhost admin]$ grep -i "dictionary_protect" *
cdenv_mig.sql:Rem nishchau 12/22/21 - Proj 89500: add DBA_USERS.DICTIONARY_PROTECTED
cdenv_mig.sql: PROTECTED, READ_ONLY, DICTIONARY_PROTECTED)
cdenv_mig.sql: -- DICTIONARY_PROTECTED
cdenv_mig.sql: MANDATORY_PROFILE_VIOLATION, PROTECTED, READ_ONLY, DICTIONARY_PROTECTED)
cdenv_mig.sql: -- DICTIONARY_PROTECTED
cdenv.sql:Rem nishchau 12/22/21 - Proj 89500: add DBA_USERS.DICTIONARY_PROTECTED
cdenv.sql:comment on column USER_USERS.DICTIONARY_PROTECTED is
cdenv.sql:comment on column DBA_USERS.DICTIONARY_PROTECTED is
cdenv.sql: DICTIONARY_PROTECTED)
cdenv.sql: -- DICTIONARY_PROTECTED
cdenv.sql:comment on column ALL_USERS.DICTIONARY_PROTECTED is
dsec.bsq:REM nishchau 12/22/21 - Proj 89500: Mark AUDSYS DICTIONARY_PROTECTED
e21.sql:Rem nishchau 12/22/21 - Proj 89500: clear DICTIONARY_PROTECTED bit in
e21.sql:Rem DICTIONARY_PROTECTED
[oracle@localhost admin]$
[oracle@localhost admin]$ cat dsec.bsq | grep "dictionary prote"
REM dictionary protected
REM skulhari 10/21/22 - Bug 34453879: Mark SYSKM user dictionary protected
REM vivnatar 09/27/22 - Bug 34453883: SYSDG dictionary protection
create user AUDSYS enable dictionary protection no authentication account lock
enable dictionary protection
create user sysdg no authentication account lock enable dictionary protection
create user syskm no authentication account lock enable dictionary protection
create user sysrac no authentication enable dictionary protection
[oracle@localhost admin]$
This is the syntax used to enable this new feature. Lets test this on other users and see what it does.
First lets check to see which schemas have objects and in which tablespace:
SQL> select distinct tablespace_name from dba_tables;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
SQL>
Note that there seems to be a blank tablespace name. Lets continue the investigate:
SQL> select count(*),owner from dba_tables where tablespace_name='SYSTEM' group by owner;
COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
969 SYS
26 SYSTEM
3 OUTLN
1 GSMADMIN_INTERNAL
6 OJVMSYS
22 LBACSYS
6 rows selected.
SQL> select count(*),owner from dba_tables where tablespace_name='SYSAUX' group by owner;
COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
580 SYS
50 GSMADMIN_INTERNAL
33 WMSYS
42 CTXSYS
2 SYSTEM
3 DBSFWUSER
1 GGSHAREDCAP
12 DBSNMP
5 APPQOSSYS
34 XDB
2 OLAPSYS
COUNT(*) OWNER
---------- --------------------------------------------------------------------------------------------------------------------------------
129 MDSYS
335 APEX_220200
1 FLOWS_FILES
43 DVSYS
15 rows selected.
SQL>
We have default schemas that have objects in the SYSTEM tablespace and the SYSAUX tablespace. Notice that from earlier and here, for instance, GSMADMIN_INTERNAL is dictionary protected and OUTLN is not. First lets grant SELECT ANY TABLE TO our sample user VB:
SQL> grant select any table to vb;
Grant succeeded.
SQL>
Now find tables owned by GSMADIN_INTERNAL and OUTLN so we can test the ANY grant against them:
SQL> select table_name from dba_tables where owner='OUTLN' and tablespace_name='SYSTEM';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
OL$
OL$HINTS
OL$NODES
SQL>
SQL> select table_name from dba_tables where owner='GSMADMIN_INTERNAL' and tablespace_name='SYSTEM';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
SHARD_TS
SQL>
Connect to our sample VB user and test:
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from outln.ol$;
COUNT(*)
----------
0
SQL> select count(*) from gsmadmin_internal.shard_ts;
select count(*) from gsmadmin_internal.shard_ts
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
This seems to prove the technology, GSMADMIN_INTERNAL is protected and OUTLN is not. Check tables owned my XDB also which is DICTIONARY PROTECTED>
SQL> select table_name from dba_tables where tablespace_name='SYSAUX' and owner='XDB';
TABLE_NAME
--------------------------------------------------------------------------------
X$NM7NOP924P5RJP3E0KOT51368GPG
X$QN7NOP924P5RJP3E0KOT51368GPG
X$PT7NOP924P5RJP3E0KOT51368GPG
XDB$TTSET
XDB$ROOT_INFO
XDB$H_INDEX
XDB$XDB_READY
XDB$DBFS_VIRTUAL_FOLDER
XDB$NONCEKEY
XDB$CDBPORTS
XDB$IMPORT_TT_INFO
TABLE_NAME
--------------------------------------------------------------------------------
XDB$TSETMAP
XDB$D_LINK
XDB$COLUMN_INFO
XDB$PATH_INDEX_PARAMS
XDB$IMPORT_QN_INFO
XDB$IMPORT_NM_INFO
XDB$IMPORT_PT_INFO
APP_USERS_AND_ROLES
APP_ROLE_MEMBERSHIP
XDB$REPOS
XDB$MOUNTS
TABLE_NAME
--------------------------------------------------------------------------------
JSON$COLLECTION_METADATA
JSON$USERS
JSON$USER_CREDENTIALS
JSON$USER_ROLES
XDB_INDEX_DDL_CACHE
XDB$CHECKOUTS
XDB$DXPTAB
XDB$XIDX_PART_TAB
XDB$XIDX_PARAM_T
XDB$XTAB
XDB$XTABNMSP
TABLE_NAME
--------------------------------------------------------------------------------
XDB$XTABCOLS
34 rows selected.
SQL>
Check if we can access one of these tables as VB:
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from xdb.xdb$xtab;
select count(*) from xdb.xdb$xtab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
This also works. Lets try and create a table as our sample VA user and then try and protect it>
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 15:28:13 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> create table va.test5(col01 number) tablespace system;
Table created.
SQL>
As the user VB can we access this table?
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from va.test5;
COUNT(*)
----------
0
SQL>
Yes of course. What if we try and make VA DICTIONARY PROTECTED as we created our test table in the SYSTEM tablespace; I know we should not do this, its just a test:
SQL> alter user va enable dictionary protection;
alter user va enable dictionary protection
*
ERROR at line 1:
ORA-40374: Dictionary protection feature is supported for only Oracle
maintained users.
SQL>
Hmmm, we cannot make our own users DICTIONARY PROTECTED even if we created tables in the SYSTEM tablespace. We could probably work around this and set _oracle_script and recreate the user and the table in the dictionary and test enable dictionary protection. This would likely work but this can break upgrades so we wont do this as the user would be marked as Oracle maintained but its isn’t.
Can we enabled DICTIONARY PROTECTION on other oracle users?
A user not currently protected is OJVMSYS:
SQL> alter user ojvmsys enable dictionary protection;
User altered.
SQL>
And now we can test access to a table owned by this default user:
SQL> select table_name from dba_tables where tablespace_name='SYSTEM' and owner='OJVMSYS';
TABLE_NAME
--------------------------------------------------------------------------------
OJDS$BINDINGS$
OJDS$INODE$
OJDS$ATTRIBUTES$
OJDS$REFADDR$
OJDS$PERMISSIONS$
OJDS$SHARED$OBJ$
6 rows selected.
SQL>
Connect as the user VB:
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
select count(*) from ojvmsys.ojds$inode$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
This seems to work as planned. Remove the DICTIONARY PROTECTION and test again:
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 15:43:08 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> alter user ojvmsys disable dictionary protection;
alter user ojvmsys disable dictionary protection
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter user ojvmsys disable dictionary protection;
User altered.
SQL> alter session set container=freepdb1;
Session altered.
SQL> select dictionary_protected from dba_users where username='OJVMSYS';
DIC
---
NO
SQL>
Strange, we added DICTIONARY PROTECTION in the PDB but cannot remove until we are connected to the CDB?? â€" BUG? (or not, see below as to why!), logically if we cannot remove it in the PDB we should not be able to add it in the PDB. It would make sense to allow protection at the PDB level but if that were the case then we should be able to remove it in the PDB. Seems like a bug at some level to me.
Test now that VB can access the table now that the protection is removed:
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
COUNT(*)
----------
3
SQL>
Now protection is removed the user VB with SELECT ANY TABLE works
Check if we ALTER USER in the PDB is the user dictionary protected in the CDB:
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 15:46:33 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> select sys_context('userenv','con_name')
2 /
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
FREEPDB1
SQL> alter user ojvmsys enable dictionary protection;
alter user ojvmsys enable dictionary protection
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers
SQL>
OK, we know now that we cannot set DICTIONARY PROTECTION in the PDB so why did i think that this could be a bug?
Well, I had the _oracle_script set as I was testing whether I could create a DICTIONARY PROTECTED user of my own above. Thats why i was able to add it in the PDB above, so not a BUG!!
Check everything is now fine:
SQL> select dictionary_protected from dba_users where username='OJVMSYS';
DIC
---
NO
SQL> alter session set container=cdb$root;
Session altered.
SQL> select dictionary_protected from dba_users where username='OJVMSYS';
DIC
---
NO
SQL>
Re-run the test from the CDB this time:
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter user ojvmsys enable dictionary protection;
User altered.
SQL> alter session set container=freepdb1;
Session altered.
SQL> select dictionary_protected from dba_users where username='OJVMSYS';
DIC
---
YES
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
select count(*) from ojvmsys.ojds$inode$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter user ojvmsys disable dictionary protection;
User altered.
SQL> connect vb/vb@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from ojvmsys.ojds$inode$;
COUNT(*)
----------
3
SQL>
This is correct behaviour. So, this is now more fine grained than it was prior to 23c. We can now limit SYSTEM ANY privileges on each default user by enabling dictionary protection.
This is great but its unclear about SYS, clearly SYS is still protected from system ANY grants but the flag is not set.
It would also be great to protect our own users from system ANY privileges and we could do that by telling Oracle our schema is ORACLE_MAINTAINED but this would not be supported and could potentially cause issues with upgrades. Clearly protecting against system any for a realm is a feature of Database Vault out of the box.
#23c #oracleace #dbsec #oracle #security #protection
