Get OID User's Direct Memberships using PLSQL

Here is an example of how to get all the direct group memberships for a given user, the procedure receives the user name (or you could just capture the current user with portal.wwctx_api.get_user)

The procedure first searches the OID for the user to get its full DN, after this it searches again the directory looking for groups where the user is a uniquemember.

It prints the CN of the group (subtring of the DN)

CREATE OR REPLACE PROCEDURE GET_DIRECT_MEMBERSHIPS (p_username in varchar2)
IS

retval PLS_INTEGER;
my_session DBMS_LDAP.session;
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;
lv_dn VARCHAR2(2000);
group_type PLS_INTEGER := DBMS_LDAP_UTL.TYPE_DN;
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
lv_cn varchar2(200);
j NUMBER;
my_message DBMS_LDAP.message;
my_entry DBMS_LDAP.message;
entry_index PLS_INTEGER;
my_dn VARCHAR2(256);
my_attr_name VARCHAR2(256);
my_ber_elmt DBMS_LDAP.ber_element;
attr_index PLS_INTEGER;
i PLS_INTEGER;
my_vals DBMS_LDAP.STRING_COLLECTION ;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
user_handle DBMS_LDAP_UTL.HANDLE;
user_type PLS_INTEGER;
gv_ldap_host VARCHAR2(256):= '127.0.0.1' ;
gv_ldap_port PLS_INTEGER:= '389';
gv_ldap_user VARCHAR2(256):= 'cn=orcladmin';
gv_ldap_passwd VARCHAR2(256):= 'welcome1';
gv_group_base VARCHAR2(256):= 'cn=Groups,dc=acme,dc=com,dc=au';
gv_user_base VARCHAR2(256):= 'cn=Users,dc=acme,dc=com,dc=au';

BEGIN

user_type :=DBMS_LDAP_UTL.TYPE_NICKNAME;
retval := -1;
DBMS_LDAP.USE_EXCEPTION := TRUE;

--Initialize ldap connection
my_session := DBMS_LDAP.init(gv_ldap_host,gv_ldap_port);
retval := DBMS_LDAP.simple_bind_s(my_session,gv_ldap_user, gv_ldap_passwd);

--Define Attributes for the search
my_attrs(1) := 'dn';

-- Search User to retrive full DN
retval := DBMS_LDAP.search_s(my_session, gv_user_base,
DBMS_LDAP.SCOPE_SUBTREE,
'uid='||p_username,
my_attrs,
0,
my_message);

--Get First Entry of the results
my_entry := DBMS_LDAP.first_entry(my_session, my_message);
-- get user's DN
lv_dn := DBMS_LDAP.get_dn(my_session, my_entry);
htp.p('<h1>User : '||lv_dn||'</h1>');


--Now we search for all the groups that have the user's DN as a UNIQUEMEMBER
retval := DBMS_LDAP.search_s(my_session, gv_group_base,
DBMS_LDAP.SCOPE_SUBTREE,
'uniquemember='||lv_dn,
my_attrs,
0,
my_message);


-- count the number of entries returned
retval := DBMS_LDAP.count_entries(my_session, my_message);

my_entry := DBMS_LDAP.first_entry(my_session, my_message);
entry_index := 1;

--Loop throught the results
while my_entry IS NOT NULL loop

my_dn := DBMS_LDAP.get_dn(my_session, my_entry);
lv_cn:=substr( my_dn, (instr(my_dn,'=')+1));
lv_cn:=substr( lv_cn,0,(instr(lv_cn,',')-1));
htp.p('<br>'||lv_cn);
my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
entry_index := entry_index+1;
end loop;

--Close the connection
retval := DBMS_LDAP.unbind_s(my_session);

EXCEPTION
WHEN OTHERS THEN
htp.p(sqlerrm);
END;

Another way to get the memberships, is to use the Oracle Portal tables, these tables have been left in portal as part of backwards compatibility, the only problem with this approach is that if the user hasn't logged in to portal, or if he's memberships have been modified since the last time he did it, the information is no going to be there.

