\label{Institutional_Integration_Identity_Management}
Two subroutines exist in localenroll.pm to provide a connection between
institutional directory data (e.g., user information from LDAP) and
LON-CAPA. The first is \emph{get\_userinfo()} which can operate in
two modes.: (a) it can be used to provide first name, last name, e-mail
address, student/employee ID etc., for a specified username, e.g.,
for a new user being created in LON-CAPA, and (b) it can be used to
retrieve user information for multiple users from an institutional
directory searches when (for example) a course coordinator is adding
a new user directly to a course. At MSU the routine which actually
queries institutional data sources is itself called by \emph{get\_userinfo()}.
This was done so that the same underlying routine can also be used
by the second of the two subroutines: \emph{allusers\_info()} which
is called by Autoupdate.pl (a script which can be run periodically
to reconcile user information in LON-CAPA with institutional directory
data for all users).



\textbf{\large get\_userinfo}{\large \par}

Four required arguments and additional optional arguments

Two modes of operation:

\begin{enumerate}
\item Retrieves institutional data for a single user either by username,
if \$uname is included as second argument, 


or by ID if \$id is included as a third argument. Either second or
third arguments must be provided; seventh, eighth and ninth args will
be undefined.

\item Retrieves institutional user data from search of an institutional
directory based on a search. seventh and eighth args are required;
ninth is optional. second and third will be undefined.
\end{enumerate}
Arguments:

\begin{enumerate}
\item \$dom - domain
\item \$uname - username of user
\item \$id - student/faculty ID of user
\item \$instusers - reference to hash which will contain info for user as
key = value; keys will be one or all of: lastname, firstname, middlename,
generation, id, inststatus - institutional status (e.g., faculty,staff,student). 


Values are all scalars except inststatus, which is an array.

\item \$instids - reference to hash which will contain ID numbers - keys
will be unique IDs (student or faculty/staff ID) 


values will be either: scalar (username) or an array if a single ID
matches multiple usernames.

\item \$types - optional reference to array which contains institutional
types to check.
\item \$srchby - optional if \$uname or \$id defined, otherwise required.


Allowed values include: 1. lastfirst, 2. last, 3. uname corresponding
to searches by 1. lastname,firstname; 2. lastname; 3. username

\item \$srchterm - optional if \$uname or \$id defined, otherwise required
- String to search for.
\item \$srchtype - optional. Allowed values: contains, begins (defaults
to exact match otherwise).
\end{enumerate}
Returns 'ok' if no error occurred. Side effects - populates the \$instusers
and \$instids refs to hashes with information for specified username,
or specified id, if fifth argument provided, from all available, or
specified (e.g., faculty only) institutional datafeeds, if sixth argument
provided.

At MSU six separate MS-SQL database tables are queried, with each
table corresponding to a specific institutional type. A routine is
called to connect to the database. and the actual queries are handled
by a separate routine - \emph{query\_user\_tables()}.

