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

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

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><lock v:ext="edit" aspectratio="t"></lock></shapetype><shape id="图片_x0020_1" style="VISIBILITY: visible; WIDTH: 415.5pt; HEIGHT: 287.25pt" o:spid="_x0000_i1025" type="#_x0000_t75"><imagedata src="file:///C:/DOCUME~1/CHAICH~1.ASU/LOCALS~1/Temp/msohtmlclip1/01/clip_image001.jpg" o:title="r_%e6%9d%83%e9%99%90%e7%ae%a1%e7%90%86%e5%af%b9%e8%b1%a1%e5%9b%be"></imagedata></shape>

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

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('"user"')
and type = 'U')
drop table "user"
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: "user" */
/*==============================================================*/
create table "user" (
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 "user" (ID)
go

alter table employee
add constraint FK_EMPLOYEE_FK_USER_I_USER foreign key (employeeID)
references "user" (ID)
go

alter table guest
add constraint FK_GUEST_FK_USER_I_USER foreign key (guestID)
references "user" (ID)
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 "user" (ID)
go