rtAEFEh6

1--
2-- roles table
3--
4DROP TABLE IF EXISTS roles;
5CREATE TABLE roles (
6 role_id            int(11) NOT NULL AUTO_INCREMENT,
7 role_name          varchar(20) COLLATE utf8_unicode_ci NOT NULL,
8 CONSTRAINT PK_role PRIMARY KEY (role_id)
9) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
10ALTER TABLE roles AUTO_INCREMENT=10001;
11
12INSERT INTO roles(role_name) VALUES('Admin');
13INSERT INTO roles(role_name) VALUES('Editor');
14INSERT INTO roles(role_name) VALUES('User');
15INSERT INTO roles(role_name) VALUES('Guest');
16
17--
18-- resources table
19--
20DROP TABLE IF EXISTS resources;
21CREATE TABLE resources (
22 resource_id         int(11) NOT NULL AUTO_INCREMENT,
23 resource_name       varchar(20) COLLATE utf8_unicode_ci NOT NULL,
24 CONSTRAINT FK_resource PRIMARY KEY (resource_id)
25) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
26ALTER TABLE resources AUTO_INCREMENT=10001;
27
28INSERT INTO resources(resource_name) VALUES('Users');
29INSERT INTO resources(resource_name) VALUES('Projects');
30INSERT INTO resources(resource_name) VALUES('Programs');
31INSERT INTO resources(resource_name) VALUES('Articles');
32
33--
34-- permissions table
35--
36DROP TABLE IF EXISTS permissions;
37CREATE TABLE permissions (
38 resolution_id         int(11) NOT NULL AUTO_INCREMENT,
39 resolution_name       varchar(20) COLLATE utf8_unicode_ci NOT NULL,
40 CONSTRAINT FK_resolution PRIMARY KEY (resolution_id)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
42ALTER TABLE permissions AUTO_INCREMENT=10001;
43
44INSERT INTO permissions(resolution_name) VALUES('Create');
45INSERT INTO permissions(resolution_name) VALUES('Read');
46INSERT INTO permissions(resolution_name) VALUES('Write');
47INSERT INTO permissions(resolution_name) VALUES('Delete');
48
49--
50-- user_access table
51--
52DROP TABLE IF EXISTS user_access;
53CREATE TABLE user_access (
54 access_id          int(11) NOT NULL AUTO_INCREMENT,
55 role_id            int(11) NOT NULL,
56 resource_id        int(11) NOT NULL,
57 resolution_id      int(11) NOT NULL,
58 CONSTRAINT PK_access PRIMARY KEY (access_id),
59 CONSTRAINT FK_access_roles FOREIGN KEY (role_id) REFERENCES roles (role_id) ON UPDATE CASCADE ON DELETE CASCADE,
60 CONSTRAINT FK_access_resource FOREIGN KEY (resource_id) REFERENCES resources (resource_id) ON UPDATE CASCADE ON DELETE CASCADE,
61 CONSTRAINT FK_access_resolution FOREIGN KEY (resolution_id) REFERENCES permissions (resolution_id) ON UPDATE CASCADE ON DELETE CASCADE
62) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
63ALTER TABLE user_access AUTO_INCREMENT=10001;
64
65INSERT INTO user_access(role_id, resource_id, resolution_id) VALUES 10001, (10001, 10002, 10003, 10004), 10003;
66	
67INSERT INTO user_access(role_id, resource_id, resolution_id) VALUES (10001, 10001, 10003);
68INSERT INTO user_access(role_id, resource_id, resolution_id) VALUES (10001, 10002, 10003);
69INSERT INTO user_access(role_id, resource_id, resolution_id) VALUES (10001, 10003, 10003);
70...