DRM: How to check DRM User creation date

Topic:  How to find out 'Users creation date' in Oracle Data Relationship Management (DRM)

Hi Friends,

Auditing of users is an important part of any Oracle Hyperion Production system. You might have faced a scenario where you need to find out when a particular business user was added or assigned access into your Hyperion applications to cross-check whether there was a corresponding change request or incident number raised for record or not. These are some pertinent questions, which you have to find answers to, during Oracle Hyperion applications auditing.  

In this post, we will explore how to find out when was a new user created/added into your Oracle Data Relationship Management (DRM) system.

Important Note:

• This post has been written and associated activities have been demonstrated on Oracle Data Relationship Management (DRM) version 11.1.2.4.344.
• The demonstrating Hyperion environment has Oracle database server 12.2.0.2 (18c) as backend database. 

When you log in to Oracle Data Relationship Management (DRM) Web Client with DRM Administrator account and navigate to: Administer—Security—Users, you will see your DRM users list as shown below:

A new user is added/created to Oracle Data Relationship Management (DRM) system either directly from DRM Web Client (Administer—Security—Users) or synchronized from Hyperion Shared services when ‘Common User Provisioning’ is enabled in your Oracle DRM system.

Adding/creating a new user in Oracle DRM is a kind of transaction that is recorded in a table name RM_TRANSACTION_HISTORY in DRM_DB relational repository schema.

RM_TRANSACTION_HISTORY table contains a row for each transactional update to your DRM repository (DRM_DB schema).

There is a column name C_ACTION in the table RM_TRANSACTION_HISTORY that records activities/actions like Add User, Add Node, Insert Node, Delete Node, ChangeProp, etc. 

In order to extract Oracle DRM users creation date along with who created that user, perform below steps:

1- Login to your DRM_DB relational repository schema (using SQL developer).

2- Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, D_TIMESTAMP as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

3- Your output will be generated as shown below:

Description of the output:

• USER_NAME = The already existing DRM user who has created/added the new user.
• ACTION = Add User activity is performed
• ACTION_DESCRIPTION = User ‘NEW USERNAME’ added
• DATE_TIME = Date and time when the NEW USERNAME was created/added into Oracle DRM system in descending order (Newest user creation date first--oldest user creation date in last).

Optionally, if you would like to display DATE_TIME in 'YYYY-MM-DD HH24:MM:SS' format:

Run the below query:

select C_USER_NAME as USER_NAME, C_ACTION as ACTION, C_ACTION_DESCRIPTION as ACTION_DESCRIPTION, TO_CHAR(D_TIMESTAMP, 'YYYY-MM-DD HH24:MM:SS') as Date_Time from RM_Transaction_History where C_ACTION = 'Add User' order by Date_Time desc;

Note: In the above two examples, USER_NAME column signifies different kinds of DRM users who can create/add new users into Oracle DRM system. Below is the detail of the same:

• USER_NAME = ‘ADMIN’ implies that the new user has been created/added by DRM Default Administrator
• USER_NAME = ‘@@PROCESS’ implies that the new user has been created/added by synchronization process from Hyperion Shared Services  in a ‘Common User Provisioning’ enabled Oracle DRM application. '@@PROCESS' is a default internal user in Oracle DRM set up to handle inter-process communication between server components.
• USER_NAME = ‘Any existing application user’ implies that the new user has been created/added by an existing DRM business user who has been assigned privileges (Application Administrator privilege) to create/add a new user into Oracle DRM application. 

That's all for this post.

I hope this article has helped you. 

Your suggestions/feedback are most welcome.

Keep learning and Have a great day!!!