User administration

Goals

  • Create a database with all users on campus.
  • Connect the following services with the database
    • Mail server
    • FTP server
  • Administer people in the database according to their status (student/staff/retired/…)
  • Connect library user database with user adminisration
  • Create web interface to administer database

Implementation

Database structure

Version 19 May 2008

CREATE DATABASE useradministration;
USE useradministration;

CREATE TABLE `users` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
login VARCHAR(8) UNIQUE NOT NULL, KEY(login),
barcode VARCHAR(13),
first_name VARCHAR(28) NOT NULL,
last_name VARCHAR(28) NOT NULL,
local_part VARCHAR(40) NOT NULL, INDEX(local_part),
tel VARCHAR(16),
fax VARCHAR(16),
nt_password VARCHAR(255),
lm_password VARCHAR(255),
crypt_password VARCHAR(255),
unix_password VARCHAR(255),
ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ts_create TIMESTAMP NULL,
ts_exp TIMESTAMP NULL
) ENGINE = InnoDB COMMENT='Our users.';


CREATE TABLE `domains` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB COMMENT='The domains that we are hosting';


CREATE TABLE `email_aliases` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT NOT NULL,
local_part VARCHAR(20) NOT NULL, INDEX(local_part),
destination VARCHAR(80) NOT NULL,
ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE = InnoDB COMMENT='Virtual Aliases';

CREATE TABLE `email_accounts` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT NOT NULL, KEY(domain_id),
user_id INT NOT NULL, KEY(user_id),
status  TINYINT NOT NULL DEFAULT '1',
quota VARCHAR(32) NOT NULL DEFAULT '500000S',
ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user_id),
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = InnoDB COMMENT='Email accounts';

CREATE TABLE `ftp_accounts` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT NOT NULL, KEY(domain_id),
user_id INT NOT NULL, KEY(user_id),
uid INT NOT NULL DEFAULT '5500',
gid INT NOT NULL DEFAULT '5500',
status  TINYINT NOT NULL DEFAULT '1',
ul_bandwidth smallint(5) NOT NULL default '0',
dl_bandwidth smallint(5) NOT NULL default '0',
ipaccess varchar(15) NOT NULL default '*',
quota_size smallint(5) NOT NULL default '0',
quota_files  int(11) NOT NULL default '0',
ts_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT UNIQUE_FTPUSER UNIQUE (domain_id,user_id),
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = InnoDB COMMENT='FTP accounts';

Views

Problem: indexes on views are not supported!

CREATE VIEW view_email_accounts AS
SELECT CONCAT(u.local_part, '@', d.name) AS email,
       CONCAT( '{crypt}',u.crypt_password) AS password,
       CONCAT(d.name, '/', u.local_part, '/' ) AS home,
       CONCAT(d.name, '/', u.local_part, '/Maildir/') AS maildir,
       e.status,
       e.quota
FROM email_accounts AS e, domains AS d, users AS u WHERE e.domain_id = d.id and u.id = e.user_id;

CREATE VIEW view_email_aliases AS
SELECT CONCAT(a.local_part, '@', d.name) AS email,
       destination
FROM email_aliases a, domains AS d WHERE a.domain_id=d.id;

CREATE VIEW view_ftp_accounts AS
SELECT CONCAT(u.local_part, '@', d.name) AS ftp_login,
       u.crypt_password AS password,
       CONCAT(d.name, '/', u.local_part, '/' ) AS home,
       f.status,
       f.uid,
       f.gid,
       f.ul_bandwidth,
       f.dl_bandwidth,
       f.ipaccess,
       f.quota_size,
       f.quota_files
FROM ftp_accounts AS f, domains AS d, users AS u WHERE f.domain_id = d.id and u.id = f.user_id;

Hosted domains

  • For each domain we host:
    insert into domains set name='vliruos-ict.ugent.be', id = 1;

Accounts

  • Create a user in the users table (X is CRYPT password hash, Y is MD5 password hash):
    insert into users set 
      id=1,
      login='rgevaert',
      local_part='rudy.gevaert',
      first_name='Rudy',
      last_name='Gevaert',
      ts_create=NOW(),
      crypt_password='X',
      unix_password = 'Y';
  • Create an email alias for that user if necessary:
    insert into email_aliases set 
      domain_id=1,
      local_part='helpdesk',
      destination='rudy.gevaert@vliruos-ict.ugent.be';
  • Create an email account:
    insert into email_accounts set domain_id=1, user_id=1;
  • Create an ftp account:
    insert into ftp_accounts set domain_id=1, user_id=1;
vlir/projects/useradministration.txt · Last modified: 2008/05/19 15:12 by rudy
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Run by Debian Driven by DokuWiki