AFFILIATE PERSONNEL FEED FILE FORMAT Academic Information Systems (AcIS) Columbia University in the City of New York $Revision: 2.2 $ CHANGE HISTORY -------------- Version 2 of the feed file format is instituted in January 1996. Document changes are indicated by vertical bars in the margin. The following document specifies the format for files listing affiliate personnel who are to be included in the AcIS ColumbiaNet Kerberos database under a special agreement with AcIS. The Kerberos database controls access to basic Email and other restricted ColumbiaNet services contracted for. A file in this format is required for any affiliated institution (hereafter referred to as the Affiliate) wishing to use ColumbiaNet Email or other services. The following sections outline how the data is used, including optional uses to be chosen by the affiliate; how to deliver the "feed file" to AcIS; how it should be formatted; and what the required fields and their meanings are. USES OF THE DATA ---------------- Affiliate data is used to: 1. Populate the ColumbiaNet authentication (Kerberos) database. Each person is assigned a unique Network User Identifier (NUI), also known as a handle. 2. Optionally provide an online telephone/email directory: Affiliates may request that directory information in the file be made public through ColumbiaNet's directory (lookup) service by providing the "Directory Release" flag described below. This will allow the receipt of "mail-by-name" and allow any Internet user who links to the Directory service to retrieve the directory information. "Directory information" includes name, title, department, office phone, campus mailing address, and email address. "Mail-by-name" allows a user to receive mail addressed using his or her given and last name--as long as it is unique at Columbia--e.g., Walter_Bourne@columbia.edu. Please note that the affiliate is responsible for seeing that all privacy laws and regulations are adhered to. 3. Produce the "new" (as of 1/96) University identification cards for affiliated institutions whose ID cards are provided through the Columbia ID office. The authentication database is designed in such a way that multiply-affiliated individuals (i.e. both employee and student) are identified with a single NUI. MAKING THE FILE AVAILABLE TO ACIS --------------------------------- The file should be provided periodically (daily, weekly, or monthly) by placing it in a special directory in the Cunix home directory of the affiliate's designated representative. Files not provided online in this manner will only be accepted every six months and will be subject to an hourly consulting fee for data conversion (minimum one hour). The special directory must be called: acis-kerberos and the file in the directory should be named affiliate.list - list of affiliated persons The file must include ALL authorized individuals each time it is submitted. Changes in authorized membership will be inferred from the new file: 1. New names will be added to the Kerberos database; 2. Any names not present in the new file will be deleted from the database. To avoid synchronization problems, it is recommended that a new version of the file be uploaded under a temporary name and then renamed to affiliate.list once it has been verified that the file is intact. This will avoid problems with an upload overlapping loading of the file into the database or a failed upload causing a partial file to be left. For example: 1. Upload the file as 'affiliate.list.new' 2. Rename file with the mv command: mv affiliate.list.new affiliate.list ENCRYPTION ---------- If you prefer to bulk-encrypt the file, please use the standard Unix crypt(1) utility to encrypt the entire file. Choose an encryption key and communicate this key *out-of-band* (e.g. via phone or in person). Use the command 'man crypt' for information on using the crypt command. The shared secret field, described below, may be individually one-way encrypted for comparison purposes. The currently defined one-way encryption algorithms are: Algorithm 0: Not encrypted. Algorithm 1: Based on standard Unix one-way password encryption. Contact AcIS for further details. N.B.: Future public-key-based encryption technologies are being researched but are NOT presently supported. FILE LAYOUT ----------- One of four file layouts may be used: 1. Blank-padded, fixed-width fields all on one "record" per person. (Mainframe programmers are generally most familiar with this format.) 2. One "tagged" field per record, yielding multiple records per person. (This format is more amenable to microcomputer word processors since the text lines are not so long.) 3. Same as format 1, above, with additional "version 2" fields. 4. Same as format 2, above, with additional "version 2" fields. VERSION 2 --------- In January, 1996, version 2 of the affiliate feed file specification is instituted. Version 2 is upwardly compatible from version 1 and includes some new fields and new values for old fields which are documented below. Affiliates who do not need the new features provided by version 2 are not required to upgrade. In each case, the file must be plain ASCII text, with the Unix newline character (ASCII linefeed) as the record delimiter. (In other words, a plain old Unix text file.) Wordprocessor file formats are not accepted. If using a wordprocessor to create the file, find out how to "export" the file as ASCII text. Note that using ftp or Kermit to upload the file in 'text' mode will create the proper newline character. The details of these formats and examples follow below. Format 1 files simply have fixed-width columns of data. For example, assuming that the only fields are SubAffil (length 4), Unique ID (10), SSN (9), Surname (50), Given Names (50), the file would look like: STAF0000000001123456789Smith John STAF0000000002987654321Doe Jane Format 2 files have one field per line. In order to avoid errors caused by a field being accidentally omitted (which would then cause the remainder of the file to be out of sync), each field is preceded by an identifying two digit "tag". For example: 01STAF 020000000001 03123456789 04Smith 05John 01STAF 020000000002 03987654321 04Doe 05Jane FIELD DEFINITIONS ----------------- The following describes each field, giving its tag (for format 2 files), name, maximum length (which in format 1 files are padded on the right with blanks), and description. Please note the following general rules: 1. ALL FIELDS listed below are REQUIRED and must be supplied in the order listed. 2. Always left justify and pad on the right. Some fields may optionally be left blank and are so indicated in the description. 3. The first two fields, marked below with an asterisk, are considered to be the unique "key" for each record and, in combination, must be UNIQUE across ALL INDIVIDUALS who may ever appear in any original or replacement feed file. 4. For dates use the following format: YYYYMMDD. For example 19940421 for 21 April, 1994. Tag Field Name Width Description --- ---------- ----- --------------------------------------------- 01 *SubAffil 04 Affiliation within affiliated organization (e.g. INST, STUD, ADMN, etc.) Useful when the same person may have multiple affiliations within your organization. Also, if your organization has subscribed to AcIS basic and/or extended email account service, then use one of the following values: INST - Officer of Instruction (or equivalent) RSCH - Officer of Research (or equivalent) ADMN - Officer of Administration (or equivalent) LIBR - Officer of Libraries (or equivalent) SUPS - Supporting Staff STAF - "generic" employee (version 1) STUD - Student Types INST, RSCH, ADMN, LIBR, SUPS are added in version 2 and are intended to replace STAF. Standard AcIS account policies apply based on the above affiliations. 02 *Unique ID 10 A unique identifier if one is available in the affiliate's database. If not, use, e.g. SSN. This unique ID is used to match records from one instance of the extract file to the next. 03 SSN 09 Social Security Number. Used for matching with data from other people-data sources (such as the CLIO Patron File or cross-registration files) so that a single individual who is multiply affiliated shows up as one person and not several. Please either list an actual SSN or leave blank. 04 Surname 50 Last name. Correct alphabetic case preferred. 05 Given names 50 First, middle, etc. names, separated with blanks. 06 Secret-type 02 A two-letter code indicating the type of secret (to enable useful prompting by the automated ID creation software). Valid types are: S0 - Social Security Number S1 - SSN, encrypted using algorithm 1 D0 - Date of Birth (YYYYMMDD) D1 - DOB, encrypted using algorithm 1 P0 - Affiliate-assigned Personal Identification Code (PIN) (may be arbitrary text) P1 - PIN, encryptyed using algorithm 1 New codes will be added upon request. Encryption algorithms are defined above. 07 Secret 20 Shared secret info to be used for automated ID creation password. Should not be easily known except by person concerned. Examples are a PIN or date of birth. Do NOT disclose PINs to AcIS if they protect important affiliate information. Leave blank if no secret, in which case automated ID creation will be impossible. 08 Title 50 Job title. Mixed case please. Leave blank if none. 09 Department 50 Department within affiliate organization, if any. Mixed case. Leave blank if none. 10 Addr line 1 50 Line 1 of local mailing address. Leave blank if none. 11 Addr line 2 50 Line 2 12 Addr line 3 50 Line 3 13 Addr line 4 50 Line 4 14 Phone 10 Local phone (sample format: 2128541234) or blank. 15 Fax 10 Local fax (see phone) 16 Email 50 Local Email address provided by your institution, i.e., not on the central CUNIX systems. This field will be used to create a forwarding address on the Columbia mail hub. If no local Email, leave blank. 17 Basic ID 01 Y or N. Y means enable a basic AcIS email account. Available only by special advance arrangement. 18 Extended ID 01 Y or N. Y indicates that a fee-based extended AcIS email account has been authorized which will be billed to the affiliate's general project number. Extended accounts have unlimited connect time and more disk space -- for a fee. Available only by special advance arrangement. 19 Dir Release 01 Y or N. Y indicates that listing in our online phonebook (lookup) is authorized by affiliate and that affiliate has necessary permission to do so, e.g. FERPA clearance for students. 20 Good From 08 Date that affiliation is good from. See DATES note. If from now forward, any date in the past (e.g. 00000101). 21 Good Until 08 Date that affiliation should expire. If date is for the forseeable future (e.g. until affiliation agreement expires) then use a date in the distant future (e.g. 99991231). The Good Until date is used as the ID card expiration date as well. ***** Following are used for Version 2 files only (formats 3 and 4) ********* 22 Under 21 01 Y, N, or blank. Y indicates that the person is under age 21. 23 Gender 01 M, F, or blank. 24 Bldg Code 04 Residence Halls building code for students 25 Reg Term 05 YYYYT. Most current registration term. T is 1, 2, or 3 for spring, summer, or fall, respectively.