How does friendship begin with Active Directory

I often have to deal with the processing of information from Active Directory. Since this system is widely distributed 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 the applications from employees, at best, the accounts came in, at worst they could write something like “Me and my boss”. At first, it strained, and I, feeling like a real admin, sent such requests back to careless users, so that they would redo everything. It turned into a pile of angry letters and calls. I even began to wonder if it was not by chance that the abbreviation from Active Directory - AD coincided with the Russian word “Hell”. In the end, realizing that we need to change the world for the better, I decided to sort out in detail what is generally stored in Active Directory and how it can be used to automatically distribute access and other useful things.

The company employee information is stored in 2 places:

• Active Directory
• Oracle DB

Uniqueness is maintained in Oracle, and access is given via AD, i.e. one employee may have multiple accounts. There was a need to uniquely identify the employee for his account.
')
The solution to this problem was found and replicated in many utilities and software products actively used within the company. One of the most widely used utilities is the employee information search program. It searches for an employee or employees by various criteria and provides the most complete information about them, combining data from Active Directory and Oracle. Particularly popular are photos of employees that 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 solution found, an application appeared that was able to quickly give access to a group of users from Active Directory to the pages of the Sharepoint portal, and also to remove from it users who, for various reasons, left it.

The problem was solved in 2 ways:

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

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

• The DirectorySearcher.PageSize property must be different from 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, which sets the maximum number of records in the returned result. By default, it is 0, which means to 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 changes ( link ).

The link above has an example of code that uses yiled, which is more correct from the point of view of .NET operation, but in the example below I did not use it, for clarity of the algorithm.

Code examples:

• Oracle DBMS_LDAP
  

  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; 

  
  
• WITH#
  
  

    static void Main(string[] args) { var listDict = ActiveDirectoryTraversal(); var d = listDict[0]; } private static List<Dictionary<string, object>> ActiveDirectoryTraversal() { List<Dictionary<string, object>> 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<Dictionary<string, object>> SaveData(SearchResultCollection results) { var ret = new List<Dictionary<string, object>>(); for (int i = 0; i < results.Count; i++) { var res = results[i]; var dict = new Dictionary<string, object>(); 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 the programmers of the department where I work. Initially, for the sake of experiment, I downloaded the contents of Active Directory into a database table. Then, to my surprise, I discovered a number of stored procedures that worked with this table. It turned out that the developers turned to it, too lazy to understand the package DBMS_LDAP. This precedent led me to the desire to tell and give examples of how to work with Active Directory.