DRM Tutorial: Bulk Inserting DRM Users

Disclaimer: Not recommended / endorsed by Oracle.  Follow these steps at your own risk.

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

Recently, I was tasked with adding Users to DRM. The list contained almost TWO HUNDRED users!  Using the below three windows to input the users one by one was not a viable option in my opinion, instead I was able to bulk insert the users into DRM. Work smart whenever possible!

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 6 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 and assigned the “Workflow User” role.

 

Step A: Backup DB Tables

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

 

Step B: Acquire Pertinent Pieces of Information

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

1. 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:
     
   • i_role_id for “Workflow User” –> 102
2. 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:
     
   • MAX(i_user_id) + 1 –> 362
3. 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:
     
   • MAX(i_access_group_id) + 1 –> 316

 

 Step C: Create data files for import into the following 5 tables

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

1. Populating RM_User data
   • Not ALL fields in the RM_User table are required for each user added. I got away with only inputting data into the above 9 fields.
   • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
2. Populating RM_Category_User data
   
   • 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 B2 –> MAX(i_user_id) + 1 –> 362
3. Populating RM_Object_Access_Group data
   
   • The number sequence for the i_access_group_id field will begin with the value derived in Step B3 –> MAX(i_access_group_id) + 1 –> 316.
4. Populating RM_Object_Access_Group_User data
    
   
   • The number sequence for the i_access_group_id field will begin with the value derived in Step B3 –> MAX(i_access_group_id) + 1 –> 316
   • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
5. Populating RM_User_Role data
   
   • The number sequence for the i_user_id field will begin with the value derived in Step B2 –> MAX(i_user_id) + 1 –> 362
   • The i_role_id will be set for the value derived in Step B1 –> i_role_id for “Workflow User” –> 102

Step D: Import / Insert the prepared user data from prior Step into the DRM tables

I leveraged the Microsoft SQL Server Management Studio Import and Export Wizard.

1. Import the data for the RM_User and RM_Object_Access_Group tables first.
2. Import the data for the remaining 3 tables:
   • RM_User_Role
   • RM_Category_User
   • RM_Object_Access_Group_User

 

Step E: Reset Database Sequence Numbers  

1. 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:
     
   • MAX(i_user_id) –> 371
2. 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:
     
   • MAX(i_access_group_id) –> 325
3. 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 F: Restart Oracle DRM Service

 

Step G: Verify Users Were Added Successfully

Step H: Confirm Users Roles Associated to Users Are Correct