grant select any dictionary to
grant select_catalog_role to
The short answer to the last question is -- no; these two do not produce the same result. Since they are fundamentally different, there is a place of each of these. One is not a replacement for the other. In this blog I will explain the subtle but important differences between the two seemingly similar privileges and how to use them properly.
Create the Test Case
First let me demonstrate the effects by a small example. Create two users called SCR and SAD:
SQL> create user scr identified by scr;
SQL> create user sad identified by sad;
Grant the necessary privileges to these users, taking care to grant a different one to each user.
SQL> grant create session, select any dictionary to sad;
SQL> connect sad/sad
The procedure is very simple; it merely counts the number of connected sessions by querying V$SESSION. When you connect as SAD and create the procedure by executing p.sql:
The procedure was created properly; but when you connect as SCR and execute the script:
Warning: Procedure created with compilation errors.
Errors for PROCEDURE P:
4/2 PL/SQL: SQL Statement ignored
6/7 PL/SQL: ORA-00942: table or view does not exist
Not All Privileges have been Created Equal
The answer lies in the way Oracle performs compilations. To compile a code with a named object, the user must have been granted privileges by direct grants; not through the roles. Selecting or performing DML statements do not care how the privileges were received. The SQL will work as long as the privileges are there. The privilege SELECT ANY DICTIONARY is a system privilege, similar to create session or unlimited tablespace. This is why the user SAD, which had the system privilege, could successfully compile the procedure P.
The user SCR had the role SELECT_CATALOG_ROLE, which allowed it to SELECT from V$SESSION but not to create the procedure. Remember, to create another object on the base object, the user must have the direct grant on the base object; not through a role. Since SCR had the role not the direct grant on V$DATAFILE, it can't compile the procedure.
So while both the privileges allow the users to select from v$datafile, the role does not allow the users to create objects; the system privilege does.
Why the Role?
Now that you know how the privileges are different, you maybe wondering why the role is even there. It seems that the system grant can do everything and there is no need for a role. Not quite.The role has a very different purpose. Roles provide privileges; but only when they are enabled. To see what roles are enabled in a session, use this query:
SQL> connect scr/oracle
SQL> select * from session_roles
2 rows selected.
Now the roles have been enabled. Since the roles are not default, the user must explicitly enable them using the SET ROLE command. This is a very important characteristic of the roles. We can control how the user will get the privilege. Merely granting a role to a user will not enable the role; the user's action is required and that can be done programmatically. In security conscious environments, you may want to take advantage of that property. A user does not always have the to have to privilege; but when needed it will be able to do so.
The SET ROLE command is an SQL*Plus command. To call it from SQL, use this:
You can also set a password for the role. So it will be set only when the correct password is given;
SQL> alter role SELECT_CATALOG_ROLE identified by l
SQL> select GRANTED_ROLE, DEFAULT_ROLE
2 from dba_role_privs
3 where GRANTEE = 'SCR';
Thanks to Randolph Geist (http://www.blogger.com/profile/13463198440639982695) and Pavel Ruzicka (http://www.blogger.com/profile/04746480312675833301) for pointing out yet another important difference. SELECT ANY DICTIONARY allows select from all SYS owner tables such as TAB$, USER$, etc. This is not possible in the SELECT_CATALOG_ROLE. This difference may seem trivial; but is actually quite important in some cases. For instance, latest versions of Oracle do not show the password column from DBA_USERS; but the hashed password is visible in USER$ table. It's not possible to reverse engineer the password from the hash value; but it is possible to match it to a similar entry and guess the password. A user with the system privilege will be able to do that; but a user with the role will not be.
In this blog entry I started with a simple question - what is the difference between two seemingly similar privileges - SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. The former is a system privilege, which remains active throughout the sessions and allows the user to create stored objects on objects on which it has privileges as a result of the grant. The latter is not a system grant; it's a role which does not allow the grantee to build stored objects on the granted objects. The role can also be non-default which means the grantee must execute a set role or equivalent command to enable it. The role can also be password protected, if desired.
The core message you should get from this is that roles are different from privileges. Privileges allow you to build stored objects such as procedures on the objects on which the privilege is based. Roles do not.