基于角色的访问控制'的权限管理的数据库的设计实现

use [master] 
go 

--檢查數據庫[RBAC]是否存在,如果存在則刪除(只測試用,不然會丟數據.) 
-- Search from the sysdatabase to see that if the [RBAC] database exist. 
-- If exists then drop it else create it. 

if exists(select * from sysdatabases where name = 'RBAC') 
    drop database [RBAC] 
go 

--創建數據庫[RBAC] 
-- Create the database named by '[RBAC]'. 
create database [RBAC] 
go 

--使用數據庫[RBAC] 
-- Use the database of '[RBAC]'. 
use [RBAC] 
go 

--創建"用戶"數據表[RBAC_User] 
-- Create the datatable named by '[RBAC_User]' to save users. 
create table [RBAC_User] 
( 
  --用戶編號 
  [User_ID] int primary key not null, 
  --用戶名稱 
  [User_Name] varchar(20) not null, 
  --用戶密碼 
  [User_PassWord] varchar(20) not null, 
  --用戶狀態 
  [User_Lock] bit not null 
) 
go 

--添加測試數據 
-- Add data for test 
insert into [RBAC_User] values(1,'FightingYang','PassWord',0); 
go 
insert into [RBAC_User] values(2,'Supper3000','Teacher',0); 
go 
insert into [RBAC_User] values(3,'JianzhongLi','Teacher',1); 
go 

select * from [RBAC_User] 
go 


--創建"組"數據表[RBAC_Group] 
-- Create the datatable named by '[RBAC_Group]' to save groups. 
create table [RBAC_Group] 
( 
  --組編號 
  [Group_ID] int primary key not null, 
  --組名稱 
  [Group_Name] varchar(20) not null 
) 
go 

--添加測試數據 
-- Add data for test 
insert into [RBAC_Group] values(1,'編程愛好者'); 
go 
insert into [RBAC_Group] values(2,'MSDN老師'); 
go 

select * from [RBAC_Group] 
go 

   

--創建"角色"數據表[RBAC_Role] 
-- Create the datatable named by '[RBAC_Role]' to save roles. 
create table [RBAC_Role] 
( 
  --角色編號 
  [Role_ID] int primary key not null, 
  --角色名稱 
  [Role_Name] varchar(20) not null 
) 
go 

--添加測試數據 
-- Add data for test 
insert into [RBAC_Role] values(1,'admin'); 
go 
insert into [RBAC_Role] values(2,'user'); 
go 

select * from [RBAC_Role] 
go 

  

--創建"資源"數據表[RBAC_Resource] 
-- Create the datatable named by '[RBAC_Resource]' to save Resources. 
create table [RBAC_Resource] 
( 
  --資源編號 
  [Resource_ID] int primary key not null, 
  --資源名稱 
  [Resource_Name] varchar(20) not null 
) 
go 

--添加測試數據 
-- Add data for test 
insert into [RBAC_Resource] values(1,'音頻'); 
go 
insert into [RBAC_Resource] values(2,'視頻'); 
go 

select * from [RBAC_Resource] 
go 

  

--創建"操作"數據表[RBAC_Operate] 
-- Create the datatable named by '[RBAC_Operate]' to save Operates. 
create table [RBAC_Operate] 
( 
  --操作編號 
  [Operate_ID] int primary key not null, 
  --操作名稱 
  [Operate_Name] varchar(10) not null 
) 
go 

--添加測試數據 
-- Add data for test 
insert into [RBAC_Operate] values(1,'添加'); 
go 
insert into [RBAC_Operate] values(2,'讀取'); 
go 
insert into [RBAC_Operate] values(3,'編寫'); 
go 
insert into [RBAC_Operate] values(4,'刪除'); 
go 

select * from [RBAC_Operate] 
go 

  

--創建"權限"數據表[RBAC_Privilege] 
-- Create the datatable named by [RBAC_Privilege] to save privileges. 
create table [RBAC_Privilege] 
( 
  --權限編號 
  [Privilege_ID] int primary key not null, 
  --資源編號 
  [Resource_ID] int foreign key references [RBAC_Resource]([Resource_ID]) not null, 
  --操作編號 
  [Operate_ID] int foreign key references [RBAC_Operate]([Operate_ID]) not null 
) 
go 

--添加測試數據 
-- Add data for test 

--第一條權限是對"音頻"的"添加"權限 
insert into [RBAC_Privilege] values(1,1,1); 
go 
--第二條權限是對"音頻"的"讀取"權限 
insert into [RBAC_Privilege] values(2,1,2); 
go 
--第三條權限是對"音頻"的"編寫"權限 
insert into [RBAC_Privilege] values(3,1,3); 
go 
--第四條權限是對"音頻"的"刪除"權限 
insert into [RBAC_Privilege] values(4,1,4); 
go 
--第五條權限是對"視頻"的"添加"權限 
insert into [RBAC_Privilege] values(5,2,1); 
go 
--第六條權限是對"視頻"的"讀取"權限 
insert into [RBAC_Privilege] values(6,2,2); 
go 
--第七條權限是對"視頻"的"編寫"權限 
insert into [RBAC_Privilege] values(7,2,3); 
go 
--第八條權限是對"視頻"的"刪除"權限 
insert into [RBAC_Privilege] values(8,2,4); 
go 

select * from [RBAC_Privilege] 
go 

  

--創建"授權"數據表[RBAC_Impower] 
-- Create the datatable named by [RBAC_Impower] to save Impower. 
create table [RBAC_Impower] 
( 
  --授權編號 
  [Impower_ID] int primary key not null, 
  --角色編號 
  [Role_ID] int foreign key references [RBAC_Role]([Role_ID]) not null, 
  --權限編號 
  [Privilege_ID] int foreign key references [RBAC_Privilege]([Privilege_ID]) not null 
) 
go 

