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.
Posts
