Chapter Contents
The Elektron ODBC authentication method allows you to store your user credentials in an ODBC-compliant SQL database. This has the advantage of supporting all the high-end features of RDBMS systems like replication and failover, while giving you the ability to create tools to manage user accounts separately from the Elektron Settings application.
ODBC Configuration
To use an ODBC-compliant database as your user credential store, you must configure the following options:
Data Source Name
This is the ODBC Data Source Name (DSN) for the target server. You may create a data source using ODBC Administrator (on Mac OS X, in /Applications/Utilities) or Data Sources (ODBC) (on Windows, found under Start->Control Panel->Administrative Tools). Be sure to create a system data source for use with Elektron — user data sources will not be visible to Elektron.
Database
This field should contain the name of the database containing your user credential table. If the database is specified in the ODBC data source, you may leave this field blank.
Username
Enter the username that will be used to authenticate to the database. If a username is not required or is specified in the ODBC data source, you may leave this field blank.
Password
In this field you may specify the password that will be used to authenticate to the database. If a password is not required or is specified in the ODBC data source, you may leave this field blank.
Table
Enter the name of the table that contains your user information. This field is required.
Username Column
This field contains the name of the column that holds the login names of users. This field is required.
Real Name Column
This field contains the name of the column that holds the full name of users (such as “Alice Smith”). This field is optional; information from it is used only for display purposes.
NT Password Hash Column
This field contains the name of the column that holds the NT password hash of the user’s password. By storing NT password hashes, you can avoid having to keep passwords in plaintext form in your SQL database. This field is optional, but if not included then the LM Password Hash Column or Plaintext Password Column must be included.
NT password hash calculation is defined in RFC 2433. It is the MD4 hash of the user’s password in 16 bit Unicode format. This results in a binary block of 16 bytes, which Elektron expects to be encoded into a string consisting of the hex-encoded values. For instance, the NT password hash of the password “password” should be stored in the NT Password Hash Column as the value “8846F7EAEE8FB117AD06BDD830B7586C”.
LM Password Hash Column
This field contains the name of the column that holds the LM password hash of the user’s password. By storing LM or NT password hashes, you can avoid having to keep password in plaintext form in your SQL database. This field is optional, but if not included then the NT Password Hash Column or Plaintext Password Column must be included.
The LM password hash is described in RFC 2433. It uses DES as a hash function to compute a value from the user’s password. Like the NT password hash, the result is a block of binary data that must be hex-encoded before being stored in the database. For example, the LM password hash of the password “password” would be stored in the LM Password Hash Column as “E52CAC67419A9A224A3B108F3FA6CB6D”.
Plaintext Password Column
This column contains the user’s password without any kind of encoding. It is optional, but if it is not present then either the NT Password Hash Column or LM Password Hash Column must be included.
Group Membership Query
If you have defined authorization policies based on account group membership, you will need to define the group membership query so that a user’s groups can be pulled from the database. This field is optional, and is not necessary if your policies do not rely on account group membership.
In the group membership query field, enter a SQL statement that will query the database for the groups to which a specified user belongs. The result of the query should contain zero or more rows consisting of one column of values. Each row will contain the name of a single group to which the user belongs. When constructing the query, you may use the value “%u”, which will be replaced with the username of the user attempting to log in.
For example, a simple user authentication system with group information could be built with three tables. The tables are constructed using the following SQL statements:
CREATE TABLE users (username VARCHAR(128), realname TEXT, nthash CHAR(32), lmhash CHAR(32), password VARCHAR(255), uid INTEGER PRIMARY KEY NOT NULL);
CREATE TABLE groups (groupname VARCHAR(128), gid INTEGER PRIMARY KEY NOT NULL);
CREATE TABLE groupmembers (uid INTEGER, gid INTEGER);
In this example the “users” table contains the information for individual users, the “groups” table is a list of available groups, and the “groupmembers” table maps users to groups. To populate this database with users “testuser” and “anotheruser” and groups “Administrators”, “Power Users”, and “Guests”, the following SQL statements could be used (both users are created with the password “password”):
INSERT INTO users VALUES (‘testuser’, ‘Test User’, ‘8846F7EAEE8FB117AD06BDD830B7586C’, ‘E52CAC67419A9A224A3B108F3FA6CB6D’, ‘password’, 1);
INSERT INTO users VALUES (‘anotheruser’, ‘Another User’, ‘8846F7EAEE8FB117AD06BDD830B7586C’, ‘E52CAC67419A9A224A3B108F3FA6CB6D’, ‘password’, 2);
INSERT INTO groups VALUES (‘Administrators’, 1);
INSERT INTO groups VALUES (‘Power Users’, 2);
INSERT INTO groups VALUES (‘Guests’, 3);
Finally, to make “testuser” a member of the “Administrators” and “Power Users” groups, and to make “anotheruser” a member of “Administrators” and “Guests”, use these SQL statements:
INSERT INTO groupmembers VALUES (1, 1);
INSERT INTO groupmembers VALUES (1, 2);
INSERT INTO groupmembers VALUES (2, 1);
INSERT INTO groupmembers VALUES (2, 3);
A group membership query that would return the group names for an individual user based on this example schema could be:
SELECT groupname FROM groups WHERE gid IN (SELECT gid FROM groupmembers WHERE uid = (SELECT uid FROM users WHERE username=’%u’));
If Elektron executes the statement for user “testuser”, the “%u” variable will be automatically replaced with the username, and the query will return two rows: one with the value “Administrators” and another with the value “Power Users”.
Note that MySQL versions earlier than 4.1.0 do not support subqueries like the example above; you will need to substitute a join clause for the subquery.