--添加測試數據 
-- Add data for test 

--第一條授權內容"admin"具有'對"音頻"的"添加"權限' 
insert into [RBAC_Impower] values(1,1,1); 
go 
--第二條授權內容"admin"具有'對"音頻"的"讀取"權限' 
insert into [RBAC_Impower] values(2,1,2); 
go 
--第三條授權內容"admin"具有'對"音頻"的"編寫"權限' 
insert into [RBAC_Impower] values(3,1,3); 
go 
--第四條授權內容"admin"具有'對"音頻"的"刪除"權限' 
insert into [RBAC_Impower] values(4,1,4); 
go 
--第五條授權內容"admin"具有'對"視頻"的"添加"權限' 
insert into [RBAC_Impower] values(5,1,5); 
go 
--第六條授權內容"admin"具有'對"視頻"的"讀取"權限' 
insert into [RBAC_Impower] values(6,1,6); 
go 
--第七條授權內容"admin"具有'對"視頻"的"編寫"權限' 
insert into [RBAC_Impower] values(7,1,7); 
go 
--第八條授權內容"admin"具有'對"視頻"的"刪除"權限' 
insert into [RBAC_Impower] values(8,1,8); 
go 
--第九條授權內容"user"具有'對"音頻"的"讀取"權限' 
insert into [RBAC_Impower] values(9,2,2); 
go 
--第十條授權內容"user"具有'對"視頻"的"讀取"權限' 
insert into [RBAC_Impower] values(10,2,6); 
go 

select * from [RBAC_Impower] 
--delete from rbac_impower
go 

--創建"授權"數據表[RBAC_Impower] 
-- Create the datatable named by [RBAC_Impower] to save Impower. 
create table [RBAC_GroupRole] 
( 
  --授權編號 
  [GroupRole_ID] int primary key not null, 
  --角色編號 
  [Group_ID] int foreign key references [RBAC_Group]([Group_ID]) not null, 
  --權限編號 
  [Role_ID] int foreign key references [RBAC_Role]([Role_ID]) not null 
) 
go   

--添加測試數據 
-- Add data for test 

--組所具備的角色的數據第一條的內容是"MSDN老師"具有"admin"的角色 
insert into [RBAC_GroupRole] values(1,2,1); 
go 
--組所具備的角色的數據第二條的內容是"編程愛好者"具有"user"的角色 
insert into [RBAC_GroupRole] values(2,1,2); 
go 

select * from [RBAC_GroupRole] 
go 


--創建"用戶組"數據表[RBAC_UserGroupRole] 
-- Create the datatable named by '[RBAC_UserGroupRole]' to save userGroupRoles. 
create table [RBAC_UserGroupRole] 
( 
  --用戶組編號 
  [UserGroup_ID] int primary key not null, 
  --用戶編號 
  [User_ID] int foreign key references [RBAC_User]([User_ID]) not null, 
  --組編號 
  [Group_ID] int foreign key references [RBAC_Group]([Group_ID]) not null, 
  --角色編號 
  [Role_ID] int foreign key references [RBAC_Role]([Role_ID]) not null 
) 
go 

--添加測試數據 
-- Add data for test 

--第一條用戶組數據是"FightingYang"屬於"編程愛好者"組,在組中的角色是"admin" 
insert into [RBAC_UserGroupRole] values(1,1,1,1); 
go 
--第二條用戶組數據是"Supper3000"屬於"MSDN老師"組,在組中的角色是"admin" 
insert into [RBAC_UserGroupRole] values(2,2,2,1); 
go 
--第三條用戶組數據是"JianzhongLi"屬於"MSDN老師"組,在組中的角色是"user" 
insert into [RBAC_UserGroupRole] values(3,3,2,2); 
go 

select * from [RBAC_UserGroupRole] 
go


select rbac_user.user_name,rbac_group.group_name,rbac_role.role_name,rbac_resource.resource_name,rbac_operate.operate_name
from rbac_user 
left join rbac_usergrouprole on rbac_user.user_id=rbac_usergrouprole.user_id
left join rbac_group on rbac_usergrouprole.group_id=rbac_group.group_id
left join rbac_grouprole on rbac_group.group_id=rbac_grouprole.group_id
left join rbac_role on rbac_grouprole.role_id=rbac_role.role_id
left join rbac_impower on rbac_role.role_id=rbac_impower.role_id
left join rbac_privilege on rbac_impower.privilege_id=rbac_privilege.privilege_id
left join rbac_resource on rbac_privilege.resource_id=rbac_resource.resource_id
left join rbac_operate on rbac_privilege.operate_id=rbac_operate.operate_id
where  rbac_user.user_name='Supper3000'

select rbac_user.user_name,rbac_role.role_name,rbac_resource.resource_name,rbac_operate.operate_name
from rbac_user 
left join rbac_usergrouprole on rbac_user.user_id=rbac_usergrouprole.user_id
left join rbac_role on rbac_usergrouprole.role_id=rbac_role.role_id
left join rbac_impower on rbac_role.role_id=rbac_impower.role_id
left join rbac_privilege on rbac_impower.privilege_id=rbac_privilege.privilege_id
left join rbac_resource on rbac_privilege.resource_id=rbac_resource.resource_id
left join rbac_operate on rbac_privilege.operate_id=rbac_operate.operate_id
where  rbac_user.user_name='Supper3000'

轉自:http://www.cnblogs.com/lds85930/archive/2009/02/13/1389655.html