Using SESSION_ROLES it will retrieve the current roles that are active for a user in a session. Example: SQL> connect test/… SQL> select * from session_roles; ROLE —————————— DELETE_CATALOG_ROLE Using “SET ROLE” one can change the roles active in the current session. Example: SQL> select * from session_roles; ROLE —————————— TEST11 Enable specific set of roles in the current session SQL> set role test11, delete_catalog_role; … Continue reading How to list current active roles for a user in a session?
It seems at times when roles are assigned they are not assigned as DEFAULT role to a user. The reason for seems to be the following: In the table sys.user$ there is a column called “DEFROLE” which indicates if the role will be a default role or not. The possible values that seem to be are as follows: 0 – None (So when a role … Continue reading DEFAULT role explained
The view DBA_ROLES doesn’t have the created date column to display when the role was created, but the information is stored in sys.user$ the underlying table. So using the following query one can find the date the role was created. SQL> select name, to_char(ctime, ‘DD-MON-YYYY HH24:MI:SS’) from sys.user$ where name = ‘RESOURCE’; NAME TO_CHAR(CTIME,’DD-MON-YYYYHH24:MI:SS’) ——– —————————————– RESOURCE 22-JUL-2005 00:42:50 Continue reading How to find the date when a role was created?