8.3. Automating User Creation from a Third-Party Database

In some cases, you may need to integrate an OS X directory system with a third-party information system, such as an employee database. In an education environment, for example, tens or hundreds of thousands of student records exist, with new records being added every day. All of this user information exists largely in third-party databases, and you may need to ensure that records added to one system can easily be added to Open Directory.

To successfully import the data, you must get it into an acceptable format. Importing data into Open Directory can be performed from the GUI with Workgroup Manager, which supports import of data in a delimited format, with customizable attribute and record delimiters. This makes it a decently flexible tool for importing from third-party databases, as most of these systems can import into a delimited format of some sort, most commonly csv (comma separated values). Once you have a csv file in hand, you can import the file into the system. Consider a csv file with the following data structure:

shortname,fname,lname,pass
jdoe,John,Doe,JD09876
jsmith,Jane,Smith,JD09876

This is about as clean as csv data gets. To import the file, you can open up Workgroup Manager, connect to your OD master, and select Import from the Server menu. From there, you can map the attribute delimiter (,) and the record delimiter (Newline, hex value of 0x0A). If there is a problem with the import, you can check the dsimport log found at ~/Library/Logs/ImportExport/.

However, the premise of this section is to help automate this process, and having to manually specify delimiters each time you do an import isn't the cleanest way to work. Thus, we resort to the command line for automation. Importing data from the command line requires data to be formatted as a dsimport file, a colon-delimited format with an Open Directory-specific header:

0x0A 0x5C 0x2C 0x3A dsRecTypeStandard:Users 4 dsAttrTypeStandard:RecordName

dsAttrTypeStandard:FirstName dsAttrTypeStandard:LastName dsAttrTypeStandard:Password

This data consists of a number of values. In this code, the first value, 0x0A, denotes the record delimiter (a Unix newline ). The second value, 0x5C, specifies the escape character (a standard backslash ), which is used for escaping the attribute delimiter should its value actually be required for a field. For instance, importing computer data involves importing MAC addresses, which contain a colon. If your attribute delimiter is a colon, you must escape it with the character specified, so 00:50:56:c0:00:08 must be represented as 00:50:56:c0:00:08.

The next value in the header, 0x2C, is the attribute/field delimiter (in this case, a comma,). 0x3A then specifies the attribute value delimiter, used if a particular field/attribute contains more than one value. The next value, dsRecTypeStandard:Users, specifies the record type for import. You can use this tool to import Users, Groups, Computers, ComputerGroups or ComputerLists using those respective values. Next, you specify the number of columns in your import file (4), and the header for each column. The headers consist of dsAttributeStandard entries, which are records abstracted for use by the Directory Services API. These attributes do not correspond to LDAP attributes directly, but rather the abstracted field name. In this example, we are specifying four headers, RecordName (shortname), FirstName, LastName, and Password. A header of IGNORE can be set here to ignore the column of data. Once you specify headers, the rest of the file should consist of record and attribute data conforming to the specified delimiters.

If you have programmatic control over your information system, or if its export options are decently featured, you may be able to craft your own dsimport file. If not, you'll need to process your exports so that they can conform for import. To do so, you have a few options, and luckily one of them is included with this book. In the book's resource section, the csvtowgm Python script can be used to process your own csv-delimited outputs, and convert them to dsimport-compatible import files. The csvtowgm script accepts data from a csv file, or it can read it from the stdin data stream. Its options are best displayed through the program's help page:

csvToWGM: converts a csv delimited import file into a dsimport compatible file

Syntax:
    csvToWGM [-i infile] [-o outfile] [-f format] [-h headers]

Flags:
    -i infile          -- path to csv import file
    -o outfile       -- destination path for generated dsimport-compatible output file
    -d delimiter  -- The field delimeter used in your data, default ","
    -h headers    -- specify headers if they are not included in the first line of
                        import file. This should be a comma delimited string.
    -f format     -- Use specified config file. If none is specified, the format
                        "users" is assumed. Supported formats:
                            "users"
                            "computers"

In its basic usage, you can simply pass csvtowgm a user import file for processing and an output path to save the file:

csvtowgm -i ~/Documents/users.csv -o ~/Documents/users_dsimport.txt

csvtowgm will try its best to determine header information from the first line in your file. Optimally, headers will be identical to the dsAttrTypeStandard header. You can find the header resolution determined by csvtowgm by consulting its log file after running the import. The log file will contain an entry specifying the header resolution that was used by the import. Specifically, look for the line:

INFO Importing headers: '['realname', ' shortname', ' password']' as: '['RealName', 

 'RecordName', 'Password']'

