Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 and OID – USER and GROUP – Phase 2

Posted by Venkatakrishnan J on October 12, 2007

In the last post, we saw how to establish OID authentication. But that was the simplest part in the sense that all one would have to do is establish the connection to OID by giving in the proper credentials. In this post, we would see how to go about getting GROUPs from OID for the logged in user. This is also straightforward once we know how to get the groups from OID using the user names. In order to the user related groups one would have to use the DBMS_LDAP package. This package has some methods that would return the groups of users. In our case, what we would do is to call this package from a pipelined function so that we can directly use this function in the repository. Following is the function that i used

CREATE OR REPLACE FUNCTION GETUSERGROUP(Username in Varchar2) RETURN ARRAY PIPELINED AS
ldap_host VARCHAR2(256);
ldap_port PLS_INTEGER;
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
sub_type PLS_INTEGER;
subscriber_id VARCHAR2(2000);
my_pset_coll DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
my_property_names DBMS_LDAP.STRING_COLLECTION;
my_property_values DBMS_LDAP.STRING_COLLECTION;
group_handle DBMS_LDAP_UTL.HANDLE;
group_id VARCHAR2(2000);
group_type PLS_INTEGER;
user_handle DBMS_LDAP_UTL.HANDLE;
user_id VARCHAR2(2000);
user_type PLS_INTEGER;
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
group_string varchar2(32767);
BEGIN
— Please customize the following variables as needed
ldap_host := ‘10.176.246.145’ ;
ldap_port := 389;
ldap_user := ‘cn=orcladmin';
ldap_passwd := ‘welcome1′;
sub_type := DBMS_LDAP_UTL.TYPE_DN;
subscriber_id := ‘o=acme,dc=com';
user_type := DBMS_LDAP_UTL.TYPE_DN;
user_id := ‘cn=’||Username||’,cn=users,dc=idc,dc=oracle,dc=com';
group_type := DBMS_LDAP_UTL.TYPE_DN;
group_id := ‘cn=group1,cn=groups,o=acme,dc=com';
— Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;
———————————————–
— Connect to the LDAP server
— and obtain and ld session.
———————————————–
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
———————————————–
— Bind to the directory

———————————————–
retval := DBMS_LDAP.simple_bind_s(my_session,
ldap_user,
ldap_passwd);
———————————————————————
— Create User Handle

———————————————————————
retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘create_user_handle returns : ‘ || to_char(retval));
END IF;
—————————————
— Get Group Membership

—————————————
my_attrs.delete();
my_attrs(1) := ‘cn';
retval := DBMS_LDAP_UTL.get_group_membership ( my_session,
user_handle,
DBMS_LDAP_UTL.DIRECT_MEMBERSHIP,
my_attrs,
my_pset_coll );
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘get_group_membership returns : ‘ || to_char(retval));
END IF;
IF my_pset_coll.count > 0 THEN
group_string := NULL;
FOR i in my_pset_coll.first .. my_pset_coll.last LOOP
retval := DBMS_LDAP_UTL.get_property_names(my_pset_coll(i),
my_property_names);
IF my_property_names.count > 0 THEN
FOR j in my_property_names.first .. my_property_names.last LOOP
retval := DBMS_LDAP_UTL.get_property_values(my_pset_coll(i),
my_property_names(j),
my_property_values);
IF my_property_values.COUNT > 0 THEN
FOR k in my_property_values.FIRST..my_property_values.LAST LOOP
DBMS_OUTPUT.PUT_LINE( my_property_names(j) || ‘ : ‘ ||
my_property_values(k));
IF my_property_names(j) = ‘cn’ THEN
PIPE ROW(my_property_values(k));
END IF;
END LOOP;
END IF;
END LOOP;
END IF; — IF my_property_names.count > 0
END LOOP;
END IF; — If my_pset_coll.count > 0
———————————————————————
— Free handle

———————————————————————
DBMS_LDAP_UTL.free_handle(user_handle);
— unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘unbind_s returns : ‘ || to_char(retval));
END IF;
— Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ Error code : ‘ || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(‘ Error Message : ‘ || SQLERRM);
DBMS_OUTPUT.PUT_LINE(‘ Exception encountered .. exiting’);
END GETUSERGROUP;
/

Before creating the above function one would have to create a array type using the below code.

create or replace type array as table of varchar2(32767); 

Now lets test out the above function.

      

      

As you see above i have 2 users orcladmin and Test in my OID that are associated with certain groups. The above function would return the data that we needed. Once this is done the next step is to create another session initialization block that would basically call this database function. If you do not have a connection pool for connecting to the schema of the database function create one. In my case i used a connection pool called Authentication.

      

Name the Initialization block as GroupIB and click on Edit Data Source.

      