You can query the user memberships by simply:

select * from portal.wwv_user_groups where user_name='MyUSER';

Here is an example couple of reports I did for a client not long ago, it shows the members of a particular group, all the users and the groups they are members of and there is an extra report that displays the users that exist in OID but because the haven't logged into portal the are not in the table wwv_user_groups

create or replace PACKAGE USER_REPORTS_PKG AS

PROCEDURE LIST_ALL_USERS;
PROCEDURE LIST_MEMBERS (p_group in varchar2);
FUNCTION get_memberships (p_username in varchar2) RETURN VARCHAR2;
PROCEDURE INACTIVE_USERS (p_filter in varchar2 default 'objectclass=*');

END USER_REPORTS_PKG;

CREATE OR REPLACE PACKAGE BODY user_reports_pkg AS

FUNCTION get_html_heading RETURN VARCHAR2 IS p_heading VARCHAR2(2000);
BEGIN
p_heading := '<html><head>
<style>
h1 {font-family: Arial, Helvetica, sans-serif; font-size: 12pt; font-weight: bolder; color: #000000; }
h2 {font-family: Arial, Helvetica, sans-serif; font-size: 11pt; font-weight: bolder; color:#000000; }
h3 {font-family: Arial, Helvetica, sans-serif; font-size: 10pt; font-weight: bold; color: #4C4D92; }
p {font-family: Arial, Helvetica, sans-serif; color:#000000; font-size: 9pt;}
td {font-family: Arial, Helvetica, sans-serif; color:#000000; font-size: 9pt;}
body {font-family: Arial, Helvetica, sans-serif; font-size: 9pt; }
.form_heading {font-weight: bold; color: #000000; font-size: 9pt; background-color: #E8E8E8;}
.tableheading {font-weight: bold; font-family:Arial, Helvetica, sans-serif; font-size: 9pt; color: #FFFFFF; background-color: #6C8FA0; padding:4px;}
.tableborder {border: 1px solid #D6D6EB;}
.form_error {font-family: Arial, Helvetica, sans-serif; font-size: 9pt; color:#FF0000; font-weight: normal; }
.error {font-family: Arial, Helvetica, sans-serif; font-size: 9pt; color:#FF0000; font-weight: normal; }

.Product {font-family: Arial, Helvetica, sans-serif; font-size: 12pt; font-weight: bold; color: #000000; }
.row1 {background-color: #FFFFFF; }
.row2 {background-color: #E8E8E8; }


a:link { color: #0000FF;}
a:visited { color: #0000FF;}
a:hover { color: #CC3300; text-decoration: none;}
a:active { color: #333333;}
</style>

</head><body>';
RETURN p_heading;

EXCEPTION
WHEN others THEN
RETURN NULL;
END;

FUNCTION is_admin RETURN boolean IS

admin_member boolean := FALSE;
admin2_member boolean := FALSE;
view_member boolean := FALSE;
p_user_id VARCHAR2(15) := portal.wwctx_api.get_user_id;

BEGIN

BEGIN
admin_member := portal.wwsec_api.is_user_in_group(p_user_id, portal.wwsec_api.group_id('PORTAL_ADMINISTRATORS'));

EXCEPTION
WHEN others THEN
admin_member := FALSE;
END;

IF admin_member THEN
RETURN TRUE;
END IF;

RETURN FALSE;

EXCEPTION
WHEN others THEN
RETURN FALSE;

END;

FUNCTION get_memberships(p_username IN VARCHAR2) RETURN VARCHAR2 IS p_memberships VARCHAR2(4000);
BEGIN

FOR lv_rec IN
(SELECT group_name
FROM portal.wwv_user_groups
WHERE user_name = p_username
AND group_name != 'AUTHENTICATED_USERS')
LOOP
p_memberships := p_memberships || ', ' || lv_rec.group_name;
END LOOP;

RETURN p_memberships;

EXCEPTION
WHEN others THEN
RETURN NULL;
END;

PROCEDURE list_members(p_group IN VARCHAR2) IS

CURSOR c_report IS
SELECT user_name
FROM portal.wwv_user_groups
WHERE group_name IN(p_group)
ORDER BY user_name;
l_person_rec wwsec_person % rowtype;
v_row NUMBER;
no_access

EXCEPTION;
BEGIN

IF is_admin = FALSE THEN
RAISE no_access;
END IF;

htp.p(get_html_heading);

--PAGE TITLE
htp.p('<h1>' || p_group || ' members</h1>');
htp.p('<table border="0" width="700" class="tableborder" cellpadding="3" cellspacing="3">');
htp.p('<tr>');
htp.p('<td class="tableheading">Username</td>');
htp.p('<td class="tableheading">First Name</td>');
htp.p('<td class="tableheading">Last Name</td>');
htp.p('<td class="tableheading">Email</td>');
htp.p('</tr>');

v_row := 0;

FOR lv_rec IN c_report
LOOP

BEGIN
l_person_rec := wwsec_api.person_info(p_user_name => lv_rec.user_name);

EXCEPTION
WHEN others THEN
l_person_rec := NULL;
END;

IF MOD(v_row, 2) = 0 THEN
htp.p('<tr class="row1">');
ELSE
htp.p('<tr class="row2">');
END IF;

v_row := v_row + 1;

htp.p('<td>' || lv_rec.user_name || '</td>');
htp.p('<td>' || l_person_rec.first_name || '</td>');
htp.p('<td>' || l_person_rec.last_name || '</td>');
htp.p('<td>' || l_person_rec.email || '</td>');

htp.p('</tr>');

END LOOP;

--SURROUNDING TABLE_ENDS
htp.p('</table><br />');
--HTML FORM DEFINITION_ENDS
htp.p('</body></html>');

EXCEPTION
WHEN no_access THEN
htp.p(' Error : You don''t have access to perform this operation');
WHEN others THEN
htp.p(' Error code : ' || to_char(SQLCODE));
htp.p(' Error Message : ' || sqlerrm);
htp.p(' Exception encountered .. exiting');
END;

PROCEDURE list_all_users IS no_access

EXCEPTION;
CURSOR c_report IS
SELECT UNIQUE user_name
FROM portal.wwv_user_groups
ORDER BY user_name;

l_person_rec wwsec_person % rowtype;
v_row NUMBER;

BEGIN

IF is_admin = FALSE THEN
RAISE no_access;
END IF;

htp.p(get_html_heading);

--PAGE TITLE
htp.p('<h1>Compass Users</h1>');
htp.p('<table border="0" width="700" class="tableborder" cellpadding="3" cellspacing="3">');
htp.p('<tr>');
htp.p('<td class="tableheading">Username</td>');
htp.p('<td class="tableheading">First Name</td>');
htp.p('<td class="tableheading">Last Name</td>');
htp.p('<td class="tableheading">Email</td>');
htp.p('<td class="tableheading">Memberships</td>');
htp.p('</tr>');

v_row := 0;

FOR lv_rec IN c_report
LOOP

BEGIN
l_person_rec := wwsec_api.person_info(p_user_name => lv_rec.user_name);

EXCEPTION
WHEN others THEN
l_person_rec := NULL;
END;

IF MOD(v_row, 2) = 0 THEN
htp.p('<tr class="row1">');
ELSE
htp.p('<tr class="row2">');
END IF;

v_row := v_row + 1;

htp.p('<td>' || lv_rec.user_name || '</td>');
htp.p('<td>' || l_person_rec.first_name || '</td>');
htp.p('<td>' || l_person_rec.last_name || '</td>');
htp.p('<td>' || l_person_rec.email || '</td>');
htp.p('<td>' || get_memberships(lv_rec.user_name) || '</td>');

htp.p('</tr>');

END LOOP;

--SURROUNDING TABLE_ENDS
htp.p('</table><br />');
--HTML FORM DEFINITION_ENDS
htp.p('</body></html>');

EXCEPTION
WHEN no_access THEN
htp.p(' Error : You don''t have access to perform this operation');
WHEN others THEN
htp.p(' Error code : ' || to_char(SQLCODE));
htp.p(' Error Message : ' || sqlerrm);
htp.p(' Exception encountered .. exiting');
END;

PROCEDURE list_members_oid(p_group IN VARCHAR2) IS

no_access

EXCEPTION;
ldap_host VARCHAR2(256) := 'myinfraserver';
ldap_port pls_integer := '4032';
ldap_user VARCHAR2(256) := 'cn=orcladmin';
ldap_passwd VARCHAR2(256) := 'oracle10g';
retval pls_integer;
my_session dbms_ldap.SESSION;
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 := dbms_ldap_utl.type_dn;
my_mod_pset dbms_ldap_utl.mod_property_set;
my_attrs dbms_ldap.string_collection;
l_group_rec portal.wwsec_group$ % rowtype;
lv_cn VARCHAR2(200);
l_person_rec wwsec_person % rowtype;
BEGIN

IF is_admin = FALSE THEN
RAISE no_access;
END IF;

l_group_rec := portal.wwsec_api.group_info(p_group_id => portal.wwsec_api.group_id(UPPER(p_group)));

group_id := l_group_rec.dn;

dbms_ldap.use_exception := TRUE;
my_session := dbms_ldap.init(ldap_host, ldap_port);
my_attrs(1) := 'uniquemember';
retval := dbms_ldap_utl.create_group_handle(group_handle, group_type, group_id);
retval := dbms_ldap_utl.get_group_properties(my_session, group_handle, my_attrs, dbms_ldap_utl.entry_properties, my_pset_coll);

IF retval != dbms_ldap_utl.success THEN
htp.p('get_group_properties returns : ' || to_char(retval));
END IF;

IF my_pset_coll.COUNT > 0 THEN

FOR i IN my_pset_coll.FIRST .. my_pset_coll.LAST
LOOP
retval := dbms_ldap_utl.get_property_values(my_pset_coll(i), my_attrs(1), my_property_values);

IF my_property_values.COUNT > 0 THEN
FOR k IN my_property_values.FIRST .. my_property_values.LAST
LOOP

IF instr(UPPER(my_property_values(k)), 'CN=USERS') > 0 THEN
lv_cn := SUBSTR(my_property_values(k), (instr(my_property_values(k), '=') + 1));
lv_cn := SUBSTR(lv_cn, 0, (instr(lv_cn, ',') -1));
l_person_rec := wwsec_api.person_info(p_user_name => lv_cn);
htp.p('<br />' || lv_cn);
htp.p(' - ' || l_person_rec.email);
END IF;

END LOOP;
END IF;

END LOOP;
END IF;

dbms_ldap_utl.free_handle(group_handle);
retval := dbms_ldap.unbind_s(my_session);

EXCEPTION
WHEN no_access THEN
htp.p(' Error : You don''t have access to perform this operation');
WHEN others THEN
htp.p(' Error code : ' || to_char(SQLCODE));
htp.p(' Error Message : ' || sqlerrm);
htp.p(' Exception encountered .. exiting');
END;

FUNCTION has_logged(p_username IN VARCHAR2) RETURN boolean IS lv_exist NUMBER;
BEGIN
SELECT COUNT(*)
INTO lv_exist
FROM portal.wwv_user_groups
WHERE UPPER(user_name) = UPPER(p_username);

IF lv_exist > 0 THEN
RETURN TRUE;
END IF;

RETURN FALSE;

EXCEPTION
WHEN others THEN
RETURN FALSE;
END;
PROCEDURE inactive_users(p_filter IN VARCHAR2 DEFAULT 'objectclass=*')

IS
search_filter VARCHAR2(512);
retval pls_integer;
my_session dbms_ldap.SESSION;
v_row NUMBER := 0;
my_attrs dbms_ldap.string_collection;
my_message dbms_ldap.message;
my_entry dbms_ldap.message;
entry_index pls_integer;
temp_vals dbms_ldap.string_collection;
subscriber_handle dbms_ldap_utl.handle;
gv_ldap_host VARCHAR2(256) := '10.6.252.129';
gv_ldap_port pls_integer := '4032';
gv_ldap_user VARCHAR2(256) := 'cn=orcladmin';
gv_ldap_passwd VARCHAR2(256) := 'iasinfra1';
gv_user_base VARCHAR2(256) := 'cn=users,dc=eurest,dc=com,dc=au';

lv_count NUMBER := 0;
BEGIN

retval := -1;
dbms_ldap.use_exception := TRUE;

--Initialize ldap connection
my_session := dbms_ldap.init(gv_ldap_host, gv_ldap_port);
retval := dbms_ldap.simple_bind_s(my_session, gv_ldap_user, gv_ldap_passwd);

--Define Attributes for the search
my_attrs(1) := 'cn';
-- my_attrs(1) := 'uid';
my_attrs(2) := 'givenname';
my_attrs(3) := 'sn';
my_attrs(4) := 'mail';
my_attrs(5) := 'telephoneNumber';

retval := dbms_ldap.search_s(my_session, gv_user_base, dbms_ldap.scope_subtree, p_filter, my_attrs, 0, my_message);

-- count the number of entries returned
retval := dbms_ldap.count_entries(my_session, my_message);

htp.p('<html><head><style>
td {font-family: Arial, Helvetica, sans-serif; color:#000000; font-size: 9pt;}
body {font-family: Arial, Helvetica, sans-serif; font-size: 9pt; }
.tableheading {font-weight: bold; font-family:Arial, Helvetica, sans-serif;
font-size: 9pt; color: #FFFFFF; background-color: #6C8FA0; padding:4px;}
.tableborder {border: 1px solid #D6D6EB;}
.row1 {background-color: #FFFFFF; }
.row2 {background-color: #E8E8E8; }
</style></head><body>');

htp.p('<table border="0" width="100%" class="tableborder" cellpadding="3" style="font-size: 21px;"> cellspacing="3" >');

htp.p('<tr>');
htp.p('<td class="tableheading">DN</td>');

FOR i IN my_attrs.FIRST .. my_attrs.LAST
LOOP
htp.p('<td class="tableheading">' || my_attrs(i) || '</td>');
END LOOP;

htp.p('</tr>');
v_row := 0;

my_entry := dbms_ldap.first_entry(my_session, my_message);
WHILE my_entry IS NOT NULL
LOOP
temp_vals := dbms_ldap.get_values(my_session, my_entry, 'cn');

IF temp_vals.COUNT > 0 THEN

IF has_logged(temp_vals(0)) = FALSE THEN

IF MOD(v_row, 2) = 0 THEN
htp.p('<tr class="row1">');
ELSE
htp.p('<tr class="row2">');
END IF;

v_row := v_row + 1;

htp.p('<td>' || dbms_ldap.get_dn(my_session, my_entry) || '</td>');
FOR i IN my_attrs.FIRST .. my_attrs.LAST
LOOP
temp_vals := dbms_ldap.get_values(my_session, my_entry, my_attrs(i));

IF temp_vals.COUNT > 0 THEN
htp.p('<td>' || temp_vals(0) || '</td>');
ELSE
htp.p('<td>--</td>');
END IF;

END LOOP;

htp.p('</tr>');
lv_count := lv_count + 1;
END IF;

END IF;

my_entry := dbms_ldap.next_entry(my_session, my_entry);
entry_index := entry_index + 1;
END LOOP;

htp.p('</table>');
htp.p(lv_count || ' Results of ' || retval || 'Total Users');
htp.p('</body></html>');

--Close the connection
retval := dbms_ldap.unbind_s(my_session);

EXCEPTION
WHEN others THEN
htp.p('Error:' || sqlerrm);
END;

END user_reports_pkg;
/

Let me know if you found this useful ...
Enjoy!!