spool bootstrapwithgroups.log;
------------------------------------------------------------------------
--
-- FILE
--  bootstrapwithgroups.sql :	BI User Community Sample Script
--					                  to bootstrap BI Catalog with groups/users
--					                  from OID
-- DESCRIPTION
--
--      This SQL file contains the PL/SQL code required to bootstrap
--      groups and its members from Oracle Internet Directory (LDAP server)
--      to BI Catalog.
--
--      This script needs some settings, please look in
--      the customizable settings section after first begin statement

--  NOTES
--	Make sure you have installed DBMS_LDAP package and the schema supplied with sample
--	The output from this script will be recorded in bootstrapwithgroups.log
--	If a group in OID already exists in BI Catalog, then it is not imported from OID
------------------------------------------------------------------------
set serveroutput on size 100000
set define on

DECLARE

	c_ldap_host                 VARCHAR2(256);
	c_ldap_port                 VARCHAR2(256);
	c_ldap_user                 VARCHAR2(256);
	c_ldap_passwd               VARCHAR2(256);
	c_ldap_base                 VARCHAR2(256);
	c_bootstrap_filter          VARCHAR2(128);  -- groups filter, users is *
	c_user_trace                BOOLEAN := true;
	c_search_scope              PLS_INTEGER := DBMS_LDAP.SCOPE_SUBTREE;
	c_permission  				      PLS_INTEGER := 10;
  c_username_attribute        VARCHAR2(100);
  c_common_container_dn       VARCHAR2(256);
	-- program variables
  
	v_retval                    PLS_INTEGER;
	v_session1                  DBMS_LDAP.session;
	v_session2                  DBMS_LDAP.session;
	v_attrs                     DBMS_LDAP.string_collection;
	v_ret_values                DBMS_LDAP.string_collection;
	v_message                   DBMS_LDAP.message;
	v_entry                     DBMS_LDAP.message;
	v_entry_index               PLS_INTEGER;
	v_attr_name                 VARCHAR2(256);
	v_ber_elmt                  DBMS_LDAP.ber_element;
	v_attr_index                PLS_INTEGER;
	i                           PLS_INTEGER;
	v_vals                      DBMS_LDAP.STRING_COLLECTION ;
	v_version                   VARCHAR2(1000) ;
	v_comp_version              VARCHAR2(1000) ;
	v_count                     PLS_INTEGER := 0;
	v_userlist                  List := List();
	v_grplist                   List := List();
	v_users_retval              PLS_INTEGER;
	v_users_filter              VARCHAR2(128);
	v_user_attrs                DBMS_LDAP.string_collection;
	v_users_message             DBMS_LDAP.message;
	v_user_attr_ret_values      DBMS_LDAP.string_collection;
	v_users_entry               DBMS_LDAP.message;
	v_userObject                ListObject;
	v_member_ret_values         DBMS_LDAP.STRING_COLLECTION;
	v_currentTime		            VARCHAR2(56);
	v_users_ldap_base           VARCHAR2(256); -- dynamically defined
	v_addthisgroup				      BOOLEAN;
	v_users_scope   	          PLS_INTEGER := DBMS_LDAP.SCOPE_SUBTREE;
  v_nicknameattribute         VARCHAR2(100);
