Alex Lima

How to find the date when a role was created?

Published by

Alex Lima

January 27, 2009

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

5 responses to “How to find the date when a role was created?”

subbu

June 28, 2012

when i execute below query to find the dump date, it is throwing and error saying object(table or view) not exist. please suggest.

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

LikeLike

Reply
Gorakh

September 3, 2020

Thanks Nice info..It works..

LikeLike

Reply
javed

August 19, 2021

can you please help to find, when the role was deleted

LikeLike

Reply
patricia lopez

May 8, 2024

looking for same - when was role created or edited?

LikeLike

Reply
1. Alex Lima
  
  May 8, 2024
  
  can you try flashback?
  SELECT * FROM DBA_ROLES AS OF TIMESTAMP TO_TIMESTAMP(‘YYYY-MM-DD HH24:MI:SS’, ‘YYYY-MM-DD HH24:MI:SS’);
  
  If auditing was enabled at the time of deletion, there should be a record.
  SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION LIKE ‘%ROLE%’;
  SELECT OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, OBJ_NAME, ACTION_NAME, SQL_TEXT
  FROM DBA_AUDIT_TRAIL
  WHERE ACTION_NAME = ‘DROP ROLE’;
  
  LikeLike
  
  Reply