| 1 |
CREATE TABLE roles |
| 2 |
( |
| 3 |
id INT NOT NULL PRIMARY KEY, |
| 4 |
role VARCHAR(100) NOT NULL UNIQUE |
| 5 |
); |
| 6 |
|
| 7 |
CREATE TABLE users |
| 8 |
( |
| 9 |
id INT NOT NULL PRIMARY KEY, |
| 10 |
username VARCHAR(100) NOT NULL UNIQUE, |
| 11 |
pwd VARCHAR(50) NOT NULL |
| 12 |
); |
| 13 |
|
| 14 |
CREATE TABLE user_roles |
| 15 |
( |
| 16 |
user_id INT NOT NULL, |
| 17 |
role_id INT NOT NULL, |
| 18 |
UNIQUE(user_id, role_id) |
| 19 |
); |
| 20 |
|
| 21 |
INSERT INTO roles VALUES |
| 22 |
(0,'user'), |
| 23 |
(1,'admin'); |
| 24 |
|
| 25 |
INSERT INTO users VALUES |
| 26 |
(1,'jetty','MD5:164c88b302622e17050af52c89945d44'), |
| 27 |
(2,'admin','CRYPT:adpexzg3FUZAk'), |
| 28 |
(3,'other','OBF:1xmk1w261u9r1w1c1xmq'), |
| 29 |
(4,'plain','plain'), |
| 30 |
(5,'user','password'), |
| 31 |
(6,'digest','MD5:6e120743ad67abfbc385bc2bb754e297'), |
| 32 |
(7,'dstest','dstest'); |
| 33 |
|
| 34 |
INSERT INTO user_roles VALUES |
| 35 |
(1,1), |
| 36 |
(2,1), |
| 37 |
(2,2), |
| 38 |
(3,1), |
| 39 |
(4,1), |
| 40 |
(5,1), |
| 41 |
(6,1), |
| 42 |
(7,1); |