基于RBAC的数据库设计.doc

use mastergo - 检查数据库 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 RBACgo- 创建数据库 RBAC- Create the database named by RBAC.create database RBACgo、- 使用数据库 RBAC- Use the database of RBAC.use RBACgo- 创建 用户 数据表 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 testinsert into RBAC_User values(1,FightingYang,PassWord,0);goinsert into RBAC_User values(2,Supper3000,Teacher,0);goinsert into RBAC_User values(3,JianzhongLi,Teacher,1);goselect * from RBAC_Usergo- 创建 组 数据表 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 testinsert into RBAC_Group values(1,编程爱好者);goinsert into RBAC_Group values(2,MSDN老师);goselect * from RBAC_Groupgo- 创建 角色 数据表 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 testinsert into RBAC_Role values(1,admin);goinsert into RBAC_Role values(2,user);goselect * from RBAC_Rolego- 创建 资源 数据表 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 testinsert into RBAC_Resource values(1,音频);goinsert into RBAC_Resource values(2,视频);goselect * from RBAC_Resourcego- 创建 操作 数据表 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 testinsert into RBAC_Operate values(1,添加);goinsert into RBAC_Operate values(2,读取);goinsert into RBAC_Operate values(3,编写);goinsert into RBAC_Operate values(4,删除);goselect * from RBAC_Operatego- 创建 权限 数据表 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);goselect * from RBAC_Operatego- 创建 授权 数据表 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);go- 第二条授权内容admin具有对音频的读取权限insert into RBAC_Impower values(2,2);go- 第三条授权内容admin具有对音频的编写权限insert into RBAC_Impower values(3,3);go- 第四条授权内容admin具有对音频的删除权限insert into RBAC_Impower values(4,4);go- 第五条授权内容admin具有对视频的添加权限insert into RBAC_Impower values(5,5);go- 第六条授权内容admin具有对视频的读取权限insert into RBAC_Impower values(6,6);go- 第七条授权内容admin具有对视频的编写权限insert into RBAC_Impower values(7,7);go- 第八条授权内容admin具有对视频的删除权限insert into RBAC_Impower values(8,8);go- 第九条授权内容user具有对音频的读取权限insert into RBAC_Impower values(9,2);go- 第十条授权内容user具有对视频的读取权限insert into RBAC_Impower values(10,5);goselect * from RBAC_Impowergo- 添加测试数据- Add data for test- 组所具备的角色的数据第一条的内容是MSDN老师具有admin的角色insert into RBAC_GroupRole values(1,2,1);go- 组所具备的角色的数据第二条的内容是编程爱好者具有user的角色insert into RBAC_GroupRole values(2,1,2);goselect * from RBAC_GroupRolego- 创建 用户组 数据表 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属于编程爱好者组,在组中的角色是admininsert into RBAC_UserGroup values(1,1,1,1);go- 第二条用户组数据是Supper3000