Oracle BI EE 10.1.3.3/2 – Automating import of Users/Groups into Repository using UDML

In my last blog entry here, we saw how to go about using UDML to automate the process of repository migration from Development to Test/Prod instances. Along, the same lines lets look at another interesting feature of UDML that would allow us to automate the process of importing Users/Groups automatically into the Repository. For example, consider a case wherein you have a list of users stored inside a database table. But you would like these users to be imported into the repository since you are planning to use repository authentication instead of external table authentication. Also, you do not want to create each of these users manually one by one. In such a case, UDML can come in very handy. The other scenario where this can be useful is when you are using LDAP/OID authentication. In such a scenario, you would need all the LDAP groups to be manually defined inside the repository. UDML again can come in very handy to automatically import all the groups into the repository. Lets try out the first example (importing users from a database table).

Lets start with a simple table containing only the list of users whom we need inside the repository.

······

This is nothing but a simple table containing around 10 users. Now, the idea is to import these users automatically into the repository. In order to do this, lets start with the below script (Thanks to Gerard Braat for this nice little simple tip in one of our internal email lists).

SELECT ‘DECLARE USER “‘||username||’” AS “‘||username||’” UPGRADE ID 1121 FULL NAME {} PASSWORD ”welcome1” NEVER EXPIRES
 PRIVILEGES ( READ);’ from rep_users

Just fire this from sql developer or from sqlplus with spool on to extract the resultant output into a physical file. You should get the output as shown below

······

If you want you can have different random passwords for each of the users. But this should give you an idea. Similarly, you can write a simple function which can retrive the groups from within OID. Now save the above file and fire the below command.

D:\oracle\OracleBI\server\Bin>nqudmlexec -U Administrator -P Administrator -I “D:\oracle\OracleBI\server\Sample\nqUDMLExec\User Import\userImport.udml” -B “D:\oracle\OracleBI\server\Sample\nqUDMLExec\User Import\BeforeUserImport.rpd” -O “D:\oracle\OracleBI\server\SamplenqUDMLExec\User Import\AfterUserImport.rpd”

······

Now you should get a repository with all the users automatically imported into the repository.

······

Another simple feature but can be very useful in a lot of situations.

---

Possibly related posts: (automatically generated)

• Oracle BI EE 10.1.3.3/2 – Creating Windows Service for BI EE OC4J
• Hyperion System 9 BI – Overview
• Oracle BI EE 10.1.3.3.1 – New release – New Features
• UDML in Oracle BI Server