Choose database as the data source and enter the above sql. Then go to Edit Data Target and set Row-Wise Initialization. This would convert the above sql in the datasource into a single row statement.

Just to be sure, test the initialization block (after changing :USER to orcladmin in the data source. You should be getting the output similar to the below picture.

      

Once this is done click on edit preference and click on Edit Execution Precedence and select the first initialization block (OIDIB) that we created in the last post. Also remeber to check the Used for Authentication check box.

      

Now go to BI Answers login page and login as orcladmin. In order to see whether our groups have been properly assigned lets create a sample report. In the title view of the report, enter the following

@{biServer.variables[‘NQ_SESSION.GROUP’]}

      

      

As you see above our groups have got populated properly into the GROUP variable.

About these ads

19 Responses to “Oracle BI EE 10.1.3.3/2 and OID – USER and GROUP – Phase 2”

  1. Raghuraman said

    Nice Job. Lots of useful Info about OBIEE. It will be nice to have an example of Other Database Access like Micorsoft SQLSERVER / OLAP if possible.

  2. oddjob said

    Excellent stuff – I’m going to try and do something similar with Active Directory now.

  3. Build Your Own Residual Income Business
    Products to Make You Feel Great, a Strong Support Team, and a Revolutionary New, Lucrative Compensation Plan! Agel is a new company and is uniquely positioned to be the next giant in this area. The company has developed an entirely new category of products. Imagine being part of the next industry-changing innovation.

    Video information http://www.biz.go-agel.biz/index.php?newlang=english&name=videoclips&op=CatView&cat=2
    This video may change your life forever.

    Click here to get more information http://www.biz.go-agel.biz/index.php?newlang=russian&newlang=english

  4. P. Girolami said

    Great article! I’ve used Microsoft Active Directory as LDAP Server and it works fine!
    Very usefull; thanks!

    • Nilesh said

      Hi P.Girolami,

      Could you pls share the SQL code that you used for Active Directory. Can i use the same for Sun LDAP?

      Thanks,
      Nilesh

    • San said

      Hi Girolami,

      Could you please share the code which you used with Microsoft Active Directory as LDAP Server and brought the Group information into OBIEE.i am working on a similar integartion.

      Thanks & regards,
      san

      • Sriram said

        Could you please share tha code, I am tring to working on similar proj.Need to Authenticate user only belong to one group in active directory.

  5. Edwin said

    Hi, I Can’t use this example with Active Directory, I don’t understand the user_id value in this example.

    Some Idea?

    Thanks

  6. Pauline said

    Hi,

    I was able to utlize the example above to obtain groups from AD. I however have to utilize the Search_S method instead of the Get_group_membership method.

    Cheers,
    Pauline

  7. Sidhu said

    Thanks again for this info. I implemented this using Active Directory and as we didnt have groups in Active Directory and as there was no options to import all users, I had to create a separate table in some database and add user with their group. I followed the rest using your lines and it worked.

  8. Harish said

    Would be helpful to get the Active directory groups info into OBIEE without pulling them into database?

  9. Homero said

    Hi Venkatakrishnan J,
    I’m trying to bind OBIEE with MS Active Directory, I followed all the steps you describe but in the GET GROUP MEMBERSHIP section I get a -3 with the get_group_membership()… do i need to do something special to get the groups from MS AD??

    Hope you can help me, thanks in advance =)

  10. Samuel said

    Hi,

    Im trying to implement the above function.
    We are running Novell E-dir as our LDAP-server.

    When running:
    select * from table(GETUSERGROUP(‘dsslr’))
    I get the error:
    “get_group_membership returns : -3″

    Any ideas about this error ?
    Rgrds
    Samuel

  11. Rachna said

    A good Article, which will definately help me to get the user groups. Can you put some light on how to get Loglevel of the user

    Thanks

  12. Beliz said

    Do we need to run the function in the ldap schema?

    Thanks

  13. dpaus said

    Hi Venkat,

    I have implemented the above in DEV, however, I need to restrict users that belong only to one group to be authenticated. Do you have a post that shows how to restrict to only one group in the LDAP? I am using OBIEE 10.1.3.4.1 and the LDAP is Novell eDirectory (basically there is an Oracle bug that is being fixed in a future release, but I am implementing now).

    Also, I cannot find the previous post to this one about the OIDIB initialization block.

    Thanks,

    Dev

  14. adam said

    Hi I followed all the steps you describe but in the GET_GROUP_MEMBERSHIP section I get a -3 with the get_group_membership().

    I am using active directory i see some other users have posted the same issue are you able to provide a work around?

  15. Andrés said

    Hi

    I also obtain the message “get_group_membership returns: -3″, but this is because I use the uid to get groups, and the function require user_id (I not know if this are the same), when i use the user name the function return groups correctly.

    Regards,

    A.S.

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: