I've seen some DBAs confused about these two "privileges" or "roles".
SELECT ANY DICTIONARY is a System Privilege.
SELECT_CATALOG_ROLE is a Role you would see in DBA_ROLES. However, querying DBA_SYS_PRIVS does NOT show what privileges are granted to this role.
SELECT_CATALOG_ROLE predates the SELECT ANY DICTIONARY privilege.
The SELECT ANY DICTIONARY privilege grants Read access on Data Dictionary tables owned by SYS. The SELECT_CATALOG_ROLE role grants Read access to Data Dictionary (DBA_%) and Performance (V$%) views.
Here is a short demo :
If you needed to grant a new / junior DBA or a Consultant the privilege to query the Data Dictionary and Performance views, which would you grant ?
.
.
.
SELECT ANY DICTIONARY is a System Privilege.
SELECT_CATALOG_ROLE is a Role you would see in DBA_ROLES. However, querying DBA_SYS_PRIVS does NOT show what privileges are granted to this role.
SELECT_CATALOG_ROLE predates the SELECT ANY DICTIONARY privilege.
The SELECT ANY DICTIONARY privilege grants Read access on Data Dictionary tables owned by SYS. The SELECT_CATALOG_ROLE role grants Read access to Data Dictionary (DBA_%) and Performance (V$%) views.
Here is a short demo :
SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 6 07:48:15 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user sad identified by sad; User created. SQL> grant create session, select any dictionary to sad; Grant succeeded. SQL> create user scr identified by scr; User created. SQL> grant create session, select_catalog_role to scr; Grant succeeded. SQL> SQL> connect sad/sad Connected. SQL> select count(*) from sys.user$; COUNT(*) ---------- 115 SQL> select count(*) from dba_users; COUNT(*) ---------- 53 SQL> connect scr/scr Connected. SQL> select count(*) from sys.user$; select count(*) from sys.user$ * ERROR at line 1: ORA-00942: table or view does not exist SQL> select count(*) from dba_users; COUNT(*) ---------- 53 SQL>
If you needed to grant a new / junior DBA or a Consultant the privilege to query the Data Dictionary and Performance views, which would you grant ?
.
.
.
5 comments:
We would grant select catalog role.
I have a question you said select any dictionary grants access to dictionary views, and not performance views.
But aren't v$ views are also views owned by sys and kind of dictionary objects ?
I didn't say "select any dictionary grants access to dictionary views".
I said :
"The SELECT ANY DICTIONARY privilege grants Read access on Data Dictionary tables owned by SYS."
The grant is on the base tables themselves !
It is SELECT_CATALOG_ROLE that we would prefer to use because the grant does NOT include the base tables.
Re-read my demo. The user with SELECT ANY DICTIONARY could query both USER$ (the base table !) and DBA_USERS. The user with SELECT_CATALOG_ROLE query query only DBA_USERS and not USER$ !
Hemant
I have set up a c## privileged user in a 12c multitenant database and have given it a bunch of roles including dba,select_catalog_role, and system privs select any dictionary, create session and set container. It needs to select * fron all_tablespaces but gets ORA-00942.
What am I missing?
Thanks! MRB
Oracle doesn't create an ALL_TABLESPACES view. Use DBA_TABLESPACES.
my typo! THanks - this works. MRB
Post a Comment