MySQL创建权限和角色对应的操作权限表为权限认证做准备------MySQL

旧约Alatus

于 2024-06-13 00:04:31 发布

阅读量1.1k

点赞数 14

分类专栏：数据库 # MySQL 文章标签：数据库 sql 数据库系统 database mysql 后端 java

本文链接：https://blog.csdn.net/2201_75960169/article/details/139625928

版权

数据库同时被 2 个专栏收录

106 篇文章 0 订阅

订阅专栏

MySQL

12 篇文章 0 订阅

订阅专栏

CREATE TABLE permissions (
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
    info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
    `desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
    PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `uid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
    `pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

SELECT info FROM permissions WHERE id IN (
	SELECT rid FROM role_user WHERE id 
)
CREATE TABLE permissions (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
`desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`uid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
`pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

SELECT info FROM permissions WHERE id IN (
SELECT rid FROM role_user WHERE id
)

CREATE TABLE permissions (
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
    info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
    `desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
    PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `rid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
    `pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

UPDATE `shirodb`.`role_ps` SET `pid` = '3' WHERE `id` = '3'

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

SELECT info FROM permissions WHERE id IN (
	SELECT pid FROM role_ps WHERE rid IN (
		SELECT id FROM role WHERE NAME IN ('admin','userMag')
	)
)
CREATE TABLE permissions (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
`desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`rid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
`pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

UPDATE `shirodb`.`role_ps` SET `pid` = '3' WHERE `id` = '3'

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

SELECT info FROM permissions WHERE id IN (
   SELECT pid FROM role_ps WHERE rid IN (
       SELECT id FROM role WHERE NAME IN ('admin','userMag')
   )
)

CREATE TABLE permissions (
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
    info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
    `desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
    PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `rid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
    `pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

UPDATE `shirodb`.`role_ps` SET `pid` = '3' WHERE `id` = '3'

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

UPDATE `shirodb`.`user` SET `rid` = '2' WHERE `id` = '1'

SELECT info FROM permissions 
WHERE id IN (
   SELECT pid FROM role_ps 
   WHERE rid IN (
       SELECT id FROM role 
       WHERE NAME IN (
           'admin','userMsg'
       )
   )
)

UPDATE `shirodb`.`user` SET `rid` = '1' WHERE `id` = '2'

`user`
CREATE TABLE permissions (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
NAME VARCHAR(30) DEFAULT NULL COMMENT '权限名',
info VARCHAR(30) DEFAULT NULL COMMENT '权限信息',
`desc` VARCHAR(50) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限表';

INSERT INTO `shirodb`.`role_ps` (`id`, `uid`, `pid`) VALUES ('3', '1', '3')

CREATE TABLE `role_ps` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`rid` BIGINT(20) DEFAULT NULL COMMENT '用户 id',
`pid` BIGINT(20) DEFAULT NULL COMMENT '权限 id',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限用户映射表';

UPDATE `shirodb`.`role_ps` SET `pid` = '3' WHERE `id` = '3'

INSERT INTO `shirodb`.`role_user` (`id`, `uid`, `rid`) VALUES ('3', '2', '2')

INSERT INTO `shirodb`.`permissions` (`id`, `name`, `info`, `desc`) VALUES ('3', '修改用户', 'user:edit', '修改用户')

UPDATE `shirodb`.`user` SET `rid` = '2' WHERE `id` = '1'

SELECT info FROM permissions
WHERE id IN (
SELECT pid FROM role_ps
WHERE rid IN (
SELECT id FROM role
WHERE NAME IN (
'admin','userMsg'
)
)
)

UPDATE `shirodb`.`user` SET `rid` = '1' WHERE `id` = '2'

`user`