Select Any Dictionary vs Select_Catalog_Role

Allways we want give to a user access to data dictionary and system views, we find two  privileges to grant select any dictionary and grant select_catalog_role with both we acomplish with the job, but, why there two options? are the same options? no, they arent the same and one is not over. Is a role vs direct privilege and yes is diferent.

1)
First big diference between SELECT_CATALOG_ROLE role and direct privilege SELECT ANY DICTIONARY,  is that with the privilege user can’t access to all SYS owner tables like USER$, TAB$, etc…
For example: In lasted version of Database user password is in USER$ and user can see password with privilege
but no with role.

2)

Another big diference is with compilations, in a PL/SQL a role does not allow the grantee to build stored objects on the granted objects.

For compile a code with a named object, the user must have been granted privileges by direct grants; not through the roles. With the role we can to only one user switch on or switch off the privilege, thats the reason of his existence.
Roles provide privileges; but only when they are enabled.

SQL> alter user USER default role none;

SQL> connect USER/USER
SQL> select * from session_roles;

no rows selected

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. To enable the role, the user has to execute the SET ROLE command:

SQL> set role SELECT_CATALOG_ROLE;

For security reason we can add a password to the role:

SQL> alter role SELECT_CATALOG_ROLE identified by oracle09 ;

user in a PL/SQL will need use this :

set role SELECT_CATALOG_ROLE identified by oracle09;

The role can also be non-default which means the grantee must execute a set
role or equivalent command to enable it and we saw the role can also be password protected.

The SET ROLE command is an SQL*Plus command. To call it from SQL with out password.

begin
    dbms_session.set_role (‘SELECT_CATALOG_ROLE’);
end;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s