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!

DRM_Users01

DRM_Users02

DRM_Users03

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:
      RM_Role
    • 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:
      DRM_2B
    • 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:
      DRM_2C
    • MAX(i_access_group_id) + 1 –> 316

 

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

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

DRM_Users05

  1. Populating RM_User dataDRM_4A
    • 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
    DRM_4B

    • 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
    RM_Object_Access_Group

    • 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
    RM_Object_Access_Group_User 

    • 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
    RM_User_Role

    • 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:
      371
    • 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:
      371 (2)
    • 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
DRMService

 

Step G: Verify Users Were Added Successfully
UserRoleConfirmation


Step H: Confirm Users Roles Associated to Users Are Correct
 RoleConfir

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s