基于角色的权限访问控制数据库设计- cychai的专栏- CSDN博客
更新时间:2024-05-07 04:33:01 阅读量: 综合文库 文档下载
基于角色的权限访问控制数据库设计 - 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
正在阅读:
基于角色的权限访问控制数据库设计- cychai的专栏- CSDN博客05-07
初级会计实务(2012) VIP阶段性测试(第1-3章)试题及答案06-09
小学足球完整教案与方案01-12
二年级下册语文课课知识点一06-18
ITSS第11章服务台管理制度10-07
《数字电子技术基础》实验讲稿11-01
三级贫困证明怎么写02-14
辩论赛:全民微信时代增加人与人之间的交流06-28
冬期回填土施工方案03-08
- 天大砼方案 - 图文
- 农业科技网络书屋能力提升_玉米错题选
- DNS习题
- 浅议检察官对罪犯谈话的技巧与效果
- 高考语文文言文翻译专题训练
- AB类学科竞赛目录(2015)
- 建筑面积计算新规定(2015最新)
- Revit2012初级工程师题集一
- 十三五项目米线可行性报告
- 2013体育学院党组织建设工作总结
- 2014Revit工程师题库
- 高中数学如何实施研究性学习
- 茶艺表演 中英互译
- 小学音乐湘文艺版 四年级下册 第十一课《(歌表演)脚印》优质课公
- 山西省农村合作经济承包合同管理条例
- 2015年镇江市中考化学一模试题参考答案及评分标准(定稿)
- 统计 题集
- 批评意见清单
- 8潞安集团蒲县黑龙关煤矿矿业公司2
- 鄂教版四年级语文上册复习精要(光谷四小)
- 角色
- 权限
- 基于
- 专栏
- 控制
- 数据库
- cychai
- 访问
- 设计
- 博客
- CSDN