BEGIN

	---- CUSTOMIZABLE SETTINGS BEGIN -----
	c_ldap_host  		      := 'ykosuru-pc.us.oracle.com';
	c_ldap_port  		      := '389';
	c_ldap_user  		      := 'cn=orcladmin';
	c_ldap_passwd		      := 'welcome';
	c_ldap_base           := 'ou=Groups;o=abc.com';
	c_search_scope		    := DBMS_LDAP.SCOPE_SUBTREE;
	c_user_trace 		      := true;
	c_bootstrap_filter 	  := '(objectclass=groupofnames)';
	c_permission 		      := 10;
  c_username_attribute  := null; 
  c_common_container_dn := 'cn=Common,cn=Products,cn=OracleContext';
	---- CUSTOMIZABLE SETTINGS END -----

  -- get the current time in UTC format -- 
	select to_char(sysdate,'YYYYMMDDHH24MISS') into v_currentTime from dual;
	dbms_output.put_line('Script Execution Time '|| v_currentTime);
  -- make sure that the RDBMS is not 8.x version
	dbms_utility.db_version(v_version, v_comp_version);
	dbms_output.put_line('Oracle Server Version '|| v_version||' detected ');
	if substr(v_version, 1, 2) = '8.' then
		dbms_output.put_line('Warning : In Oracle 8.x release, this script should not be run in MTS mode');
	end if;
	v_retval         := -1;
	DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || c_ldap_host);
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || c_ldap_port);
	DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Base ',25,' ') || ': ' || c_ldap_base);
	-- Choosing exceptions to be raised by DBMS_LDAP library.
	DBMS_LDAP.USE_EXCEPTION := TRUE;
  -- create two sessions with LDAP server
	-- we need two handles to LDAP in order to do nested search
	v_session1 := DBMS_LDAP.init(c_ldap_host,c_ldap_port);
	v_session2 := DBMS_LDAP.init(c_ldap_host,c_ldap_port);
	-- bind each session to the directory
	v_retval := DBMS_LDAP.simple_bind_s(v_session1, c_ldap_user, c_ldap_passwd);
	v_retval := DBMS_LDAP.simple_bind_s(v_session2, c_ldap_user, c_ldap_passwd);
	
  -- if username attribute has not been specified,  look for nick name attribute in LDAP server
  -- if user name attribute is specified, it takes precdence
  if c_username_attribute is null then
   -- get orcl common nick name attribute
    v_nicknameattribute := LDAP_BICATALOG_SYNC_UTILS.getnicknameattribute(v_session1,c_common_container_dn,c_user_trace);
    if v_nicknameattribute is null then
      if c_common_container_dn is null then
         -- if username attr is not specified, then common container dn must be specified
        dbms_output.put_line('TRACE: Either c_username_attribute must be set or c_common_container_dn must be set');
      else
        dbms_output.put_line('TRACE: orclcommonnicknameattribute value could not be identified');
        dbms_output.put_line('TRACE: Make sure common container and orclcommonnicknameattribute exists');
      end if;
      
      dbms_output.put_line('TRACE: Script terminating');
      goto exit_script;
    else
      c_username_attribute := v_nicknameattribute;
    end if;
	end if;

  -- following is the attributes we want the search for group entries to return
	v_attrs(1) := 'cn';
	v_attrs(2) := 'orclguid';
	v_attrs(3) := 'member';
  -- following is the user attributes we want the search for user entries to return
	v_user_attrs(1) := c_username_attribute;
	v_user_attrs(2) := 'orclguid';

  -- search LDAP for entries matching the criteria
	v_retval := DBMS_LDAP.search_s(v_session1, c_ldap_base,c_search_scope,
							  c_bootstrap_filter,
							  v_attrs,
							  0,
							  v_message);

	-- count the number of entries returned
	v_retval := DBMS_LDAP.count_entries(v_session1, v_message);
  if c_user_trace = true then 
    DBMS_OUTPUT.PUT_LINE(RPAD('TRACE: Number of Entries Found ',25,' ') || ': ' || TO_CHAR(v_retval));
  end if;
  
	DBMS_OUTPUT.PUT_LINE('===================================================');

	-- get the first entry
	v_entry := DBMS_LDAP.first_entry(v_session1, v_message);
	v_entry_index := 1;
	--- loop through each group entry and for each group do the following------
	while v_entry IS NOT NULL loop
    -- dynamically expand the array
		v_grplist.extend();
		v_grplist(v_grplist.count) := ListObject(null,null,'f','f','f');
    -- retrieve the attributes
		v_attr_name := DBMS_LDAP.first_attribute(v_session1,v_entry, v_ber_elmt);
		v_attr_index := 1;
    -- each attribute has some values
		while v_attr_name IS NOT NULL loop
      -- get the values associated with each attribute
			v_vals := DBMS_LDAP.get_values (v_session1, v_entry,v_attr_name);
			if v_vals.COUNT > 0 then
			FOR i in v_vals.FIRST..v_vals.LAST loop
      -- for a group entry, we use CN for group name and hopefully there is only 
      -- one value for it
			if v_attr_name = 'cn' and i = 0 then
				v_grplist(v_grplist.count).name := SUBSTR(v_vals(i),1,length(v_vals(i)));
        if c_user_trace = true then
          dbms_output.put_line ('TRACE: Group name '|| v_grplist(v_grplist.count).name);
        end if;
      -- if there are multiple values for CN, then ignore the rest  
			elsif v_attr_name = 'cn' and i <> 0 then
				 dbms_output.put_line('WARNING : multiple values found for '||v_attr_name||' considering the first one');
      -- there should only be one ORCLGUID per entry
			elsif v_attr_name = 'orclguid' and i = 0 then
				v_grplist(v_grplist.count).guid := SUBSTR(v_vals(i),1,length(v_vals(i)));
			elsif v_attr_name = 'orclguid' and i <> 0 then
				dbms_output.put_line('WARNING : multiple values found for '||v_attr_name||' considering the first one');
				-- we expect multiple values for member attr
			elsif v_attr_name = 'member' then
				v_member_ret_values := DBMS_LDAP.get_values(v_session1, v_entry,v_attr_name);
				v_count := DBMS_LDAP.count_values(v_member_ret_values);
				if v_count > 0 then
        -- loop through each user entry in the member list
				for j in v_member_ret_values.FIRST..v_member_ret_values.LAST loop
					--DBMS_OUTPUT.PUT_LINE ('Group Member # '|| j ||' = '|| v_member_ret_values(j));
					begin
            -- we have the DN of the user, no need to specify any additional filters
						v_users_filter := '(objectclass=*)';
						v_users_ldap_base := v_member_ret_values(j);
						v_users_message := null;
						begin
              -- retrieve the user entry
							v_users_retval := DBMS_LDAP.search_s(v_session2, v_users_ldap_base,
												  v_users_scope,v_users_filter,v_user_attrs,0,v_users_message);
						exception
						when others then
						DBMS_OUTPUT.PUT_LINE('WARNING : Search failed to find entry with dn='|| v_users_ldap_base ||', ignoring it');
						goto continue_loop;
						end;
						v_users_entry := DBMS_LDAP.first_entry(v_session2, v_users_message);
						-- strictly speaking, there should only be one entry because
						-- the member values are supposed to be dn's which are unique
						while v_users_entry IS NOT NULL loop
              -- get the user object
							v_userObject := LDAP_BICATALOG_SYNC_UTILS.getuserObject(v_session2,v_users_entry,c_username_attribute);
              -- dbms_output.put_line ('Username = '|| v_userObject.name);
							if v_userObject is not null then
                -- dynamically expand the user list array
								v_userlist.extend();
								v_userlist(v_userlist.count) := v_userObject;
							end if;
							v_users_entry := DBMS_LDAP.next_entry(v_session2, v_users_entry);
						end loop;  -- end while loop
					exception
					when others then
					-- there is a problem retrieving this user, ignore it
					DBMS_OUTPUT.PUT_LINE('WARNING : Error occured with dn ='|| v_users_ldap_base ||', Error = '|| SQLERRM || ', ignoring it');
					end;
				<<continue_loop>>
        -- release the message 
				v_retval := DBMS_LDAP.msgfree(v_users_message);
				end loop;  -- end for loop
				end if;
			end if;
			v_attr_name := DBMS_LDAP.next_attribute(v_session1,v_entry,v_ber_elmt);
			v_attr_index := v_attr_index+1;
			end loop;  -- end attribute loop
			end if;
		end loop; -- end outer attribute loop
	-- flush any elements in the userlist
	begin
	-- canAddThisGroup performs the following :
    -- 1. checks to see if this group already exists in the catalog
		-- 2. if it was originally imported from OID, it's OK if it exists and was imported from OID
    -- 3. if it was manually created, skip this over, log a message
	v_addthisgroup := LDAP_BICATALOG_SYNC_UTILS.canAddThisGroup(v_grplist(v_grplist.count).guid,v_grplist(v_grplist.count).name);
	if v_addthisgroup = true then
  -- if it can be added, go ahead and add it to the Catalog
  -- the specified groups will be created, users will be created and 
  -- association between the group and the users will be made in the catalog
  -- privileges if requested will be granted to the user and group 
	v_users_retval := LDAP_BICATALOG_SYNC_UTILS.addUsersToCatalog(v_userlist,v_grplist,c_permission,c_user_trace);
	end if;
	exception
	when others then
	null;
	end;
	v_userlist.delete;
	v_grplist.delete;
	-- go over to the next group, and repeat the same thing
	v_entry := DBMS_LDAP.next_entry(v_session1, v_entry);
	DBMS_OUTPUT.PUT_LINE('===================================================');
	v_entry_index := v_entry_index+1;
	end loop; -- end outer entry loop;
	-- free LDAP Message
	v_retval := DBMS_LDAP.msgfree(v_message);
	-- unbind from the directory
	v_retval := DBMS_LDAP.unbind_s(v_session1);
	v_retval := DBMS_LDAP.unbind_s(v_session2);
	DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');
	<<exit_script>>
	DBMS_OUTPUT.PUT_LINE('Script exiting');
	-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR: Error code    : ' || TO_CHAR(SQLCODE));
	DBMS_OUTPUT.PUT_LINE('ERROR Message : ' || SQLERRM);
END;
/


spool off;
set verify on
set trimspool on
set feedback on
set termout on
set define off

