Sunday, July 24, 2011

Difference between Select Any Dictionary and Select_Catalog_Role

When you want to give a user the privilege to select from data dictionary and dynamic performance views such as V$DATAFILE, you have two options:

grant select any dictionary to ;
grant select_catalog_role to ;

Did you ever wonder why there are two options for accomplishing the same objective? Is one of them redundant? Won't it make sense for Oracle to have just one privilege? And, most important, do these two privileges produce the same result?

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;


Grant succeeded.

SQL> grant create session, select_catalog_role to scr;

Grant succeeded.

Let's test to make sure these privileges work as expected:

SQL> connect sad/sad
Connected.

SQL> select * from v$session;
 ... a bunch of rows come here ...

SQL> connect scr/scr
Connected.

SQL> select * from v$datafile;
 ... a bunch of rows come here ...

Both users have the privilege to select from the dictionary views as we expected. So, what is the difference between these two privileges? To understand that, let's create a procedure on the dictionary tables/views on each schema. Since we will create the same procedure twice, let's first create a script which we will call p.sql. Here is the script:

create or replace procedure p as
    l_num number;
begin
    select count(1)
    into l_num
    from v$session;
end;
/

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:

SQL> @p.sql


Procedure created.

The procedure was created properly; but when you connect as SCR and execute the script:


SQL> @p.sql


Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P:


LINE/COL ERROR
-------- ------------------------------------------------
4/2      PL/SQL: SQL Statement ignored
6/7      PL/SQL: ORA-00942: table or view does not exist

That must be perplexing. We just saw that the user has the privilege to select from the V$SESSION view. You can double check that by selecting from the view one more time. So, why did it report ORA-942: table 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
Connected.
SQL> select * from session_roles
  2  /


ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE


2 rows selected.

We see that two roles - SELECT_CATALOG_ROLE and HS_ADMIN_SELECT_ROLE - have been enabled in the session. The first one was granted to the user. The other one is granted to the first one; so that was also enabled.

Just because a role was granted to the user does not necessarily mean that the role would be enabled. The roles which are marked DEFAULT by the user will be enabled; the others will not be. Let's see that with an example. As SYS user, execute the following:

SQL> alter user scr default role none;

User altered.

Now connect as SCR user and see which roles have been enabled:

SQL> connect scr/oracle
SQL> select * from session_roles;

no rows selected

None of the roles have been enabled. Why? That's  because none of the roles are default for the user (effected by the alter user statement by SYS). At this point when you select from a dynamic performance view:

SQL> select * from v$datafile;
select * from v$datafile
              *
ERROR at line 1:
ORA-00942: table or view does not exist

You will get this error because the role is not enabled, or active. Without the role the user does not have any privilege to select from the data dictionary or dynamic performance view. To enable the role, the user has to execute the SET ROLE command:

SQL> set role SELECT_CATALOG_ROLE;

Role set.

Checking the enabled roles:

SQL> select * from session_roles;

ROLE
------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

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:

begin

   dbms_session.set_role ('SELECT_CATALOG_ROLE');
end;


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       
  2  /


Role altered.

To set the role, you have to give the correct password:

SQL> set role SELECT_CATALOG_ROLE identified by l;

Role set.

If you give the wrong password:

SQL> set role SELECT_CATALOG_ROLE identified by fl
  2  /
set role SELECT_CATALOG_ROLE identified by fl
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'SELECT_CATALOG_ROLE'

You can also revoke the execute privilege on dbms_session from public. After that the user will not be able to use it to set the role. You can construct another wrapper procedure to call it. Inside the wrapper, you can have all sort of checks and balances to make sure the call is acceptable.

We will close this discussion with a tip. How do you know which roles are default? Simply use the following query:


SQL> select GRANTED_ROLE, DEFAULT_ROLE
  2  from dba_role_privs
  3  where GRANTEE = 'SCR';


GRANTED_ROLE                   DEF
------------------------------ ---
SELECT_CATALOG_ROLE            NO


Update


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.



Conclusion

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.



18 comments:

Pavel Ruzicka said...

Arup
It is all nice.. but I guess you did not mention one important difference between these two roles or maybe an important security related implication of the inherent differences.

