Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on April 8, 2008

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.

Advertisements

13 Responses to “Oracle BI EE 10.1.3.3/2 – Automating import of Users/Groups into Repository using UDML”

  1. I finally decided to write a comment on your blog. I just wanted to say good job. I really enjoy reading your posts.

    Tina Russell

  2. Walt said

    Following is how to create an user group and assign an user to a special group:

    DECLARE SECURITY ROLE “Group01” AS “Group01”
    PRIVILEGES ( READ);
    DECLARE SECURITY ROLE “Group02” AS “Group02”
    PRIVILEGES ( READ);
    DECLARE USER “Test01” AS “Test01” FULL NAME {} PASSWORD ‘welcome1’ NEVER EXPIRES
    HAS ROLES (
    “Group01” )
    PRIVILEGES ( READ);
    DECLARE USER “Walt” AS “Walt” FULL NAME {} PASSWORD ‘welcome1’ NEVER EXPIRES
    HAS ROLES (
    “Group02” )
    PRIVILEGES ( READ);
    Save above to an udml file and use command ‘nqudmlexec’ for generation.
    Note: Don’t need to specify the ‘UPGRADE ID’ because it was generated by BIEE automatically.
    🙂

    Walt

    • swapna said

      Hi Walt,

      Thanks for your Input.
      How do I create Filters using UDML Scripts but I’m not able to create it.
      I’ve used the below script for creating filters.
      DECLARE SECURITY ROLE “Non Functional TEAM” AS “Non Functional TEAM”
      HAS USERS(
      “VIJAY”,
      “KRISHNA” ) PRIVILEGE PACKAGE “Pkg1”
      PRIVILEGES ( READ);
      DECLARE PRIVILEGE PACKAGE “Pkg1” AS “Pkg1”
      FILTER CONDITION (
      ENTITY FOLDER “FLASH”..”EXPENSE_TYPE” ( “FLASH”.”EXPENSE_TYPE”.”Gen2,EXPENSE_TYPE” = ‘All Expense Type’) ENABLE )
      PRIVILEGES ( READ);

      Could you tell me how do I create the filters using UDML Scripts.

      Regards
      Swapna

  3. Walt, great comment! Where could I find details about declare user (security roles)? What other options may exist in addition to these two?
    Thank you in advance,
    pp11pp22@yahoo.com

  4. Great comment Walt! Where could I find details about the declare user/security role, please? What other options but user/security role may exist? Thank you in advance pp11pp22@yahoo.com

  5. pp11pp22 said

    Great comment Walt! where could I find details about the declare user? thanks in advance , pp11pp22@yahoo.com

  6. […] groups if you are using RPD security (this would need, I believe, to be done using UDML scripting, see this other posting by Venkat). If anyone reading this knows of any others then add these as comments to the […]

  7. Javier Castellano said

    Hello,
    first of all thanks for your posts.
    This is my question:
    Is it possible to change a user password via udml ?
    Thanks in advance.

  8. santhosh said

    I created an udml file and placed it in the same folder where i have my RPD files and run the 4 line code in command prompt but it shows and “NQS Error 26012 at line 1”
    This is the code i ran in the cmd prompt

    nqudmlexec -U Administrator -P Administrator -I “C:\Documents and Settings\User\Desktop\Repository & catalog\importusers.udml” -B “C:\Documents and Settings\User\Desktop\Repository & catalog\import.rpd” -O “C:\Documents and Settings\User\Desktop\Repository & catalog\import.rpd”

    any suggestions please?

    Thanks in Advance.

  9. Andy said

    Dears,

    we make use of user security within the rpd file. New users will be created into our prod environment.
    When we have made changes to our dev rpd, we copy the entire rpd file from dev to prod. This way I’m loosing my users. Is there a way to export users from my prod rpd towards my dev rpd?

    Txs for your quick help!

    Kr,
    Andy

  10. Osman said

    Hi,

    i tried it also, but i’m getting the message that the uml file can’t be opened.
    What could be the problem? The file is not opened in notepad or other app.

    Kr.
    Osman

  11. shiva molabanti said

    Hi venkat,
    How to store different random passwords for each of the users.I have stored usernames and password in a table.
    We are getting usernames.but for passwords it is storing empty passwords irrespective to that what we have in table.

    Please help me.

    Thanks
    Shiva

    • Vishwanath said

      Hi Shiva,

      The passwords should be stored in the encrypted form in the table then only the password canb set in the rpd.

      For example: D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F

      above string should be stored in the password column for ‘welcome1’ as password in the rpd.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: