Ad Hoc Changes to EDIR accounts
Original author: Mark Anderson - 20091029
Limitations in the UI Interface Necessitating Alternate Editing Methods
We cannot make all necessary additions, deletions and updates to EDIR/AUTHSERV data through the existing web interfaces. This document describes one way to make the small-scale changes which are the most common case. Although simple to carry out, these changes must be performed with care to ensure that the updated data is accurate. Since the changes are easy to make, it is also easy to make them carelessly.
It is also important to ensure that the updated data is organized consistently with the other data in EDIR/AUTHSERV. Otherwise, as inconsistent "edge cases" proliferate in the data it becomes increasingly difficult to carry out large-scale changes to, or queries of, the data.
Changes must (almost always) be made to both the LDAP server Directory and the Oracle server Registry. If changes are made only to the Directory, then they may be erased by the next Banner refresh which pushes the contents of the Registry out to the Directory. If changes are made only to the Registry, they will not be visible in EDIR/AUTHSERV. The only exceptions to this rule are the attributes which are not stored in the Registry but only in the Directory: userPassword, uakSecQuestion and uakSecResponse
The following procedure is just one way to make these updates. The artifacts of this procedure -- the .ldif and .sql files -- are not of permanent value but are useful to keep around for a short time so that if you made an error (for example, updated the Directory but not the Registry) you can consult the files to isolate that error. They can also serve as templates for similar updates later.
EDIR LDAP Updates
To update the Directory, log in to any of the LDAP directory hosts as iplanet and submit an LDIF script to the appropriate Directory instance. How to do this is described in detail in this section.
Create an LDIF
First, create an LDIF file consisting of four basic lines (described in detail below) and optional comment lines preceded by #:
% cat phmccarthy_addEduPersonAffiliation.ldif # Paul McCarthy - emeritus dn: uid=FR464UKX01,ou=people,dc=alaska,dc=edu changetype: modify add: eduPersonAffiliation eduPersonAffiliation: Member %
By convention the file is named after the directory entry followed by the change, e.g.
- phmccarthy_addEduPersonAffiliation.ldif
- elaineVroman_changeDefaultPassword.ldif
- syfnd_addVanityAddress.ldif
Line 1: Distinguished Name (dn)
The first line of the LDIF will contain the unique Distinguished Name (dn) of the entry you intend to update. Usually the entry will be for a People or Resource account, although occasionally the DN may be in the Departments or Routing OU of the directory.
Distinguished Name - ou=people
dn: uid=FL0Z2N9R01,ou=people,dc=alaska,dc=edu dn: uid=GOOGLEAPPS03,ou=resource,dc=alaska,dc=edu
In any case, you can get the dn by querying the Directory for the entry. The DN will always be the first non-comment line returned:
% ldap_queryProd "(cn=lawrence duffy)" dn # SSL connection to port 17630 dn: uid=FL0Z2N9R01,ou=people,dc=alaska,dc=edu
Distinguished Name - ou=resource
% ldap_queryProd "(uid=GOOGLEAPPS03)" dn # SSL connection to port 17630 dn: uid=GOOGLEAPPS03,ou=resource,dc=alaska,dc=edu
Distinguished Name - ou=routing
% ldap_queryProd "(mailRoutingAddress=syfnd@email.alaska.edu)" dn # SSL connection to port 17630 dn: uid=46506,ou=routing,dc=alaska,dc=edu
The examples above query the Prod Directory; you would use ldap_queryPrep or ldap_queryTest for the Prep or Test Directories. The arguments to the scripts are the search filter and a space-delimited list of attributes you wish the query to return. If you omit the list, all attributes will be returned.
Line 2: LDIF Modification Entry
The second line of the LDIF will, for purposes of this document, always be
- changetype: modify
Line 3: LDIF Change Type
The third line of the LDIF may be an "add:", "change:" or "delete:" of an attribute. However, best practice for changing an attribute's value is to delete it and then add it (details on this later). For example:
add: edupersonAffiliation or delete: defaultPassword
Line 4: Change Value
The fourth line of the LDIF will be the value of the attribute named in the third line, for example:
edupersonAffiliation: sponsored or defaultPassword: 3562041167
Creating LDIF Files for Multiple Changes
If you want to make several changes to the same entry in the same LDIF, you can repeat the third and fourth lines separated by "-" on a line by itself. For example, to modify an attribute:
delete: edupersonAffiliation edupersonAffiliation: sponsored - add: edupersonAffiliation edupersonAffiliation: member
Apply LDIF to EDIR LDAP
To apply the LDIF to the directory, use the ldap_modifyProd (or ldap_modifyTest or ldap_modifyPrep, as appropriate) script as iplanet:
% ldap_modifyProd < phmccarthy_addEduPersonAffiliation.ldif (blah blah blah) modify complete %
The change you make will be automatically replicated to all the other LDAP servers in the Test, Prep or Prod EDIR instance.
People Registry Updates
To update the Registry, connect to the appropriate Registry database (RPTS or RPTP for Prod, RPTQ for Prep, RPTT for Test) as ops$sxldap and submit an SQL script.
The steps to perform an update are
- Log in to target database, RPTP (Prod), RPTT (Test), or RPTQ (Prep)
- Shell to user 'sxldap'
- Create SQL update file
- Log in to People Registry Schema
- Run query from SQLPLUS command line
Login to RPTP and Shell to 'sxldap'
From your desktop login to RPTP.
[Your desktop]: /Users/<desktop user name> > ssh <sx ID>@rptp.alaska.edu Password: Last login: Tue Dec 23 13:22:05 2014 from <desktop user name>.swits.alaska.edu <sx ID>@tazlina> ssh sxldap@tazlina.alaska.edu Last login: Tue Dec 23 13:41:05 2014 from tazlina.alaska.edu sxldap@tazlina>
Create a SQL File to Add an Attribute
Create an SQL script like the one below, by convention named exactly like the corresponding LDIF file but with a ".sql" extension. The following example adds an attribute.
% cat phmccarthy_addEduPersonAffiliation.sql -- add eduPersonAffiliation: Member insert into LDAP_ATTR_SS ( LUID, OU, PIDM, ATTRIB_NAME, ATTRIB_VALUE, ATTRIB_CHANGE_DATE, ATTRIB_EXTRACT_DATE, CHANGED_BY ) values ( 'FR464UKX01', 'people', -1, 'eduPersonAffiliation', 'Member', sysdate, NULL, 'uid=XKK63NT8Z01,ou=people,dc=alaska,dc=edu' ); %
The "insert into" clause of the SQL enumerates the columns of the OPS$SXLDAP.LDAP_ATTR_SS table and the "values" clause lists the row of values that will be inserted into those columns.
It is worth noting here that the Registry, unlike the Directory, separates data that flows into EDIR/AUTHSERV from Banner from data that flows into EDIR/AUTHSERV from other sources. Ad hoc updates should only be made to tables with the latter kind of data (like LDAP_ATTR_SS); if you update tables of Banner data, your updates will be overwritten by the daily Banner refresh.
The values for a row of the LDAP_ATTR_SS table are:
LUID - "uid" attribute from the dn: line on your LDIF file OU - "ou" attribute from the dn: line on your LDIF file PIDM - always -1 in non-Banner-data tables ATTRIB_NAME - attribute name from the LDIF file "add:" line ATTRIB_VALUE - attribute value from the LDIF file ATTRIB_CHANGE_DATE - current date-time; the SYSDATE funtion will return this ATTRIB_EXTRACT_DATE - always null in non-Banner-data tables CHANGED_BY - your EDIR uid
Create a SQL File to Delete an Attribute
Create an SQL script like this one, by convention named exactly like the corresponding LDIF file but with a ".sql" extension. The following examples are for TEST.
% cat codyJohnson_removeVanityAddress.sql -- Cody Johnson, Post Doctoral Researcher -- delete mailAlternateAddress: cody.johnson@alaska.edu delete from LDAP_ATTR_SS where LUID = '0IP53JMT02' and ATTRIB_NAME = 'mailAlternateAddress' and ATTRIB_VALUE = 'cody.johnson@alaska.edu'; %
In this example, LUID, ATTRIB_NAME and ATTRIB_VALUE have the same meanings that they did in the Add example. The LUID uniquely identifies the entry (i.e. the EDIR account) and the ATTRIB_NAME and ATTRIB_VALUE uniquely identify the attribute within the entry.
Log in to the People Registry and Submit the Script
The following example is for RPTP (Prod). Submit the SQL script to RPTP like this. The references to RPTP will be replaced by references to some other database as appropriate.
% sqlplus / SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 23 16:39:40 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options OPS$SXLDAP@RPTP> @phmccarthy_addEduPersonAffiliation.sql 1 row created. OPS$SXLDAP@RPTP> commit work; Commit complete. OPS$SXLDAP@RPTP> quit %
The following sequence sets the environment to one of the other environments. You must know the password for the environment to use this approach.
% . ua_oracle rpts env
% sqlplus
Enter user-name: ops$sxldap
Enter password:
RPTS>
Double-check to ensure that you deleted exactly the number of rows that you expected to delete. If anything unexpected happened, then roll back your changes:
RPTS> rollback work; Rollback complete.
If all seems well, then commit your delete:
RPTS> commit work; Commit complete. RPTS> quit %
