Arups post was more in depth and illustrated that both allow access to dictionary objects but in a different way. SELECT ANY DICTIONARY is a system privilege and SELECT_CATALOG_ROLE is a database role that contains various grants on SYS objects - more on those in a minute.
Arup made a point that the main difference is that SELECT_CATALOG_ROLE is a role and therefore its not useful to grant this when code accesses one of the tables or views granted to that role. This is because the PL/SQL compiler cannot access the VIEW or table - Arup used V$SESSION and v$DATABASE - actually V_$SESSION and V_$DATABASE as these are the actual views not the synonyms. So to compile PL/SQL code that uses a SYS view that could be exposed by SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY to the PL/SQL code owner then the role doesn't work. Actually from 12c its possible to grant roles to code but more on that in a later blog post and also se the conclusion later in this post
SELECT_CATALOG_ROLE is much older than SELECT ANY DICTIONARY as the latter was added to resolve access to the data dictionary tables and views when Oracle closed them by changing O7_DICTIONARY_ACCESSIBILITY many years ago. This parameter stopped system %ANY% privileges such as SELECT ANY TABLE affecting SYS objects and the data dictionary.
Mike Dietrich also posted about SELECT ANY DICTIONARY in 2015 in a post titled New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set to discuss that in 12c Oracle restricted SELECT ANY DICTIONARY to not include access to tables such USER$, XS$VERIFIERS, ENC$ etc. Mike confirmed that LINK$ was excluded from SELECT ANY DICTIONARY back in release 10.1 and new exclusions have been added through the years. Also Mike confirmed that you cannot grant SELECT ANY DICTIONARY to a role:
SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create roles pete;
Role created.
SQL> grant select any dictionary to pete;
Grant succeeded.
SQL> grant pete to test;
Grant succeeded.
SQL> connect test/test@//192.168.56.78:1523/xepdb1
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------------------------------------------------------
PETE
1 row selected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE DIMENSION
SELECT ANY DICTIONARY
CREATE JOB
15 rows selected.
SQL>
SQL> select count(*) from v$session;
COUNT(*)
----------
51
1 row selected.
SQL>
SQL> select count(*) from v$database;
COUNT(*)
----------
1
1 row selected.
SQL>
Just to prove that a user without SELECT ANY DICTIONARY cannot see V$SESSION and V$DATABASE:
SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create user test1 identified by test1;
User created.
SQL> grant create session to test1;
Grant succeeded.
SQL> connect test1/test1@//192.168.56.78:1523/xepdb1
Connected.
SQL> select count(*) from v$database;
select count(*) from v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from v$session;
select count(*) from v$session
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
OK, so Mikes statement that SELECT ANY DICTIONARY cannot be granted via a role des not seem correct at least in 18cXE. Mike also stated that SELECT ANY DICTIONARY cannot be granted via GRANT ALL PRIVILEGES. I assume that he meant GRANT ANY PRIVILEGE; lets test that for completeness:
SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create user test3 identified by test3;
User created.
SQL> grant create session,grant any privilege to test3;
Grant succeeded.
SQL> connect test3/test3@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_roles;
no rows selected
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
GRANT ANY PRIVILEGE
2 rows selected.
SQL>
SQL> grant select any dictionary to test;
Grant succeeded.
SQL>
This would also seem to be incorrect at least in 18c XE. Lets go back to 11.2.0.4 and test if its the same:
SQL> connect sys/oracle1@//192.168.1.85:1521/bfora.localdomain as sysdba
Connected.
SQL> create role pete;
create role pete
*
ERROR at line 1:
ORA-01921: role name 'PETE' conflicts with another user or role name
SQL> create role pete_role;
Role created.
SQL> grant select any dictionary to pete_role;
Grant succeeded.
SQL> create user test1 identified by test1;
User created.
SQL> grant create session, pete_role to test1;
Grant succeeded.
SQL> connect test1/test1@//192.168.1.85:1521/bfora.localdomain
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
FACADM_SUPPORT
FACADM_ALL
PETE_ROLE
3 rows selected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY DICTIONARY
2 rows selected.
SQL> select count(*) from v$session;
COUNT(*)
----------
22
1 row selected.
SQL> select count(*) from v$database;
COUNT(*)
----------
1
1 row selected.
SQL>
So we can grant SELECT ANY DICTIONARY via a role in 11.2.0.4. What about GRANT ANY PRIVILEGE:
SQL> connect sys/oracle1@//192.168.1.85:1521/bfora.localdomain as sysdba
Connected.
SQL> create user test4 identified by test4;
User created.
SQL> grant create session,grant any privilege to test4;
Grant succeeded.
SQL> connect test4/test4@//192.168.1.85:1521/bfora.localdomain
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
FACADM_SUPPORT
FACADM_ALL
2 rows selected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
GRANT ANY PRIVILEGE
2 rows selected.
SQL> grant select any dictionary to test1;
Grant succeeded.
SQL>
So, yes, this seems to be historic behaviour. I am uncertain what Mike meant by the statement in his blog that this privilege cannot be granted via a role and cannot be granted with GRANT ANY PRIVILEGE. His statement is
"Please be very careful with granting this privilege. Furthermore, you need to be aware that it can’t be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES". This doesn't seem to be true.
I don't want to repeat what Arup did in his post, i want to focus on what actually is included in SELECT ANY DICTIONARY. Mike states in his post that you cannot query anywhere in the database to find out exactly what is granted (or limited) by this privilege. First lets see how many grants SELECT_CATALOG_ROLE actually has:
SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL>
SQL> select count(*),privilege
2 from dba_tab_privs
3 where grantee='SELECT_CATALOG_ROLE'
4 group by privilege;
COUNT(*) PRIVILEGE
---------- ----------------------------------------
2 EXECUTE
4521 SELECT
2 READ
14 FLASHBACK
SQL>
So, SELECT_CATALOG_ROLE is not pure SELECT as it also has some FLASHBACK and also 2 READ but importantly 2 EXECUTE grants. The total grants are 4539 in Oracle 18c XE.
The next step is how many SYS objects of TABLE or VIEW are there. I know you also SELECT from a SEQUENCE but lets focus on TABLE and VIEW:
SQL> create user sad identified by sad;
User created.
SQL>
SQL> grant select any dictionary to sad;
Grant succeeded.
SQL>
SQL> grant create session to sad;
Grant succeeded.
SQL>
SQL> connect sad/sad@//192.168.56.78:1523/xepdb1
Connected.
SQL>
SQL> select count(*),object_type from dba_objects where owner='SYS' and object_type in ('VIEW','TABLE') group by object_type;
COUNT(*) OBJECT_TYPE
---------- -----------------------
1510 TABLE
6629 VIEW
SQL>
So there are 8139 TABLE and VIEW owned by SYS. How many are already granted to PUBLIC. If they are we cannot test if they are affected by SELECT ANY DICTIONARY as many users with just CREATE SESSION can access them; so lest ignore these as well:
SQL> get c
1 select count(*) from (
2 select distinct owner owner,
3 table_name object_name,
4 type object_type
5 from dba_tab_privs
6 where owner ='SYS'
7 and grantee='PUBLIC'
8* and type in ('TABLE','VIEW'))
SQL> @c
COUNT(*)
----------
1754
1 row selected.
SQL>
So we have 8139 - 1754 = 6385 potential TABLE and VIEW that could potentially be accessed via SELECT ANY DICTIONARY. We know from Oracle on the page "SELECT ANY DICTIONARY - privilege no longer accesses some SYS data dictionary tables". This page states that SELECT ANY DICTIONARY does not give access to DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only SYSDBA can access these tables now. OK, that's the documentation but how many of the 6385 potential TABLE and VIEW can we access with this privilege. We can find this out from the database easily, example also 18c:
SQL> @sad
...{output removed for space}
------------------------------------
Number of tables = [1465]
Number of views = [4920]
Number of exclusions = [157]
PL/SQL procedure successfully completed.
SQL>
So there are 1465 TABLE and 4920 VIEW that can be potentially accessed that are not granted to PUBLIC. This is 6385 potential objects as above. But we can now see a list of the 157 objects that cannot be accessed by anyone granted SELECT ANY DICTIONARY. So SELECT ANY DICTIONARY grants access in 18c XE to 6228 TABLE and VIEW objects. Most of the main tables are not accessible are in the documentation but the actual list also includes IOT and also a lot of underscore views. There are limited additional more normal real tables/views that are not listed such as OPATCH_XML_INV.
What is interesting is if we check whats granted to SELECT_CATALOG_ROLE we can use my find_all_privs.sql script to do that. This shows above that there are 4539 grants to SELECT_CATALOG_ROLE so this is a lot less than SELECT ANY DICTIONARY at 6228. Interestingly if we check the find_all_privs output we can see that a number of the views not allowed in SELECT ANY DICTIONARY are allowed in SELECT_CATALOG_ROLE. Some are here:
...{output removed for space saving}
TABLE PRIV => SELECT object => XDB.XDB_INDEX_DDL_CACHE grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_APPLY_CONSTRAINT_COLUMNS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_APPLY_OBJECT_CONSTRAINTS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_GGXSTREAM_INBOUND grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_GGXSTREAM_OUTBOUND grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_ACTIONS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_EVENT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_LINK grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_PROP grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_STAT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_FINDINGS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_RECOMMENDATIONS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_TP_COMPONENT_PROP grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_COORDINATOR grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_READER grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_SERVER grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_CAPTURE grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_MESSAGE_TRACKING grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_TRANSACTION grantable => NO
TABLE PRIV => SELECT object => SYS._INMEMORY_AIMTASKDETAILS grantable => NO
TABLE PRIV => SELECT object => SYS._INMEMORY_AIMTASKS grantable => NO
TABLE PRIV => SELECT object => SYS._SYS_AIM_SEG_HISTOGRAM grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_COORDINATOR grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_READER grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_SERVER grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_CAPTURE grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_MESSAGE_TRACKING grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_TRANSACTION grantable => NO
As you can see there are a lot of the SYS._% views are listed here; not all of them that exist but quite a few. So we have cross over between things blocked by SELECT ANY DICTIONARY (some SYS._% views ) and SELECT_CATALOG_ROLE.
Conclusion?
Well, SELECT_CATALOG_ROLE allows access to some things Oracle deemed not allowed by SELECT ANY DICTIONARY so we need to be careful of granting this role on these grounds. BUT, the overwhelming issue for me is that SELECT_CATALOG_ROLE gives access to 4539 objects and SELECT ANY DICTIONARY gives access to 6228 objects (both numbers in 18c XE)
I am not sure why Oracle do not publish the full list of exclusions in SELECT ANY DICTIONARY but they do publish all of the main tables. We can easily find out anyway. For me, i want to know what does SELECT ANY DICTIONARY really mean. I want to know what i am actually granting if I give out that privilege; well it means access to 6228 tables and views in 18cXE
Both of these rights should not be used; they are a sledgehammer to crack a peanut. If someone needs access to V$SESSION or V$DATABASE and there is a legitimate reason to have that access then grant access on the individual views not SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE.