Here we are using the csv data specified earlier in this section. We can see the imported headers: realname, shortname, and password, and see their respective mappings. If the system does not properly recognize a header, it will specify a header of IGNORE, and the data will not get imported. To process the file for import, you can specify your headers in comma-separated arguments passed to csvtowgm:

csvtowgm -i ~/Documents/users.csv -o ~/Documents/users_dsimport.txt -h

 RealName,RecordName,Password

This will process the file using fixed headers. You can also use csvtowgm to process computer records. Consider the following computer data file:

name,ethernetid
hax.lbc,00:50:56:c0:00:08
helyx.lbc,00:50:56:c0:00:01

To import this file, the process is identical to that for users, though we instead specify the computer format using the -f flag:

csvtowgm -i ~/Documents/machines.csv -o ~/Documents/machines_dsimport.txt -f computers

In some cases, you may need some more advanced features, such as field-data generators or other data-population mechanisms. Currently, csvtowgm simply does basic file conversion and has only basic facilities to generate data. If data generation is a need of yours, consider using Passenger (http://macinmind.com), a GUI utility for generating dsimport-compatible files.

Once you have data in a dsimport-compatible file, you must import that data into your system. You can do this via the dsimport command, which is fairly straightforward and has three mandatory parameters: filepath, nodepath (the target Directory node for import /LDAPv3/odserver.myco.com), and the conflict resolution options. A conflict occurs when an existing record contains a record name, UID, or GID that's the same as a new record to be imported. There are five different values that can be provided for conflict resolution:

• O: Overwrite an existing record if it contains a matching record name, UID, or GID.

• M: Merge data with any existing records. Create the record if it does not exist.

• I: Ignore the new record if there is a conflict.

• A: Append data to an existing record; do not create a new record if it does not exist

• N: Skip conflict detection. This option can be slow and problematic.

So, to import a dsimport file into an Open Directory system, use the following command:

dsimport ~/Documents/users_dsimport.txt /LDAPv3/odserver.myco.com M --userpreset "Staff 

Member" --username diradmin --password 'mydiradmin password'

In this example, we are importing a user file into our LDAP directory using a merge. Here, we specify the -userpreset flag, which allows us to set up a preset in Workgroup manager to establish group memberships, home directory settings, and mail and print settings. (You can create presets using Workgroup Manager.) In this command, we also supply our diradmin credentials to provide access for the import itself, presenting one of the biggest barriers to full automation for this process: any automated processes will need to have a directory administrator's credentials embedded.

However, embedding administrator credentials in a script to automate import from a csv is not the only issue with a fully automated user-generation process. For instance, say you set up a launchdaemon with a QueueDirectory entry, which watches a directory for any new files from your information system, passes them to csvtowgm, and then uses the resulting file for import via dsimport. Such a script might look something like this:

#!/bin/bash

PATH=/bin;/usr/bin;/usr/local/bin

## set our variables
declare -x watchFolder="/Library/dsimportwatchdir"
declare -x dirNode="/LDAPv3/odserver.myco.com"

## create our folder
mkdir -p "$watchFolder" &> /dev/null

## loop through all of the csv files in our watch folder, format them,
## import them, delete the formatted versions, and copy the original
## into an archive directory.
for file in $(ls −1 "$watchFolder" | grep ".csv"); do
        declare -x tempFile=$(mktemp /tmp/dsimport_XXXXX)
        cat "$watchFolder/$file" | csvtowgm -o "$tempFile"
        csvtowgmResultCode=$?
        if [ $csvtowgmResultCode == 0 ]; then
                dsimport "$tempFile" "$dirNode" M -username importadmin -password 'importpassword'
               rm "$tempFile"
               mkdir "$watchFolder/archive/" &> /dev/null
               mv "$watchFolder/$file" "$watchFolder/archive/"
        else
               echo "Error generating import file! error num: $csvtowgmResultCode"
               exit $csvtowgmResultCode
        fi
done

This code would get the job done, but it does present numerous concerns. First and foremost, we are trusting the security of our user base to the contents of this folder. By using a merge import, it would certainly be possible for a file to be dropped into our watchfolder that completely trashes our directory, potentially overriding data for admin accounts or simply generating accounts for itself.

Due to concerns such as these, the exact level of desired automation will greatly vary from environment to environment and will depend on the sensitivity of the data housed by the system and the security requirements set forth by the organization. A fully automated import process such as this is not advisable in any environment where security is a concern. However, even if the final dsimport is a manual step, simply by generating dsimport-style import files you are greatly reducing the possibility for human error, streamlining the import process, and ensuring more consistent results.