95 lines
3.0 KiB
SQL
95 lines
3.0 KiB
SQL
DROP DATABASE IF EXISTS db_shiroTest;
|
|
GRANT ALL ON db_shiroTest.* TO trs@localhost
|
|
IDENTIFIED BY 'shiro';
|
|
FLUSH PRIVILEGES;
|
|
CREATE DATABASE IF NOT EXISTS db_shiroTest
|
|
DEFAULT CHARSET utf8mb4
|
|
COLLATE utf8mb4_general_ci;
|
|
USE db_shiroTest;
|
|
-- 权限表 --
|
|
CREATE TABLE permission (
|
|
pid INT(11) NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL DEFAULT '',
|
|
url VARCHAR(255) DEFAULT '',
|
|
PRIMARY KEY (pid)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- 用户表 --
|
|
CREATE TABLE user (
|
|
uid INT(11) NOT NULL AUTO_INCREMENT,
|
|
username VARCHAR(255) NOT NULL DEFAULT '',
|
|
password VARCHAR(255) NOT NULL DEFAULT '',
|
|
PRIMARY KEY (uid)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- 角色表 --
|
|
CREATE TABLE role (
|
|
rid INT(11) NOT NULL AUTO_INCREMENT,
|
|
rname VARCHAR(255) NOT NULL DEFAULT '',
|
|
PRIMARY KEY (rid)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- 权限角色关系表 --
|
|
CREATE TABLE permission_role (
|
|
rid INT(11) NOT NULL,
|
|
pid INT(11) NOT NULL,
|
|
CONSTRAINT FK_prid FOREIGN KEY (rid) REFERENCES role (rid),
|
|
CONSTRAINT FK_pid FOREIGN KEY (pid) REFERENCES permission (pid)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- 用户角色关系表 --
|
|
CREATE TABLE user_role (
|
|
rid INT(11) NOT NULL,
|
|
uid INT(11) NOT NULL,
|
|
CONSTRAINT FK_urid FOREIGN KEY (rid) REFERENCES role (rid),
|
|
CONSTRAINT FK_uid FOREIGN KEY (uid) REFERENCES user (uid)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8mb4;
|
|
|
|
-- 初始化数据 --
|
|
INSERT INTO permission (pid, name, url) VALUE ('1', 'add', '');
|
|
INSERT INTO permission (pid, name, url) VALUE ('2', 'delete', '');
|
|
INSERT INTO permission (pid, name, url) VALUE ('3', 'edit', '');
|
|
INSERT INTO permission (pid, name, url) VALUE ('4', 'query', '');
|
|
|
|
INSERT INTO user (uid, username, password) VALUE ('1', 'admin', '0000');
|
|
INSERT INTO user (uid, username, password) VALUE ('2', 'trs', 'trs');
|
|
INSERT INTO user (uid, username, password) VALUE ('3', 'guest', 'guest');
|
|
|
|
INSERT INTO role (rid, rname) VALUE ('1', 'admin');
|
|
INSERT INTO role (rid, rname) VALUE ('2', 'customer');
|
|
INSERT INTO role (rid, rname) VALUE ('3', 'guest');
|
|
|
|
INSERT INTO permission_role (rid, pid) VALUE ('1', '1');
|
|
INSERT INTO permission_role (rid, pid) VALUE ('1', '2');
|
|
INSERT INTO permission_role (rid, pid) VALUE ('1', '3');
|
|
INSERT INTO permission_role (rid, pid) VALUE ('1', '4');
|
|
|
|
INSERT INTO permission_role (rid, pid) VALUE ('2', '1');
|
|
INSERT INTO permission_role (rid, pid) VALUE ('2', '3');
|
|
INSERT INTO permission_role (rid, pid) VALUE ('2', '4');
|
|
|
|
INSERT INTO permission_role (rid, pid) VALUE ('3', '4');
|
|
|
|
INSERT INTO user_role (rid, uid) VALUE ('1', '1');
|
|
INSERT INTO user_role (rid, uid) VALUE ('2', '2');
|
|
INSERT INTO user_role (rid, uid) VALUE ('3', '3');
|
|
|
|
CREATE VIEW v_user_role_permission
|
|
AS
|
|
SELECT u.username,r.rname,p.name,p.url
|
|
FROM user u
|
|
INNER JOIN user_role ur ON ur.uid = u.uid
|
|
INNER JOIN role r ON ur.uid = r.rid
|
|
INNER JOIN permission_role pr ON r.rid = pr.rid
|
|
INNER JOIN permission p ON pr.pid = p.pid
|