The important difference between SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY system privilege is about who can and who cannot see user password hashes. This difference is important only in context of Oracle 11g. More details can be found for example here:
http://jhdba.wordpress.com/2010/01/04/the-need-to-ensure-that-hashed-password-values-are-safe/

In Oracle 10g (and before), the password hash is visible via dba_users view and there is no difference between the two. In 11g+, this has been removed and it is visible only in SYS owned SYS.USER$ table.

My personal conclusion:
1. If access to data dictionary is required for non-privileged db accounts, SELECT_CATALOG_ROLE should be used instead of SELECT ANY DICTIONARY system privilege for 11g+ to prevent exposure of password hashes.
2. Special caution should be taken in case of infrastructural “non-privileged” accounts (license management tools, ad-hoc monitoring tools etc.) as these might pose particular risk given “company wide” deployment scope.

Pavel

Randolf said...

Hi Arup,

as already indicated by the previous comment, there is a significant difference between the SELECT ANY DICTIONARY system privilege and the SELECT_CATALOG_ROLE apart from what you've described:

The SELECT ANY DICTIONARY system privilege allows to query all dictionary tables owned by SYS, e.g. SYS.TAB$, SYS.USER$ etc.

This is not possible when using the role - so this is another thing to consider.

Randolf

Arup Nanda said...

Pavel and Randolph - yes, that's another important difference as well. Thank you for your comment and reference to Pavel's blog entry.

Anonymous said...

Hi Arup,

You can extend the demo further with
"O7_DICTIONARY_ACCESSIBILITY" and "SELECT_CATALOG_ROLE". It would slight information added for reference who wants to understand effect of parameter since from Oracle version 7.

- Pavan Kumar N

kamilini said...

HI
I am a newbee to oracle apps ,its realy great to get your blog about the dictionary and catlogue role...
Regards

oracle fussion middleware

Srikar Dasari said...

Pretty good, enjoyed reading it

Srikar Dasari said...

Pretty nice. Enjoyed reading it.

Srikar Dasari said...

I wrote a little column in my blog which shows the one more difference.

http://srikaroracle.blogspot.com/2010/08/select-privileges-on-sys-schema-views.html

Regards,
Srikar

Anonymous said...

Very well explained. Thank you.

Anonymous said...

Hi Arup,

One more clarification. You stated that
"The SET ROLE command is an SQL*Plus command. To call it from SQL, use this..."

The SET ROLE is an SQL Command, not an SQL*Plus command

Cheers,

Pieraldo Antonello

Arup Nanda said...

@Pieraldo I stand corrected. Yes, it's a SQL command. All SET commands are SQL*Plus, except a few like this. Thanks for pointing it out.

Unknown said...

Good arcticle. I am only little confused from sentence:

"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."

Which object is possible to create through privilege "select any dictionary"?

Arup Nanda said...

@Petr - thanks for your comments.

The sentence you are referring to explains the impact on the object creation. So, assung that a user has create view, procedure or some other object creation privilege, without the explicit grant on objects referenced to the user, it will not be possible to create objects. Select Any Dictionary allows that without explicit grants on the system objects to the user. The role does not allow that. The privilege Select Any Dictionary does not let the user create objects.

kirankumar said...
This comment has been removed by the author.
kirankumar said...

Good information and it is very useful
Sanjary Kids is one of the best play school and preschool in Hyderabad,India. The motto of the Sanjary kids is to provide good atmosphere to the kids.Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and provides Teacher Training Program.We have the both indoor and outdoor activities for your children.We build a strong value foundation for your child on Psychology and Personality development.
Preschool in hyderabad

Unknown said...

I can't believe I can earn money weekly from trading , this is amazing , and all this is from the effort of a company called skylink technology whom I met online and help me out in trading and gave me good tips about trading physiology... indeed skylink technology is a bitcoin/binary forex experts and company and I won't stop thanking them and sharing my testimony until am fully satisfied...... Interested traders should  free free to contact mail: skylinktechnes@yahoo.com  or  whatsapp/telegram: +1(213)785-1553 

HP Printer Error Code E3 said...

Thanks for this vital information through your website. I would like to thank you for the efforts you had made for writing this awesome article.

Anonymous said...

Good information, I remember that in SQL classes at the university there were several ways to do a SELECT.




___________________________________
I work in geospatial analytics.

Translate