Security Software

We have an Hyperion application which is configured to store store the user's objectGUID from Active directory in the WHO columns i.e. created_by and last_updated_by column in eery tables.
So when I query a table in the database, I see objectGUID in the following format stored in VARCHAR2(255) columns -

msad://OBJECTGUID=\bb\87\c3\6d\03\bc\a5\44\b3\b5\8c\08\f2\5b\b8\8c?USER

This seems to me like the text representation of the objectGUID. I can copy the value "\bb\87\c3\6d\03\bc\a5\44\b3\b5\8c\08\f2\5b\b8\8c" and paste it in Apache Directory Studio search function and get to the right user. So it seems to me that this text representation of objectGUID should work in procedures like DBMS_LDAP.SEARCH_S.

I have a PL/SQL block of code which will be converted to a function eventually and the main purpose of this code is to get the samaccountName attribute or the email attribute of the user.
The problem is that it does not work when I use objectGUID as a search criteria it does not return anything. I am pasting the code below for your reference

DECLARE
v_SearchUsername VARCHAR2(200) := '\ba\e7\c9\4d\52\bb\f9\43\8e\ab\50\d6\28\4d\9b\9d';
-- v_SearchUsername VARCHAR2(200) := '4dc9e7babb5243f98eab50d6284d9b9d';
-- v_SearchUsername VARCHAR2(100) := 'mpurohit';
v_LDAPSession DBMS_LDAP.SESSION;
v_LDAPAttributeQueryList DBMS_LDAP.STRING_COLLECTION ;
v_LDAPQueryResults DBMS_LDAP.MESSAGE;
v_BerElement DBMS_LDAP.BER_ELEMENT;

v_FunctionReturnValue PLS_INTEGER;
v_Result VARCHAR2(2000);


BEGIN
v_Result := '';

--v_LDAPAttributeQueryList(1) := 'objectGUID';

v_LDAPSession := DBMS_LDAP.INIT('03s03dc01.zebra.lan', '389');
--v_LDAPSession := DBMS_LDAP.INIT('10.3.1.248', '389');
v_FunctionReturnValue := DBMS_LDAP.SIMPLE_BIND_S(v_LDAPSession,
'CN=03LDAP_OID,OU=Resource Accounts,OU=03,DC=zebra,DC=lan',
'oidactdir');
v_LDAPAttributeQueryList(1) := 'distinguishedName';
v_FunctionReturnValue := DBMS_LDAP.SEARCH_S(
ld => v_LDAPSession,
base => 'DC=zebra,DC=lan',
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectGUID='||v_SearchUsername,
--filter => 'samaccountname=' || v_SearchUsername,
attrs => v_LDAPAttributeQueryList,
attronly => 0,
res => v_LDAPQueryResults);
v_FunctionReturnValue := DBMS_LDAP.COUNT_ENTRIES(v_LDAPSession,
v_LDAPQueryResults);
IF DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults) IS NOT NULL THEN
v_Result := DBMS_LDAP.GET_VALUES(
v_LDAPSession,
DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults),
DBMS_LDAP.FIRST_ATTRIBUTE(
v_LDAPSession,
DBMS_LDAP.FIRST_ENTRY(
v_LDAPSession,
v_LDAPQueryResults),
v_BerElement)) (0);
END IF;

dbms_output.put_line('result='||v_Result);

v_FunctionReturnValue := DBMS_LDAP.UNBIND_S(v_LDAPSession);
END;
/

Can someone please suggest what I can do in such a case?

Any help would be appreciated.

Thanks

Can anybody suggest alternatives?

Edited by: user11244575 on Oct 30, 2012 12:44 PM