Authentication Database Components

Three database tables are required to implement a minimal authentication/single sign-on solution:

1. users( username, password )
2. sessions( sid, username )
3. roles( username, role )

Alternatively, an LDAP directory may be used in lieu of a relational database.

The users table has two columns:

CREATE TABLE users (
  username  VARCHAR(8)  NOT NULL,
  password  CHAR(32)    NOT NULL,
  PRIMARY KEY (username) );

For security reasons, the password is stored as an MD5 hash (or other suitable hash value). In particular, cleartext passwords are not stored in the database. Instead, passwords are one-way hashed using MD5:

INSERT INTO users VALUES
  ( 'trscavo', MD5( 'nopass' ) ), ...

Regardless of input, an MD5 hash value is precisely 32 characters:

SELECT MD5( 'nopass' );
==> 0945fc9611f55fd0e183fb8b044f1afe

The sessions table has two columns:

CREATE TABLE sessions (
  sid       CHAR(13)    NOT NULL,
  username  VARCHAR(8)  NOT NULL,
  PRIMARY KEY (sid) );

The session ID (sid) is a unique sequence of 13 characters (or other suitable ID).

The roles table has two columns:

CREATE TABLE roles (
  username  VARCHAR(8)  NOT NULL,
  role      VARCHAR(20) NOT NULL,
  PRIMARY KEY (username,role) );

Access to protected resources is based on roles, which are assigned by the system administrator:

INSERT INTO roles VALUES
  ( 'trscavo', 'user' ),
  ( 'trscavo', 'administrator' );

Any given user may be assigned multiple roles.