SqlServer-RBAC五表权限

  这只是一个精简的SqlServer-RBAC五表权限，根据自身需求修改即可

--创建数据库-权限
CREATE DATABASE RBAC
GO
USE RBAC
GO

--1、权限表
CREATE TABLE t_permission (
id INT PRIMARY KEY IDENTITY,
name varchar(20) NOT NULL,
[description] varchar(255)
)

--2、角色表
CREATE TABLE t_role (
id INT PRIMARY KEY IDENTITY,
name varchar(20) NOT NULL,
[description] varchar(255)
)

--3、用户表
CREATE TABLE t_user (
id INT PRIMARY KEY IDENTITY,
username varchar(20) NOT NULL
)

--4、角色权限关系表
CREATE TABLE role_permission (
id int PRIMARY KEY IDENTITY,
role_id INT FOREIGN KEY REFERENCES dbo.t_role(id),
permission_id INT FOREIGN KEY REFERENCES dbo.t_permission(id),
)

--5、用户角色关系表
CREATE TABLE user_role (
id int PRIMARY KEY IDENTITY,
user_id INT FOREIGN KEY REFERENCES dbo.t_user(id),
role_id INT FOREIGN KEY REFERENCES dbo.t_role(id)
)

--测试数据
INSERT INTO t_permission VALUES ('小说收藏',NULL), ('小说发布',NULL), ('广告发布',NULL)
INSERT INTO t_role VALUES ('读者',NULL), ('作者',NULL), ('管理员',NULL)
INSERT INTO t_user VALUES ('reader'), ('author'), ('admin')
INSERT INTO role_permission VALUES ('1','1'), ('2','2'), ('3','3')
INSERT INTO user_role VALUES ('1','1'), ('2','2'), ('3','3')

--查询
SELECT * FROM dbo.t_permission
SELECT * FROM dbo.t_role
SELECT * FROM dbo.t_user
SELECT * FROM dbo.role_permission
SELECT * FROM dbo.user_role