基于RBAC的数据库设计
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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