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