Selected Database Tables

Table of contents

   Records table
   Names table
   Misc
      How to expose ACIS database for Person Identification Aid

ACIS creates and maintains a number of database tables. These tables are needed for system’s work, but can be used for additional purposes.

Records table

Contains basic information about each personal record currently maintained through the system. Database name: as configured in main.conf via acis-db-name, table name: records.

Columns: shortid, id, owner, userdata_file, namelast, namefull, profile_url, emailmd5.

Names table

Contains name variations of each personal record currently maintained through the system. Database name: as configured in main.conf via acis-db-name, table name: names.

Columns: shortid, name, probability.

An index: PRIMARY KEY ( shortid, name )

Misc

How to expose ACIS database for Person Identification Aid

This is needed to allow a document submission service to search the personal records database (and offer matching items for user to select).

Given the fact that ACIS uses MySQL for database, and MySQL is a network-capable system, and includes an authorization/authentication subsystem, it’s most simple to use these features. It is in the power of the ACIS administrator to make a particular database table accessible to another user on the net.

To open access to the needed database tables, ACIS administrator have to:

1. Configure his MySQL server to listen to a network TCP port on the machine; ensure the server port is accessible from the submission service machine. (May involve changes to a firewall configuration.)

   Usual default configuration of MySQL enables TCP network access on port 3306. You may check or change it in your /etc/my.cnf or /etc/mysql/my.cnf file.

2. Create a new MySQL user with a password for a submission service to use. Give to this new user SELECT privilege on certain fields of the records table and on the names table. Here is what statements to run:

   GRANT SELECT (shortid,id,namelast,namefull,profile_url,homepage,emailmd5)
     ON acisDatabase.records
     TO frank@eprints.super.edu IDENTIFIED BY "goblin";
   

   GRANT SELECT
     ON acisDatabase.names
     TO frank@eprints.super.edu;
   

   FLUSH PRIVILEGES;
   

   Here acisDatabase is the database name (the acis-db-name parameter in main.conf), frank@eprints.super.edu is the name of the user and its hostname (name of the host from which it will contact our database), goblin is its password. You can run these statements in MySQL’s mysql terminal utility. You will need administrator’s privileges.

   If the above does not work, and you can’t get database connected (error message “Client does not support authentication protocol requested by server; consider upgrading MySQL client”), read that. Depending on your MySQL server version and client libraries your remedy may be in these statements:

   UPDATE mysql.user SET Password = OLD_PASSWORD('goblin')
     WHERE Host = 'eprints.super.edu' AND User = 'frank';
   

   FLUSH PRIVILEGES;
   

3. Send the hostname (local host where ACIS and MySQL are installed), the port number (e.g. 3306), the database name (e.g. acisDatabase), the user name (e.g. frank), the password (e.g. goblin) to the submission service administrator. (These items are collectively referred to as “MySQL access parameters” in Saskatoon doc.)

Relevant pieces of MySQL documentation online:

• Using MySQL Programs

• Configuring the MySQL Server

• The MySQL Access Privilege System

  • How the Privilege System Works
  • Password Hashing in MySQL 4.1

• MySQL User Account Management

  • Adding New User Accounts to MySQL
  • Assigning Account Passwords

• Account Management Statements

  • GRANT and REVOKE Syntax