常言笑的家

Spring, Hibernate, Struts, Ajax, RoR

權(quán)限管理的數(shù)據(jù)庫(kù)設(shè)計(jì)

*/

use [master]

go

-- 檢查數(shù)據(jù)庫(kù) [RBAC]是否存在,如果存在則刪除(只測(cè)試用,不然會(huì)丟數(shù)據(jù).)

-- 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

-- 創(chuàng)建數(shù)據(jù)庫(kù) [RBAC]

-- Create the database named by '[RBAC]'.

create database [RBAC]

go

-- 使用數(shù)據(jù)庫(kù) [RBAC]

-- Use the database of '[RBAC]'.

use [RBAC]

go

-- 創(chuàng)建 "用戶" 數(shù)據(jù)表 [RBAC_User]

-- Create the datatable named by '[RBAC_User]' to save users.

create table [RBAC_User]

(

--用戶編號(hào)

[User_ID] int primary key not null,

--用戶名稱

[User_Name] varchar(20) not null,

--用戶密碼

[User_PassWord] varchar(20) not null,

--用戶狀態(tài)

[User_Lock] bit not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- 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

-- 創(chuàng)建 "組" 數(shù)據(jù)表 [RBAC_Group]

-- Create the datatable named by '[RBAC_Group]' to save groups.

create table [RBAC_Group]

(

--組編號(hào)

[Group_ID] int primary key not null,

--組名稱

[Group_Name] varchar(20) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

insert into [RBAC_Group] values(1,'編程愛(ài)好者');

go

insert into [RBAC_Group] values(2,'MSDN老師');

go

select * from [RBAC_Group]

go

-- 創(chuàng)建 "角色" 數(shù)據(jù)表 [RBAC_Role]

-- Create the datatable named by '[RBAC_Role]' to save roles.

create table [RBAC_Role]

(

--角色編號(hào)

[Role_ID] int primary key not null,

--角色名稱

[Role_Name] varchar(20) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- 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

-- 創(chuàng)建 "資源" 數(shù)據(jù)表 [RBAC_Resource]

-- Create the datatable named by '[RBAC_Resource]' to save Resources.

create table [RBAC_Resource]

(

--資源編號(hào)

[Resource_ID] int primary key not null,

--資源名稱

[Resource_Name] varchar(20) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

insert into [RBAC_Resource] values(1,'音頻');

go

insert into [RBAC_Resource] values(2,'視頻');

go

select * from [RBAC_Resource]

go

-- 創(chuàng)建 "操作" 數(shù)據(jù)表 [RBAC_Operate]

-- Create the datatable named by '[RBAC_Operate]' to save Operates.

create table [RBAC_Operate]

(

--操作編號(hào)

[Operate_ID] int primary key not null,

--操作名稱

[Operate_Name] varchar(10) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- 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

-- 創(chuàng)建 "權(quán)限" 數(shù)據(jù)表 [RBAC_Privilege]

-- Create the datatable named by [RBAC_Privilege] to save privileges.

create table [RBAC_Privilege]

(

--權(quán)限編號(hào)

[Privilege_ID] int primary key not null,

--資源編號(hào)

[Resource_ID] int foreign key references [RBAC_Resource]([Resource_ID]) not null,

--操作編號(hào)

[Operate_ID] int foreign key references [RBAC_Operate]([Operate_ID]) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

-- 第一條權(quán)限是對(duì)"音頻"的"添加"權(quán)限

insert into [RBAC_Privilege] values(1,1,1);

go

-- 第二條權(quán)限是對(duì)"音頻"的"讀取"權(quán)限

insert into [RBAC_Privilege] values(2,1,2);

go

-- 第三條權(quán)限是對(duì)"音頻"的"編寫"權(quán)限

insert into [RBAC_Privilege] values(3,1,3);

go

-- 第四條權(quán)限是對(duì)"音頻"的"刪除"權(quán)限

insert into [RBAC_Privilege] values(4,1,4);

go

-- 第五條權(quán)限是對(duì)"視頻"的"讀取"權(quán)限

insert into [RBAC_Privilege] values(5,2,1);

go

-- 第六條權(quán)限是對(duì)"視頻"的"讀取"權(quán)限

insert into [RBAC_Privilege] values(6,2,2);

go

-- 第七條權(quán)限是對(duì)"視頻"的"編寫"權(quán)限

insert into [RBAC_Privilege] values(7,2,3);

go

-- 第八條權(quán)限是對(duì)"視頻"的"刪除"權(quán)限

insert into [RBAC_Privilege] values(8,2,4);

go

select * from [RBAC_Operate]

go

-- 創(chuàng)建 "授權(quán)" 數(shù)據(jù)表 [RBAC_Impower]

-- Create the datatable named by [RBAC_Impower] to save Impower.

create table [RBAC_Impower]

(

--授權(quán)編號(hào)

[Impower_ID] int primary key not null,

--角色編號(hào)

[Role_ID] int foreign key references [RBAC_Role]([Role_ID]) not null,

--權(quán)限編號(hào)

[Privilege_ID] int foreign key references [RBAC_Privilege]([Privilege_ID]) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

-- 第一條授權(quán)內(nèi)容"admin"具有'對(duì)"音頻"的"添加"權(quán)限'

insert into [RBAC_Impower] values(1,1);

go

-- 第二條授權(quán)內(nèi)容"admin"具有'對(duì)"音頻"的"讀取"權(quán)限'

insert into [RBAC_Impower] values(2,2);

go

-- 第三條授權(quán)內(nèi)容"admin"具有'對(duì)"音頻"的"編寫"權(quán)限'

insert into [RBAC_Impower] values(3,3);

go

-- 第四條授權(quán)內(nèi)容"admin"具有'對(duì)"音頻"的"刪除"權(quán)限'

insert into [RBAC_Impower] values(4,4);

go

-- 第五條授權(quán)內(nèi)容"admin"具有'對(duì)"視頻"的"添加"權(quán)限'

insert into [RBAC_Impower] values(5,5);

go

-- 第六條授權(quán)內(nèi)容"admin"具有'對(duì)"視頻"的"讀取"權(quán)限'

insert into [RBAC_Impower] values(6,6);

go

-- 第七條授權(quán)內(nèi)容"admin"具有'對(duì)"視頻"的"編寫"權(quán)限'

insert into [RBAC_Impower] values(7,7);

go

-- 第八條授權(quán)內(nèi)容"admin"具有'對(duì)"視頻"的"刪除"權(quán)限'

insert into [RBAC_Impower] values(8,8);

go

-- 第九條授權(quán)內(nèi)容"user"具有'對(duì)"音頻"的"讀取"權(quán)限'

insert into [RBAC_Impower] values(9,2);

go

-- 第十條授權(quán)內(nèi)容"user"具有'對(duì)"視頻"的"讀取"權(quán)限'

insert into [RBAC_Impower] values(10,5);

go

select * from [RBAC_Impower]

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

-- 組所具備的角色的數(shù)據(jù)第一條的內(nèi)容是"MSDN老師"具有"admin"的角色

insert into [RBAC_GroupRole] values(1,2,1);

go

-- 組所具備的角色的數(shù)據(jù)第二條的內(nèi)容是"編程愛(ài)好者"具有"user"的角色

insert into [RBAC_GroupRole] values(2,1,2);

go

select * from [RBAC_GroupRole]

go

-- 創(chuàng)建 "用戶組" 數(shù)據(jù)表 [RBAC_UserGroupRole]

-- Create the datatable named by '[RBAC_UserGroupRole]' to save userGroupRoles.

create table [RBAC_UserGroupRole]

(

--用戶組編號(hào)

[UserGroup_ID] int primary key not null,

--用戶編號(hào)

[User_ID] int foreign key references [RBAC_User]([User_ID]) not null,

--組編號(hào)

[Group_ID] int foreign key references [RBAC_Group]([Group_ID]) not null,

--角色編號(hào)

[Role_ID] int foreign key references [RBAC_Role]([Role_ID]) not null

)

go

-- 添加測(cè)試數(shù)據(jù)

-- Add data for test

-- 第一條用戶組數(shù)據(jù)是"FightingYang"屬于"編程愛(ài)好者"組,在組中的角色是"admin"

insert into [RBAC_UserGroup] values(1,1,1,1);

go

-- 第二條用戶組數(shù)據(jù)是"Supper3000"屬于"MSDN老師"組,在組中的角色是"admin"

insert into [RBAC_UserGroup] values(2,2,2,1);

go

-- 第三條用戶組數(shù)據(jù)是"JianzhongLi"屬于"MSDN老師"組,在組中的角色是"user"

insert into [RBAC_UserGroup] values(3,3,2,2);

go

select * from [RBAC_UserGroupRole]

go

posted on 2010-07-06 15:54 常言笑閱讀(2836) 評(píng)論(1) 編輯收藏所屬分類: 數(shù)據(jù)庫(kù)

Feedback

# re: 權(quán)限管理的數(shù)據(jù)庫(kù)設(shè)計(jì) 2012-07-17 21:33 zkc

最下面的組角色那里好像有點(diǎn)問(wèn)題啊最好還能說(shuō)說(shuō)數(shù)據(jù)庫(kù)的具體使用回復(fù) 更多評(píng)論

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問(wèn) 管理
相關(guān)文章: 權(quán)限管理的數(shù)據(jù)庫(kù)設(shè)計(jì) Sql server 2005 版本數(shù)據(jù)倉(cāng)庫(kù)初探實(shí)時(shí)數(shù)據(jù)庫(kù)的簡(jiǎn)介 db2數(shù)使用幫助總結(jié) Hibernate實(shí)踐數(shù)據(jù)庫(kù)常見(jiàn)問(wèn)題集錦 Hibernate的延遲加載問(wèn)題 Hibernate的數(shù)據(jù)緩存 hibernate二級(jí)緩存攻略

常言笑的家

權(quán)限管理的數(shù)據(jù)庫(kù)設(shè)計(jì)

Feedback

# re: 權(quán)限管理的數(shù)據(jù)庫(kù)設(shè)計(jì) 2012-07-17 21:33 zkc

My Links

Blog Stats

常用鏈接

留言簿(5)

隨筆分類

隨筆檔案

搜索

積分與排名

最新評(píng)論

閱讀排行榜

評(píng)論排行榜