\begin{quotation}
\texttt{sub get\_userinfo \{}
\begin{quotation}
\texttt{my (\$dom,\$uname,\$id,\$instusers,\$instids,\$types,}
\begin{quotation}
\texttt{\$srchby,\$srchterm,\$srchtype) = @\_;}
\end{quotation}
\texttt{my \$outcome;}
\begin{quotation}
\texttt{my @srchtables;}
\end{quotation}
\texttt{my \%tables = (}
\begin{quotation}
\texttt{Faculty => 'FACULTY\_VU',}

\texttt{Staff => 'STAFF\_VU',}

\texttt{Student => 'STUDENT',}

\texttt{Assistant => 'ASSISTANT',}

\texttt{StaffAff => 'AFFILIATE',}

\texttt{StuAff => 'STUDENT\_AFFILIATE'}
\end{quotation}
\texttt{);}

\texttt{my (\$dbh,\$dbflag) = \&connect\_DB('HR');}

\texttt{foreach my \$type (@\{\$types\}) \{}
\begin{quotation}
\texttt{if (exists(\$tables\{\$type\})) \{}
\begin{quotation}
\texttt{push(@srchtables,\$tables\{\$type\});}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (@srchtables == 0) \{}
\begin{quotation}
\texttt{foreach my \$type (keys(\%tables)) \{}
\begin{quotation}
\texttt{push(@srchtables,\$tables\{\$type\});}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (\$srchby eq '' \&\& \$srchterm eq '') \{}
\begin{quotation}
\texttt{if (\$uname ne '') \{}
\begin{quotation}
\texttt{\$srchby = 'uname';}

\texttt{\$srchterm = \$uname;}
\end{quotation}
\texttt{\} elsif (\$id ne '') \{}
\begin{quotation}
\texttt{\$srchby = 'id';}

\texttt{\$srchterm = \$id;}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (\$srchterm ne '') \{}
\begin{quotation}
\texttt{\$outcome = \&query\_user\_tables(\$dbflag,\$dbh,\textbackslash{}@srchtables,}
\begin{quotation}
\texttt{~~~~~~~\$instusers,\$instids,\$srchby,\$srchterm,\$srchtype,\$types);}
\end{quotation}
\end{quotation}
\texttt{\}}

\texttt{if (\$dbflag) \{}
\begin{quotation}
\texttt{\&disconnect\_DB(\$dbh);}
\end{quotation}
\texttt{\}}

\texttt{return \$outcome;}
\end{quotation}
\texttt{\}}

Although query\_user\_tables() is not a subroutine included as a stub
in the standard localenroll.pm, it is included below to show how the
database queries are implemented at MSU.

\texttt{sub query\_user\_tables \{}
\begin{quotation}
\texttt{my (\$dbflag,\$dbh,\$srchtables,\$instusers,\$instids,\$srchby,\$srchterm,}
\texttt{~~~~\$srchtype,\$types) = @\_;}

\texttt{my (\$outcome,\$condition,\%multipids,\$ldapfilter);}

\texttt{if (\$srchby eq 'uname') \{}
\begin{quotation}
\texttt{if (\$srchterm =\~{} /\^{}\textbackslash{}w\{2,8\}\$/)
\{}
\begin{quotation}
\texttt{if (\$srchtype eq 'contains') \{}

\texttt{~~\$condition = \char`\"{}WHERE MSUNetID LIKE '\%\$srchterm\%'\char`\"{};}

\texttt{~~\$ldapfilter = '(uid={*}'.\$srchterm.'{*})';}

\texttt{\} elsif (\$srchtype eq 'begins') \{}

\texttt{~~\$condition = \char`\"{}WHERE MSUNetID LIKE '\$srchterm\%'\char`\"{};}

\texttt{~~\$ldapfilter = '(uid='.\$srchterm.'{*})';}

\texttt{\} else \{}

\texttt{~~\$condition = \char`\"{}WHERE MSUNetID = '\$srchterm'\char`\"{};}

\texttt{~~\$ldapfilter = '(uid='.\$srchterm.')';}

\texttt{\}}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\} elsif (\$srchby eq 'lastname') \{}
\begin{quotation}
\texttt{if (\$srchterm =\~{} /{[}A-Za-z\textbackslash{}-\textbackslash{}.'\textbackslash{}s]+/)
\{}
\begin{quotation}
\texttt{if (\$srchtype eq 'contains') \{}

\texttt{~~if (\$dbflag) \{}

\texttt{~~~~my \$quoted\_last = \$dbh->quote('\%'.\$srchterm.'\%');}

\texttt{~~~~\$condition = \char`\"{}WHERE LastName LIKE \$quoted\_last\char`\"{};}

\texttt{~~\}}

\texttt{~~\$ldapfilter = '(sn={*}'.\$srchterm.'{*})';}

\texttt{\} elsif (\$srchtype eq 'begins') \{}

\texttt{~~if (\$dbflag) \{}

\texttt{~~~~my \$quoted\_last = \$dbh->quote(\$srchterm.'\%');}

\texttt{~~~~\$condition = \char`\"{}WHERE LastName LIKE \$quoted\_last\char`\"{};}

\texttt{~~\}}

\texttt{~~\$ldapfilter = '(sn='.\$srchterm.'{*})';}

\texttt{\} else \{}

\texttt{~~if (\$dbflag) \{}

\texttt{~~~my \$quoted\_last = \$dbh->quote(\$srchterm);}

\texttt{~~~~\$condition = \char`\"{}WHERE LastName = \$quoted\_last\char`\"{};}

\texttt{~~\}}

\texttt{~~\$ldapfilter = '(sn='.\$srchterm.')';}

\texttt{\}}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\} elsif (\$srchby eq 'lastfirst') \{}
\begin{quotation}
\texttt{my (\$srchlast,\$srchfirst) = split(/,/,\$srchterm);}

\texttt{\$srchlast =\~{} s/\textbackslash{}s+\$//;}

\texttt{\$srchfirst =\~{} s/\^{}\textbackslash{}s+//;}

\texttt{if ((\$srchlast =\~{} /{[}A-Za-z\textbackslash{}-\textbackslash{}.'\textbackslash{}s]+/)
\&\& (\$srchfirst =\~{} /{[}A-Za-z\textbackslash{}-\textbackslash{}.'\textbackslash{}s]+/))
\{}
\begin{quotation}
\texttt{my (\$quoted\_first,\$quoted\_last);}

\texttt{if (\$srchtype eq 'contains') \{}

\texttt{~~if (\$dbflag) \{}

\texttt{~~~~\$quoted\_last = \$dbh->quote('\%'.\$srchlast.'\%');}

\texttt{~~~~\$quoted\_first = \$dbh->quote('\%'.\$srchfirst.'\%');}

\texttt{~~~~\$condition = \char`\"{}WHERE ( LastName LIKE \$quoted\_last
AND FirstName LIKE \$quoted\_first )\char`\"{};}

\texttt{~~\}}

\texttt{~~\$ldapfilter = '(\&(sn='.\$srchlast.'{*})(givenName='.\$srchfirst.'{*}))';}

\texttt{\} else \{}
\begin{quotation}
\texttt{foreach my \$table (@\{\$srchtables\}) \{}
\begin{quotation}
\texttt{next if (\$srchby \&\& \$condition eq '');}

\texttt{my \$statement = \char`\"{}SELECT MSUNetID,Pid,FirstName,LastName,}
\texttt{~~~~~~~~~~~~~~~~~Person\_Type FROM \$table \$condition\char`\"{};}

\texttt{my \$sth = \$dbh->prepare(\char`\"{}\$statement\char`\"{});}

\texttt{\$sth->execute();}

\texttt{while ( my(\$uname,\$pid,\$first,\$last,\$type) = \$sth->fetchrow\_array
) \{}

\texttt{~~\$pid=lc(\$pid);}

\texttt{~~if (ref(\$instusers->\{\$uname\}) eq 'HASH') \{}

\texttt{~~~~if (ref(\$instusers->\{\$uname\}\{'instst}
\end{quotation}
\end{quotation}
\texttt{~~if (\$dbflag) \{}

\texttt{~~~~\$quoted\_last = \$dbh->quote(\$srchterm);}

\texttt{~~~~\$quoted\_first = \$dbh->quote(\$srchterm);}

\texttt{~~~~\$condition = \char`\"{}WHERE ( LastName = \$quoted\_last
AND FirstName = \$quoted\_first )\char`\"{};}

\texttt{~~\}}

\texttt{~~\$ldapfilter = '(\&(sn='.\$srchlast.')(givenName='.\$srchfirst.'))';}

\texttt{\}}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\} elsif (\$srchby eq 'id') \{}
\begin{quotation}
\texttt{if (\$dbflag) \{}

\texttt{~~if (\$srchterm =\~{} /\^{}{[}AZ]\textbackslash{}d\{8\}\$/)
\{}
\begin{quotation}
\texttt{~~\$condition = \char`\"{}WHERE Pid = '\$srchterm'\char`\"{};}
\end{quotation}
\texttt{~~\}}

\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (\$dbflag) \{}
\begin{quotation}
\texttt{foreach my \$table (@\{\$srchtables\}) \{}
\begin{quotation}
\texttt{next if (\$srchby \&\& \$condition eq '');}

\texttt{my \$statement = \char`\"{}SELECT MSUNetID,Pid,FirstName,LastName,Person\_Type
FROM \$table \$condition\char`\"{};}

\texttt{my \$sth = \$dbh->prepare(\char`\"{}\$statement\char`\"{});}

\texttt{\$sth->execute();}

\texttt{while ( my(\$uname,\$pid,\$first,\$last,\$type) = \$sth->fetchrow\_array
) \{}

\texttt{~~\$pid=lc(\$pid);}

\texttt{~~if (ref(\$instusers->\{\$uname\}) eq 'HASH') \{}

\texttt{~~~~if (ref(\$instusers->\{\$uname\}\{'inststatus'\})
eq 'ARRAY') \{}

\texttt{~~~~~~if (!grep(/\^{}\$type\$/,@\{\$instusers->\{\$uname\}\{'inststatus'\}\}))
\{}

\texttt{~~~~~~~~push(@\{\$instusers->\{\$uname\}\{'inststatus'\}\},\$type);}

\texttt{~~~~~~\}}

\texttt{~~~~\}}

\texttt{~~~~if (\$pid ne \$instusers->\{\$uname\}\{'id'\}) \{}

\texttt{~~~~~~if (\$instusers->\{\$uname\}\{'id'\} =\~{}
/\^{}A\textbackslash{}d\{8\}\$/) \{}

\texttt{~~~~~~~~if (\$pid =\~{} /\^{}A\textbackslash{}d\{8\}\$/)
\{}

\texttt{~~~~~~~~~~if (ref(\$multipids\{\$uname\}) eq 'ARRAY')
\{}

\texttt{~~~~~~~~~~~~if (!grep(/\^{}\$pid\$/,@\{\$multipids\{\$uname\}\}))
\{}

\texttt{~~~~~~~~~~~~~~push(@\{\$multipids\{\$uname\}\},\$pid);}

\texttt{~~~~~~~~~~~~\}}

\texttt{~~~~~~~~~~\} else \{}

\texttt{~~~~~~~~~~~~@\{\$multipids\{\$uname\}\}=(\$instusers->\{\$uname\}\{'id'\},\$pid);}

\texttt{~~~~~~~~~~\}}

\texttt{~~~~~~~~~~\$instusers->\{\$uname\}\{'id'\} = \$pid;}

\texttt{~~~~~~~~\}}

\texttt{~~~~~~\} elsif (\$instusers->\{\$uname\}\{'id'\} =\~{}
/\^{}Z\textbackslash{}d\{8\}\$/) \{}

\texttt{~~~~~~~~if (\$pid =\~{} /\^{}Z\textbackslash{}d\{8\}\$/)
\{}

\texttt{~~~~~~~~~~if (ref(\$multipids\{\$uname\}) eq 'ARRAY')
\{}

\texttt{~~~~~~~~~~~~if (!grep(/\^{}\$pid\$/,@\{\$multipids\{\$uname\}\}))
\{}

\texttt{~~~~~~~~~~~~~~push(@\{\$multipids\{\$uname\}\},\$pid);}

\texttt{~~~~~~~~~~~~\}}

\texttt{~~~~~~~~~~\} else \{}

\texttt{~~~~~~~~~~~~@\{\$multipids\{\$uname\}\}=(\$instusers->\{\$uname\}\{'id'\},\$pid);}

\texttt{~~~~~~~~~~\}}

\texttt{~~~~~~~~\} elsif (\$pid =\~{} /\^{}A\textbackslash{}d\{8\}\$/)
\{}

\texttt{~~~~~~~~~~\$instusers->\{\$uname\}\{'id'\} = \$pid;}

\texttt{~~~~~~~~\}}

\texttt{~~~~~~\}}

\texttt{~~~~\}}

\texttt{~~\} else \{}

\texttt{~~~~\$instusers->\{\$uname\} = \{}

\texttt{~~~~~~firstname => \$first,}

\texttt{~~~~~~lastname => \$last,}

\texttt{~~~~~~id => \$pid,}

\texttt{~~~~~~permanentemail => \$uname.'@msu.edu', }

\texttt{~~~~~~inststatus => {[}\$type],}

\texttt{~~~~\};}

\texttt{~~\}}

\texttt{~~if (defined(\$instids->\{\$pid\})) \{}

\texttt{~~~~if (ref(\$instids->\{\$pid\}) eq 'ARRAY') \{}

\texttt{~~~~~~if (!grep(/\^{}\$uname\$/,@\{\$instids->\{\$pid\}\}))
\{}

\texttt{~~~~~~~~push(@\{\$instids->\{\$pid\}\},\$uname);}

\texttt{~~~~~~\}}

\texttt{~~~~\} elsif (\$instids->\{\$pid\} ne \$uname) \{}

\texttt{~~~~~~@\{\$instids->\{\$pid\}\} = (\$instids->\{\$pid\},\$uname);}

\texttt{~~~~\}}

\texttt{~~\} else \{}

\texttt{~~~~\$instids->\{\$pid\} = \$uname;}

\texttt{~~\}}

\texttt{\}}

\texttt{\$outcome = 'ok';}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (\$ldapfilter ne '') \{}
\begin{quotation}
\texttt{my \$ldapres = \&ldap\_search(\$ldapfilter,\$instusers,\$types);}

\texttt{if (!\$dbflag) \{}
\begin{quotation}
\texttt{\$outcome = \$ldapres;}
\end{quotation}
\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{return \$outcome;}
\end{quotation}
\texttt{\}}
\end{quotation}
At MSU, a search of the LDAP directory is used to supplement SQL queries
of Faculty, Staff and Student database tables, because there are no
student/employee IDs available from MSU's LDAP service. The LDAP search
is used to retrieve information about users who have MSUNetIDs (i.e.,
official usernames from MSU), but are not currently affiliated with
any of the institutional user types, so are absent from the six SQL
database tables.

\begin{quotation}
\texttt{sub ldap\_search \{}
\begin{quotation}
\texttt{my (\$ldapfilter,\$instusers,\$types) = @\_;}

\texttt{my \$outcome;}

\texttt{my \$ldap = Net::LDAP->new( 'ldap.msu.edu' );}

\texttt{if (\$ldap) \{}
\begin{quotation}
\texttt{\$ldap->bind;}

\texttt{my \$mesg = \$ldap->search(}
\begin{quotation}
\texttt{base => \char`\"{}dc=msu, dc=edu\char`\"{},}

\texttt{filter => \$ldapfilter,}

\texttt{attrs => {[}'sn','givenName','title','uid','mail','employeeType'],}
\end{quotation}
\texttt{);}

\texttt{if (\$mesg->code) \{}
\begin{quotation}
\texttt{\$ldap->unbind;}

\texttt{return;}
\end{quotation}
\texttt{\} else \{}
\begin{quotation}
\texttt{\$outcome = 'ok';}
\end{quotation}
\texttt{\}}

\texttt{foreach my \$entry (\$mesg->entries) \{}
\begin{quotation}
\texttt{my \$uname = \$entry->get\_value('uid');}

\texttt{next if (\$uname eq '');}

\texttt{my \$first = \$entry->get\_value('givenName');}

\texttt{my \$last = \$entry->get\_value('sn');}

\texttt{my \$email = \$entry->get\_value('mail');}

\texttt{my \$type;}

\texttt{if ((\$entry->get\_value('employeeType') eq 'Faculty') ||
(\$entry->get\_value('employeeType') eq 'Staff')) \{}
\begin{quotation}
\texttt{\$type = \$entry->get\_value('employeeType');}
\end{quotation}
\texttt{\} elsif (\$entry->get\_value('title') eq 'Student') \{}
\begin{quotation}
\texttt{\$type = \$entry->get\_value('title');}
\end{quotation}
\texttt{\}}

\texttt{if (ref(\$types) eq 'ARRAY') \{}

\texttt{~~if (@\{\$types\} > 0) \{}
\begin{quotation}
\texttt{~~if ((\$type ne '') \&\& !(grep(/\^{}\$type\$/,@\{\$types\}))) }

\texttt{~~~~next if (!grep(/\^{}default\$/,@\{\$types\}));}

\texttt{~~\}}

\texttt{~~next if ((\$type eq '') \&\& (!grep(/\^{}default\$/,@\{\$types\})));}

\texttt{\}}
\end{quotation}
\texttt{\}}

\texttt{if (ref(\$instusers->\{\$uname\}) eq 'HASH') \{}

\texttt{~~if (ref(\$instusers->\{\$uname\}\{'inststatus'\}) eq 'ARRAY')
\{}

\texttt{~~~~if (!grep(/\^{}\$type\$/,@\{\$instusers->\{\$uname\}\{'inststatus'\}\}))
\{}

\texttt{~~~~~~push(@\{\$instusers->\{\$uname\}\{'inststatus'\}\},\$type);}

\texttt{~~~~\}}

\texttt{~~\}}

\texttt{\} else \{}

\texttt{~~\$instusers->\{\$uname\} = \{}

\texttt{~~firstname => \$first,}

\texttt{~~lastname => \$last,}

\texttt{~~id => '',}

\texttt{~~permanentemail => \$email,}

\texttt{~~inststatus => {[}\$type],}

\texttt{\};}
\end{quotation}
\texttt{\}}

\texttt{\$ldap->unbind;}
\end{quotation}
\texttt{\}}

\texttt{return \$outcome;}
\end{quotation}
\texttt{\}}
\end{quotation}
\textbf{\large allusers\_info}{\large \par}

Three arguments are required:

\begin{enumerate}
\item \$dom - domain
\item \$instusers - reference to hash which will contain hashes, where keys
will be usernames and value will be a hash of user information. 


Keys in the inner hash will be some or all of: lastname, firstname,
middlename, generation, id, inststatus - institutional status (e.g.,
faculty,staff,student)

Values are all scalars except inststatus, which is an array.

\item \$instids - reference to hash which will contain ID numbers. keys
will be unique IDs (student or faculty/staff ID).


Values will be either: scalar (username) or an array if a single ID
matches multiple usernames.

\end{enumerate}
Returns 'ok' if no error occurred.

Side effects - populates the \$instusers and \$instids refs to hashes
with information for all users from all available institutional datafeeds.

In the MSU case, six SQL database tables are queried via the \emph{query\_user\_tables()}
routine described above. 

\begin{quotation}
\texttt{sub allusers\_info \{}
\begin{quotation}
\texttt{my (\$dom,\$instusers,\$instids) = @\_;}

\texttt{my \$outcome;}

\texttt{my (\$dbh,\$dbflag) = \&connect\_DB('HR');}

\texttt{if (\$dbflag) \{}
\begin{quotation}
\texttt{my @srchtables = ('FACULTY\_VU','STAFF\_VU','STUDENT','AFFILIATE',}
\texttt{~~~~~~~~~~~~~~~~~~'ASSISTANT','STUDENT\_AFFILIATE');}

\texttt{\&query\_user\_tables(\$dbflag,\$dbh,\textbackslash{}@srchtables,\$instusers,\$instids);}

\texttt{\$outcome = 'ok';}

\texttt{\&disconnect\_DB(\$dbh);}
\end{quotation}
\texttt{\}}

\texttt{return \$outcome;}
\end{quotation}
\texttt{\}}
\end{quotation}