2 # Complex sample libnss-mysql database
4 # $Id: sample_database.sql,v 1.2 2004/07/21 17:53:14 cinergi Exp $
6 # sample_database.sql revision compatible libnss-mysql versions
7 # -------------------------------------------------------------------
10 # Use 'mysql -u root -p < sample_database.sql' to load this example into MySQL
13 # This particular example is actually a modified version of the database
14 # in use at the ISP I originally developed this for. It is designed this
15 # way in order to be able to define one "person" and have that one "person"
16 # have multiple "services" (accounts) without duplicating the "person"
17 # over and over again. Services are linked to customers via the cust_num
18 # field. The "service_defs" table enables you to define different
19 # homedir and shell structures depending on the service ("product" in
20 # their "services" entry) they purchased. This was useful to separate
21 # dialup users and email-only users as I configured cistron radius to
22 # disallow users with a certain shell.
23 # Note that I do not make use of the shadow 'expire' field - instead, when
24 # a user 'expires' the stop showing up in the system, as if deleted.
25 # Setting the 'suspend' field for a user will do the same thing. I designed
26 # it this way because, back when I implemented all of this, a lot of
27 # software did not pay attention to the shadow expire field.
33 CREATE TABLE customer (
34 cust_num int(11) NOT NULL auto_increment,
35 first_name varchar(25) NOT NULL default '',
36 last_name varchar(25) NOT NULL default '',
37 middle_initial char(1) default NULL,
38 company varchar(50) default NULL,
39 address_one varchar(35) default NULL,
40 address_two varchar(35) default NULL,
41 city varchar(25) default NULL,
42 state char(2) default NULL,
43 zip varchar(10) default NULL,
44 home_phone varchar(20) default NULL,
45 work_phone varchar(20) default NULL,
47 signupdate date NOT NULL default '0000-00-00',
48 PRIMARY KEY (cust_num)
51 CREATE TABLE service_defs (
52 name varchar(25) NOT NULL default '',
53 shell varchar(255) NOT NULL default '/bin/date',
54 homedir varchar(255) NOT NULL default '/tmp',
58 CREATE TABLE services (
59 cust_num int(11) NOT NULL default '0',
60 username varchar(16) NOT NULL default '',
61 uid int(11) NOT NULL auto_increment,
62 gid int(11) NOT NULL default '100',
63 password varchar(16) NOT NULL default '',
64 product varchar(25) default NULL,
65 created date NOT NULL default '0000-00-00',
66 expire date NOT NULL default '0000-00-00',
67 suspended set('Y','N') NOT NULL default 'N',
70 UNIQUE KEY username (username),
71 KEY cust_num (cust_num)
72 ) TYPE=MyISAM AUTO_INCREMENT=5000;
75 INSERT INTO customer (first_name,last_name,middle_initial)
76 VALUES ('Benjamin','Goodwin','C');
77 INSERT INTO services (cust_num,username,password,product,created,expire)
78 VALUES (LAST_INSERT_ID(),'cinergi',ENCRYPT('cinergi'),'Basic Dialup',NOW(),
79 DATE_ADD(NOW(), INTERVAL 1 YEAR));
80 INSERT INTO service_defs (name)
81 VALUES ('Basic Dialup');
84 GRANT USAGE ON *.* TO `nss-root`@`localhost` IDENTIFIED BY 'rootpass';
85 GRANT USAGE ON *.* TO `nss-user`@`localhost` IDENTIFIED BY 'userpass';
87 GRANT Select (`cust_num`, `uid`, `gid`, `password`, `product`, `expire`,
88 `suspended`, `username`)
90 TO 'nss-root'@'localhost';
91 GRANT Select (`cust_num`, `first_name`, `last_name`, `middle_initial`)
93 TO 'nss-root'@'localhost';
94 GRANT Select (`name`,`shell`,`homedir`)
95 ON `auth`.`service_defs`
96 TO 'nss-root'@'localhost';
98 GRANT Select (`cust_num`, `uid`, `gid`, `product`, `expire`, `suspended`,
101 TO 'nss-user'@'localhost';
102 GRANT Select (`cust_num`, `first_name`, `last_name`, `middle_initial`)
104 TO 'nss-user'@'localhost';
105 GRANT Select (`name`,`shell`,`homedir`)
106 ON `auth`.`service_defs`
107 TO 'nss-user'@'localhost';