92 lines
4.1 KiB
SQL
92 lines
4.1 KiB
SQL
DROP DATABASE db_sysrbacdemo;
|
||
CREATE DATABASE db_sysrbacdemo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
||
CREATE TABLE db_sysrbacdemo.sys_user (
|
||
id varchar(128) NOT NULL COMMENT '主键',
|
||
usercode varchar(32) NOT NULL COMMENT '账号',
|
||
username varchar(64) NOT NULL COMMENT '姓名',
|
||
password varchar(32) NOT NULL COMMENT '密码',
|
||
locked INT(4) DEFAULT NULL COMMENT '账号是否锁定，1：锁定，0未锁定',
|
||
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE current_timestamp COMMENT '更新时间',
|
||
PRIMARY KEY (id)
|
||
) COMMENT = '用户表'
|
||
ENGINE=InnoDB;
|
||
|
||
CREATE TABLE db_sysrbacdemo.sys_role (
|
||
id INT NOT NULL AUTO_INCREMENT,
|
||
name varchar(128) NOT NULL,
|
||
available INT(4) DEFAULT NULL COMMENT '是否可用,1：可用，0不可用',
|
||
PRIMARY KEY (id)
|
||
) COMMENT = '角色表'
|
||
ENGINE=InnoDB ;
|
||
|
||
CREATE TABLE db_sysrbacdemo.sys_user_role (
|
||
id INT NOT NULL AUTO_INCREMENT,
|
||
sys_user_id varchar(128) NOT NULL,
|
||
sys_role_id INT NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT FK_user FOREIGN KEY (sys_user_id) REFERENCES db_sysrbacdemo.sys_user (id),
|
||
CONSTRAINT FK_role FOREIGN KEY (sys_role_id) REFERENCES db_sysrbacdemo.sys_role (id)
|
||
) COMMENT = '用户角色关系表'
|
||
ENGINE=InnoDB;
|
||
|
||
CREATE TABLE db_sysrbacdemo.sys_permission (
|
||
id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
name varchar(128) NOT NULL COMMENT '资源名称',
|
||
type varchar(32) NOT NULL COMMENT '资源类型：menu,button,',
|
||
url varchar(128) DEFAULT NULL COMMENT '访问url地址',
|
||
percode varchar(128) DEFAULT NULL COMMENT '权限代码字符串',
|
||
parentid bigint(20) DEFAULT NULL COMMENT '父结点id',
|
||
parentids varchar(128) DEFAULT NULL COMMENT '父结点id列表串',
|
||
sortstring varchar(128) DEFAULT NULL COMMENT '排序号',
|
||
available INT(4) DEFAULT NULL COMMENT '是否可用,1：可用，0不可用',
|
||
PRIMARY KEY (id)
|
||
) COMMENT = '权限表'
|
||
ENGINE=InnoDB;
|
||
|
||
CREATE TABLE db_sysrbacdemo.sys_role_permission (
|
||
id INT NOT NULL AUTO_INCREMENT,
|
||
sys_role_id INT NOT NULL COMMENT '角色id',
|
||
sys_permission_id INT NOT NULL COMMENT '权限id',
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT FK_user_role FOREIGN KEY (sys_role_id) REFERENCES db_sysrbacdemo.sys_role (id),
|
||
CONSTRAINT FK_sys_role_permission FOREIGN KEY (sys_permission_id) REFERENCES db_sysrbacdemo.sys_permission (id)
|
||
) COMMENT = '角色权限关系表'
|
||
ENGINE=InnoDB;
|
||
|
||
CREATE VIEW v_sys_user_permission
|
||
AS
|
||
SELECT
|
||
u.username,
|
||
p.percode,
|
||
p.parentid
|
||
FROM db_sysrbacdemo.sys_permission p,db_sysrbacdemo.sys_user u,db_sysrbacdemo.sys_user_role ru,db_sysrbacdemo.sys_role_permission rp
|
||
WHERE ru.sys_user_id=u.id AND ru.sys_role_id=rp.sys_role_id AND rp.sys_permission_id=p.id;
|
||
|
||
INSERT INTO `sys_permission` (`id`, `name`, `type`, `url`, `percode`, `parentid`, `parentids`, `sortstring`, `available`) VALUES
|
||
(1, '权限', '', '', NULL, 0, '0/', '0', 1),
|
||
(2, '文件管理', 'menu', '/fileupload/upload', NULL, 4, '0/1/4', '1.', 1),
|
||
(3, '文件上传', 'permission', '/fileupload/upload/add', 'file:upload', 2, '0/1/4/2', '', 1),
|
||
(4, '登录', 'menu', '/fileupload/login', 'site:login', 1, '0/1', '2.', 1);
|
||
|
||
INSERT INTO `sys_role` (`id`, `name`, `available`) VALUES
|
||
(1, '普通用户', 0),
|
||
(2, '文件管理员', 0),
|
||
(3, '游客', 0);
|
||
|
||
INSERT INTO `sys_user` (`id`, `usercode`, `username`, `password`, `locked`, `create_time`, `update_time`) VALUES
|
||
('1529723171835369380', 'admin', 'admin', 'trs', 1, '2018-06-22 19:06:11', '2018-06-22 19:06:11'),
|
||
('1529752407871409646', 'user', 'fjy', 'trs', 1, '2018-06-23 11:13:27', '2018-06-23 11:13:48'),
|
||
('1529754856532186081', 'guest', 'guest', 'guest', 1, '2018-06-23 11:54:16', '2018-06-23 11:54:16');
|
||
|
||
INSERT INTO `sys_user_role` (`id`, `sys_user_id`, `sys_role_id`) VALUES
|
||
(6, '1529723171835369380', 2),
|
||
(7, '1529752407871409646', 2),
|
||
(8, '1529754856532186081', 3);
|
||
|
||
INSERT INTO `sys_role_permission` (`id`, `sys_role_id`, `sys_permission_id`) VALUES
|
||
(7, 2, 3),
|
||
(8, 1, 4);
|
||
|
||
GRANT ALL ON db_sysrbacdemo.* TO trs@localhost IDENTIFIED BY 'fileupload';
|
||
flush privileges; |