67 lines
2.4 KiB
Plaintext
67 lines
2.4 KiB
Plaintext
|
|
|
|
CREATE TABLE report_user(
|
|
username VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
password VARCHAR(255) NOT NULL,
|
|
activity_id VARCHAR(255),
|
|
descn VARCHAR(255),
|
|
FOREIGN KEY(activity_id) REFERENCES activity(id)
|
|
);
|
|
|
|
CREATE TABLE report_role(
|
|
rolename VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
descn VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE report_user_role(
|
|
username VARCHAR(255) NOT NULL REFERENCES report_user(username),
|
|
rolename VARCHAR(255) NOT NULL REFERENCES report_role(rolename),
|
|
PRIMARY KEY(username, rolename)
|
|
);
|
|
|
|
CREATE TABLE report_authorities(
|
|
authority VARCHAR(255) NOT NULL PRIMARY KEY,
|
|
descn VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE report_role_authorities(
|
|
rolename VARCHAR(255) NOT NULL REFERENCES report_role(rolename),
|
|
authority VARCHAR(255) NOT NULL REFERENCES report_authorities(authority),
|
|
PRIMARY KEY(rolename, authority)
|
|
);
|
|
|
|
#权限定义
|
|
INSERT INTO report_authorities VALUES('report_detail','查看明细报表权限');
|
|
INSERT INTO report_authorities VALUES('report_token','查看验证码使用情况权限');
|
|
INSERT INTO report_authorities VALUES('report_total','查看总计报表权限');
|
|
|
|
#角色定义
|
|
INSERT INTO report_role VALUES('role_admin','拥有查看报表的所有权限');
|
|
INSERT INTO report_role VALUES('role_detail','只能够查看明细报表');
|
|
INSERT INTO report_role VALUES('role_token','只能够查看验证码使用情况');
|
|
INSERT INTO report_role VALUES('role_tatal','只能够查看总计报表');
|
|
|
|
#给角色赋予相应的权限
|
|
INSERT INTO report_role_authorities VALUES('role_admin','report_detail');
|
|
INSERT INTO report_role_authorities VALUES('role_admin','report_token');
|
|
INSERT INTO report_role_authorities VALUES('role_admin','report_total');
|
|
INSERT INTO report_role_authorities VALUES('role_detail','report_detail');
|
|
INSERT INTO report_role_authorities VALUES('role_token','report_token');
|
|
INSERT INTO report_role_authorities VALUES('role_tatal','report_total');
|
|
|
|
#定义用户
|
|
INSERT INTO report_user VALUES('coast','coast','01','海岸城报表查看用户');
|
|
INSERT INTO report_user VALUES('coast_total','coast__total','01','海岸城报表查看用户(只能够查看总计报表)');
|
|
INSERT INTO report_user VALUES('chengdu','chengdu','02','成都八月活动报表查看用户');
|
|
|
|
#给用户赋予相应的角色
|
|
INSERT INTO report_user_role VALUES('coast','role_admin');
|
|
INSERT INTO report_user_role VALUES('coast_total','role_detail');
|
|
INSERT INTO report_user_role VALUES('chengdu','role_admin');
|
|
|
|
|
|
|
|
|
|
|
|
|