Where does friendship with Active Directory begin

I often have to deal with the processing of information from Active Directory. Since this system is widespread in many places, I decided to share my experience with it.

It all started with the fact that I was distributing access to MS Sharepoint. In applications from employees, at best, accounts came in, at worst they could write something like “Me and my boss”. At first it bothered, and I, feeling like a real administrator, sent such applications back to careless users so that they redo everything. It turned into a heap of letters and calls saturated with anger. I even began to wonder if it was no coincidence that the abbreviation from Active Directory - AD coincides with the Russian word “Hell”. As a result, realizing that we need to change the world for the better, I decided to look in detail at what is stored in Active Directory and how it can be used to automatically distribute access and other useful things.

The company information about employees is stored in 2 places:

• Active Directory
• Oracle Database

Uniqueness is maintained in Oracle, and access is given through AD, i.e. There can be several accounts per employee. There was a need to uniquely identify the employee by his account.

The solution to this problem was found and replicated in many utilities and software products that are actively used within the company. One of the most used utilities is the employee information search program. She searches for an employee or employees according to various criteria and provides the most complete information about them, combining data from Active Directory and Oracle. Particularly popular are photographs of employees who are stored in a database.

It is also worth noting the integration of Active Directory not only with Oracle but also with MS Sharepoint. Thanks to the found solution, an application appeared that was able to quickly give access to a group of users from Active Directory on the pages of the Sharepoint portal, as well as remove users from it who, for various reasons, have left it.

The problem was solved in 2 ways:

1. For information on one employee - DBMS_LDAP in Oracle
2. In order to deflate all available records and see what is in Active Directory, we used C # code

When using C #, consider the following (System.DirectoryServices.dll library):

• The DirectorySearcher.PageSize property must be other than 0 if you want the FindAll () function to return all records.
• Be sure to call Dispose () on the DirectorySearcher instance.

There is also a DirectorySearcher.SizeLimit property that sets the maximum number of records in the returned result. By default, it is 0, which means - take this value from the server. The server usually has a value of 1000. You can play with the SizeLimit property and see how the result of the function ( link ) changes .

The link above has an example of code that uses yiled, which is more correct from the point of view of .NET, but I didn’t use it in the example below to illustrate the algorithm.

Code Examples:

• DBMS_LDAP Oracle
  

  function GetDataByUser(userAccount VARCHAR2)
  return VARCHAR2 is
  l_ldap_host VARCHAR2(256) := 'host';
  l_ldap_port VARCHAR2(256) := 'port';
  l_ldap_user VARCHAR2(256) := 'user';
  l_ldap_passwd VARCHAR2(256) := 'password';
  l_ldap_base VARCHAR2(256) := 'ldap_path';
  l_retval PLS_INTEGER;
  l_session DBMS_LDAP.session;
  l_attrs DBMS_LDAP.string_collection;
  l_message DBMS_LDAP.message;
  l_entry DBMS_LDAP.message;
  l_attr_name VARCHAR2(256);
  l_ber_element DBMS_LDAP.ber_element;
  l_vals DBMS_LDAP.string_collection;
  ret VARCHAR2(256);
  begin
     DBMS_LDAP.USE_EXCEPTION := TRUE;
     l_session := DBMS_LDAP.init(hostname => l_ldap_host,portnum => l_ldap_port);
     l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,dn => l_ldap_user,passwd => l_ldap_passwd);
     l_attrs(1) := 'postalcode';
     l_retval := DBMS_LDAP.search_s(ld => l_session,base => l_ldap_base,scope => DBMS_LDAP.SCOPE_SUBTREE,filter => '(&(objectClass=user)(cn=' ||userAccount || ')(mail=*))',attrs => l_attrs,attronly => 0,res => l_message);
     IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
        l_entry := DBMS_LDAP.first_entry(ld => l_session,msg => l_message);
        l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
        l_vals := DBMS_LDAP.get_values (ld => l_session, ldapentry => l_entry, attr => l_attr_name);
        ret := l_vals(0);
     END IF;
     l_retval := DBMS_LDAP.unbind_s(ld => l_session);
     return ret;
  end GetDataByUser; 
  

  
  
• FROM#
  
  

          static void Main(string[] args) 
          { 
              var listDict = ActiveDirectoryTraversal(); 
              var d = listDict[0]; 
          } 
          private static List> ActiveDirectoryTraversal() 
          { 
              List> ret = null; 
              var dep = new DirectoryEntry(); 
              dep.AuthenticationType = AuthenticationTypes.FastBind; 
              dep.Path = "LDAP://yourpath"; 
              using(DirectorySearcher ds = new DirectorySearcher(dep)) 
              { 
                  ds.Filter = "(&(objectClass=user))"; 
                 //ds.SizeLimit = 5; 
                  ds.PageSize = 100; 
                  using (SearchResultCollection results = ds.FindAll()) 
                  { 
                      var e = results.Count; 
                      if (results != null && results.Count > 0) 
                      { 
                        ret = SaveData(results); 
                      } 
                  } 
              } 
              dep.Close(); 
              return ret; 
          } 
          private static List> SaveData(SearchResultCollection results) 
          { 
              var ret = new List>(); 
              for (int i = 0; i < results.Count; i++) 
              { 
                  var res = results[i]; 
                  var dict = new Dictionary(); 
                  foreach (var e in res.Properties.PropertyNames) 
                  { 
                      if (!dict.ContainsKey(e.ToString())) 
                          dict.Add(e.ToString(), res.Properties[e.ToString()][0]); 
                  } 
                  ret.Add(dict); 
              } 
              return ret; 
          }
  

  
  

Conclusion:

The ability to work with Active Directory and access it directly from the Oracle database has greatly simplified the work of programmers in the department where I work. Initially, for the sake of experiment, I uploaded the contents of Active Directory into a database table. Then, to my surprise, I discovered a series of stored procedures that worked with this table. It turned out that the developers turned to her, too lazy to figure out the DBMS_LDAP package. This precedent pushed me to want to tell and give examples of how you can work with Active Directory.