基于角色的权限访问控制数据库设计- cychai的专栏- CSDN博客

基于角色的权限访问控制数据库设计 - cychai的专栏 -

CSDN博客

对于权限、角色、组、用户之间的关系，四者之间均是多对多的关系：

设计的原则：数据是数据，关系是关系。

1. 要求：

用户、客户、员工，这三者是一种继承的关系。分配角色，赋予不同的权限。

下面的设计并没有引入“组”的概念，只是涉及用户、权限、角色三者。

2. 数据库设计中实体表： 1) 用户表 user 2) 角色 role 3) 权限 permission

3. 关系表： 1) 用户角色表 userRole 2) 角色权限表 rolePermission

下面是使用PowerDesigner设计的PDM图：

主外键关系命名：

Fk_parent_<主键>_child_<外键>

表关系建立原则：

Table A 字段： aid(主键) Table B 字段： bid(主键) 1. 一对一 Table A

Table B 分别设置各自的主键 2. 一对多（A对B：1-n）

Table B中设置外键 aid 3. 多对多

必须设置一张单独的关系表 Table C 中设置外键 aid bid

下面是使用PowerDesigner设计后生成的SQL Server 2005脚本文件：

/*==============================================================*/

/* DBMS name: Microsoft SQL Server 2005 */

/* Author: ChaiChunyan

/* Created on: 2008-11-11 20:28:05 */

/*==============================================================*/

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('RolePermission') and o.name = 'FK_ROLEPERM_FK_PERMIS_PERMISSI') alter table RolePermission drop constraint

FK_ROLEPERM_FK_PERMIS_PERMISSI go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('RolePermission') and o.name = 'FK_ROLEPERM_FK_ROLE_R_ROLE') alter table RolePermission

drop constraint FK_ROLEPERM_FK_ROLE_R_ROLE go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('customer') and o.name = 'FK_CUSTOMER_FK_USER_I_USER') alter table customer

drop constraint FK_CUSTOMER_FK_USER_I_USER go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('employee') and o.name = 'FK_EMPLOYEE_FK_USER_I_USER') alter table employee

drop constraint FK_EMPLOYEE_FK_USER_I_USER go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('guest') and o.name = 'FK_GUEST_FK_USER_I_USER') alter table guest

drop constraint FK_GUEST_FK_USER_I_USER go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('userRole') and o.name =

'FK_USERROLE_FK_ROLE_R_ROLE') alter table userRole

drop constraint FK_USERROLE_FK_ROLE_R_ROLE go

if exists (select 1

from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

where r.fkeyid = object_id('userRole') and o.name = 'FK_USERROLE_FK_USER_I_USER') alter table userRole

drop constraint FK_USERROLE_FK_USER_I_USER go

if exists (select 1

from sysobjects

where id = object_id('Permission') and type = 'U') drop table Permission go

if exists (select 1

from sysobjects

where id = object_id('RolePermission') and type = 'U')

drop table RolePermission go

if exists (select 1

from sysobjects

where id = object_id('customer') and type = 'U') drop table customer go

if exists (select 1

from sysobjects

where id = object_id('employee') and type = 'U') drop table employee go

if exists (select 1

from sysobjects

where id = object_id('guest') and type = 'U') drop table guest go

if exists (select 1

from sysobjects

where id = object_id('role') and type = 'U') drop table role go

if exists (select 1

from sysobjects

where id = object_id('\ and type = 'U') drop table \go

if exists (select 1

from sysobjects

where id = object_id('userRole') and type = 'U') drop table userRole go

/*==============================================================*/

/* Table: Permission */

/*==============================================================*/

create table Permission (

ID int not null, Name nvarchar(64) null, constraint PK_PERMISSION primary key (ID) ) go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '权限',

'user', @CurrentUser, 'table', 'Permission' go

/*==============================================================*/

/* Table: RolePermission */

/*==============================================================*/ create table RolePermission (

roleID int not null, permissionID int not null, constraint PK_ROLEPERMISSION primary key (roleID,

permissionID) ) go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '授权',

'user', @CurrentUser, 'table', 'RolePermission' go

/*==============================================================*/

/* Table: customer */

/*==============================================================*/ create table customer (

customerID int not null, customerName varchar(64) null, companyName varchar(64) null, oder varchar(64) null, constraint PK_CUSTOMER primary key (customerID) )

go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '客户',

'user', @CurrentUser, 'table', 'customer' go

/*==============================================================*/

/* Table: employee */

/*==============================================================*/ create table employee (

employeeID int not null, employeeName varchar(64) null, sex int null, age int null, Department varchar(64) null, constraint PK_EMPLOYEE primary key (employeeID) ) go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '员工',

'user', @CurrentUser, 'table', 'employee' go

/*==============================================================*/

/* Table: guest */

/*==============================================================*/ create table guest (

guestID int not null, guestName nvarchar(64) null, constraint PK_GUEST primary key (guestID) ) go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '访客',

'user', @CurrentUser, 'table', 'guest' go

/*==============================================================*/

/* Table: role */

/*==============================================================*/ create table role (

roleID int not null, roleName varchar(64) null, constraint PK_ROLE primary key (roleID) ) go

/*==============================================================*/

/* Table: \ */

/*==============================================================*/ create table \

ID int not null,

Name varchar(20) null, Password varchar(20) null, constraint PK_USER primary key (ID) ) go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '用户数据表',

'user', @CurrentUser, 'table', 'user' go

/*==============================================================*/

/* Table: userRole */

/*==============================================================*/ create table userRole (

userID int not null, roleID int not null, constraint PK_USERROLE primary key (userID, roleID) )

go

declare @CurrentUser sysname select @CurrentUser = user_name()

execute sp_addextendedproperty 'MS_Description', '用户角色关系表',

'user', @CurrentUser, 'table', 'userRole' go

alter table RolePermission add constraint

FK_ROLEPERM_FK_PERMIS_PERMISSI foreign key (permissionID)

references Permission (ID) go

alter table RolePermission

add constraint FK_ROLEPERM_FK_ROLE_R_ROLE foreign key (roleID)

references role (roleID) go

alter table customer

add constraint FK_CUSTOMER_FK_USER_I_USER foreign key (customerID) references \

go

alter table employee

add constraint FK_EMPLOYEE_FK_USER_I_USER foreign key (employeeID) references \go

alter table guest

add constraint FK_GUEST_FK_USER_I_USER foreign key (guestID)

references \go

alter table userRole

add constraint FK_USERROLE_FK_ROLE_R_ROLE foreign key (roleID)

references role (roleID) go

alter table userRole

add constraint FK_USERROLE_FK_USER_I_USER foreign key (userID) references \go

go

alter table employee

add constraint FK_EMPLOYEE_FK_USER_I_USER foreign key (employeeID) references \go

alter table guest

add constraint FK_GUEST_FK_USER_I_USER foreign key (guestID)

references \go

alter table userRole

add constraint FK_USERROLE_FK_ROLE_R_ROLE foreign key (roleID)

references role (roleID) go

alter table userRole

add constraint FK_USERROLE_FK_USER_I_USER foreign key (userID) references \go

本文来源：https://www.bwwdw.com/article/nv5g.html