Changes between Initial Version and Version 1 of EDIR_adhoc_updates

Timestamp:

12/23/14 16:59:08 (10 years ago)

Author:

lttoth@…

Comment:

--

EDIR_adhoc_updates

@@ +1 @@
+= 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
+  1. Log in to target database, RPTP (Prod), RPTT (Test), or RPTQ (Prep)
+  1. Shell to user 'sxldap'
+  1. Create SQL update file
+  1. Log in to People Registry Schema
+  1. 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
+        %
+}}}