us-analytics.png
Ask an Expert

DRM Tutorial: 8 Steps for Adding New DRM Users in Bulk

 

 Recently, I was tasked with adding Users to DRM. The list contained almost 200 users! Inputting the users one-by-one was, in my opinion, not a viable option. Instead, I was able to save time by bulk inserting the users into DRM.

Bulk inserting DRM Users involves modifying/writing to back-end database tables. In addition to READ access to the DRM relational tables, you will also need WRITE access to the following six tables:

  • RM_User
  • RM_User_Role
  • RM_Category_User
  • RM_Object_Access_Group
  • RM_Object_Access_Group_User
  • DAL_Sequence

In the following example, users will be added to DRM with the “Workflow User” role.

Note: The below steps were used for an environment using MS SQL Server.

 Step 1: Backup DB Tables

As a general rule of thumb, it is always a good idea to backup database tables before modifying them. 

 
Step 2: Acquire Pertinent Pieces of Information

Because we will be inserting records into tables that contain primary keys and foreign keys, you will need to do a little detective work.

A. Determine the i_role_id key for the role you will be assigning to the users.

  • Execute the following query:  
SELECT * FROM RM_Role
Results:

 DRM_Tutorial_Step_2_A.png

  • i_role_id for “Workflow User” > 102

B. Determine the beginning number to assign to the i_user_id key for input into the RM_User table

  • Execute the following query: SELECT MAX(i_user_id)+1 FROM RM_User


Results: 

DRM_Tutorial_Step_2_B.png

  • MAX(i_user_id) + 1 > 362

C. Determine the beginning number to assign to the i_access_group_id key for input into the RM_Object_Access_Group table

  • Execute the following query: SELECT MAX(i_access_group_id)+1 FROM RM_Object_Access_Group
  •  Results:

DRM_Tutorial_Step_2_C.png

  • MAX(i_access_group_id) + 1 > 316
Step 3: Create Data File for Import into the Following 5 Tables

DRM_Tutorial_Step_3.png

I chose to create the data files for each table with specific fields in separate tabs within Excel, as shown below:

DRM_Tutorial_Step_3_1.png

A. Populating RM_User data

DRM_Tutorial_Step_3_A.png

  • Not ALL fields in the RM_User table are required for each user record added. I got away with only inputting data into the above nine fields.
  • The number sequence for the i_user_id field will begin with the value derived in Step 2B > MAX(i_user_id) + 1 > 362

B. Populating RM_Category_User data

DRM_Tutorial_Step_3_B.png

  • In the environment I was working with, i_category_id = -1 represents System and the access level required for the users is Read. If different categories and access levels are required for your purposes, adjust the data to suit your needs.
  • The number sequence for the i_user_id field will begin with the value derived in Step 2B > MAX(i_user_id) + 1 > 362

C. Populating RM_Object_Access_Group data

DRM_Tutorial_Step_3_C.png

  • The number sequence for the i_access_group_id field will begin with the value derived in Step 2C > MAX(i_access_group_id) + 1 > 316

D. Populating RM_Object_Access_Group_User data

DRM_Tutorial_Step_3_D.png

  • The number sequence for the i_access_group_id field will begin with the value derived in Step 2C > MAX(i_access_group_id) + 1 > 316
  • The number sequence for the i_user_id field will begin with the value derived in Step 2B > MAX(i_user_id) + 1 > 362

E. Populating RM_User_Role data
DRM_Tutorial_Step_3_E.png

  • The number sequence for the i_user_id field will begin with the value derived in Step 2B > MAX(i_user_id) + 1 > 362
  • The i_role_id will be set for the value derived in Step 2A > i_role_id for “Workflow User” > 102
Step 4: Import/Insert the Prepared User Data from Previous Step into the DRM Tables

Leverage the Microsoft SQL Server Management Studio Import and Export Wizard.

A. Import the data for the RM_User and RM_Object_Access_Group tables first.

B. Import the data for the remaining 3 tables:

  • RM_User_Role
  • RM_Category_User
  • RM_Object_Access_Group_User
Step 5: Reset Database Sequence Numbers

A. Determine the max i_user_id key in the RM_User table

  • Execute the following query: SELECT MAX(i_user_id) FROM RM_User
  • Results:
 DRM_Tutorial_Step_5_A.png
  • MAX(i_user_id) > 371
B. Determine the max i_access_group_id key in the RM_Object_Access_Group table
  • Execute the following query: SELECT MAX(i_access_group_id) FROM RM_Object_Access_Group
  • Results:

 DRM_Tutorial_Step_5_B.png

  • MAX(i_access_group_id) > 325

C. Execute the following update statements

  • UPDATE DAL_SEQUENCE
    SET i_id = 371
    WHERE c_key = 'i_user_id'

    and c_primary_table = 'RM_User'

  • UPDATE DAL_SEQUENCE
    SET i_id = 325
    WHERE c_key = 'i_access_group_id'
    and c_primary_table = 'RM_Object_Access_Group'

 

Step 6: Restart Oracle DRM Service

DRM_Tutorial_Step_6.png

 

Step 7: Verify that Users Were Added Successfully

DRM_Tutorial_Step_7.png

Step 8: Confirm User Roles Associated to Users are Correct

DRM_Tutorial_Step_8.png

 

Watch the Video: DRM Best Practices

Ask an EPM/BI Advisor

If you're here, you've got questions — and we've got answers. Book your consultation to ask us about any range of topics, including:

  • Evaluating EPM or BI technologies
  • Comparing on-prem vs. cloud
  • Planning upgrades and migrations
  • Estimating project costs and timeframes
  • And much more — ask us anything!

Let our experts tackle your toughest questions for you.

Let's Talk