中南大学信息科学与工程学院《数据库课程设计》题目:基于RBAC模型的贵重仪器管理系统姓名: 董嘉伟班级: 物联网工程1002班学号: 0909103303时间: 2013年7月5日目录●问题描述●系统需求分析⏹需求描述⏹系统功能结构⏹数据流图●数据库设计⏹全局ER⏹数据字典⏹数据库内关系表定义●数据库实现⏹数据库创建SQL代码⏹关系表及相关视图创建SQL代码⏹存储过程、触发器等创建SQL代码●RBAC应用场景描述⏹场景1-场景5描述●心得体验●总结一、问题描述每个贵重仪器对于各个高校都是一笔巨大的财产,除了本身的物品价值之外,还在于其巨大的使用价值。

由于部分科学实验或人文实验需要更加精确的实验结果和实验推算，大型精密的珍贵仪器成为每个研究者的最有力的帮助。

但是目前在仪器的管理和共享方面要做到规范、有序、高效方面还存在一定困难，例如用户使用权限、借出维护等。

除此之外，虽然大量数据库实现基于角色的访问控制功能，然而却没有对其特征集达成一致。

缺乏广为接受的模型，导致了对基于角色的访问控制效用和含义理解的不规范性和不确定性。

基于以上情况，为了解决仪器管理和共享上遇到的实际困难，设计了一套基于RBAC模型的贵重仪器管理系统，使贵重实验仪器的管理和共享更加信息化、智能化，使仪器的使用更加高效，价值得到了更高的体现。

二、系统需求分析a)需求描述i.贵重仪器管理负责对贵重仪器的进入仓库、借出、归还、保修以及其他功能的管理。

1.仓库管理员负责贵重仪器的进入仓库、保修的信息的维护，并且可修改贵重仪器的可借出状态。

2.老师、学生可以对贵重仪器借出、并在规定时间内归还。

3.仓库管理员针对老师、学生对于贵重仪器的请求可以做出拒绝、接收的回应。

ii.用户和权限管理实现对系统用户以及不同角色的权限的管理1．具有特定权限的管理员可以创建、删除系统用户2．用户可以在登录系统后修改自己的相应资料3．每个用户在创建时须赋于相应角色4．每个角色在被创建时可以赋于相应权限，其所具有的权限可以修改5．角色创建后可以修改、删除6．默认的系统角色：系统管理员仓库管理员学生教师b）系统功能结构系统结构图应用界面模块主要面向用户完成相应操作，并收集操作相关信息，向上提交给逻辑处理模块。

逻辑处理模块收集应用界面模块返还的信息，并判断数据的真实性和合法性，将合法数据封装好后提交给传输模块。

本地管理模块和网络管理模块针对系统使用的两种不同环境，通过与数据库连接池获取数据库连接后，将相关信息传输到数据库进行操作。

数据库系统结构图C) 数据流图三、数据库设计a)ER图设计b) 数据字典表名:UsersAssign字段名数据类型允许空说明Staff_RoleASID Nchar(10) X 用户分配角色标示StaffID Nchar(10) X 用户IDRoleID Nchar(10) X 角色ID IsActive Bit X 标示该用户已分配角色表名:Staff_USERS字段名数据类型允许空说明StaffID Nchar(10) X 用户IDName Nchar(10) X 用户姓名Sex Nchar(6) V 用户性别Birthday Nchar(10) V 用户出生日期College Nchar(10) V 用户所在学院Password Nchar(10) X 用户登录密码IsLocked bit X 用户限制登录标示表名:ROLES字段名数据类型允许空说明RoleID Nchar(10) X 角色ID RoleDesc Nchar(25) V 角色描述RoleName Nchar(10) V 角色名称表名:PermissionAssign字段名数据类型允许空说明RoleID Nchar(10) X 角色ID PRDesc Nchar(20) V 权限分配说明PermissionID Nchar(10) X 权限ID表名:Permission字段名数据类型允许空说明PermissionID Nchar(10) X 权限ID PermissionDesc Nchar(10) V 权限说明OPSID Nchar(10) X 操作ID OBJSID Nchar(10) X 对象ID表名:Operation_OPS字段名数据类型允许空说明OperationID Nchar(10) X 操作ID OperationDesc Nchar(10) V 操作说明Operation Nchar(10) V 相关的数据库操作表名:Equipments_OBJS字段名数据类型允许空说明EquipID Nchar(10) X 仪器IDArea Nchar(10) V 仪器应用领域Birthdate Nchar(10) V 仪器生产日期BirthAddress Nchar(10) V 生产地址EquipDesc Nchar(10) V 仪器描述Islocked Bit X 仪器可否借出表名:EquipManage字段名数据类型允许空说明EquipID Nchar(10) X 仪器ID StaffID Nchar(10) X 仪器借出用户LastOut date X 仪器借出时间LastIn date V 仪器归还时间C)数据库内关系图定义四、数据库实现a)数据库创建代码USE [master]CREATE DATABASE [ExpensiveEquipment]CONTAINMENT = NONEON PRIMARY( NAME = N'ExpensiveEquipment', FILENAME =N'G:\sqlsever\MSSQL11.MSSQLSERVER\MSSQL\DATA\ExpensiveEquipment.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON( NAME = N'ExpensiveEquipment_log', FILENAME =N'G:\sqlsever\MSSQL11.MSSQLSERVER\MSSQL\DATA\ExpensiveEquipment_log.l df' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE [ExpensiveEquipment] SET COMPATIBILITY_LEVEL = 110GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [ExpensiveEquipment].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [ExpensiveEquipment] SET ANSI_NULL_DEFAULT OFFGOALTER DATABASE [ExpensiveEquipment] SET ANSI_NULLS OFFGOALTER DATABASE [ExpensiveEquipment] SET ANSI_PADDING OFFGOALTER DATABASE [ExpensiveEquipment] SET ANSI_WARNINGS OFFGOALTER DATABASE [ExpensiveEquipment] SET ARITHABORT OFFGOALTER DATABASE [ExpensiveEquipment] SET AUTO_CLOSE OFFGOALTER DATABASE [ExpensiveEquipment] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [ExpensiveEquipment] SET AUTO_SHRINK OFFGOALTER DATABASE [ExpensiveEquipment] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [ExpensiveEquipment] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [ExpensiveEquipment] SET CURSOR_DEFAULT GLOBALALTER DATABASE [ExpensiveEquipment] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [ExpensiveEquipment] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [ExpensiveEquipment] SET QUOTED_IDENTIFIER OFFGOALTER DATABASE [ExpensiveEquipment] SET RECURSIVE_TRIGGERS OFFGOALTER DATABASE [ExpensiveEquipment] SET DISABLE_BROKERGOALTER DATABASE [ExpensiveEquipment] SETAUTO_UPDATE_STATISTICS_ASYNC OFFGOALTER DATABASE [ExpensiveEquipment] SETDATE_CORRELATION_OPTIMIZATION OFFGOALTER DATABASE [ExpensiveEquipment] SET TRUSTWORTHY OFFGOALTER DATABASE [ExpensiveEquipment] SET ALLOW_SNAPSHOT_ISOLATION OFFGOALTER DATABASE [ExpensiveEquipment] SET PARAMETERIZATION SIMPLEGOALTER DATABASE [ExpensiveEquipment] SET READ_COMMITTED_SNAPSHOT OFFGOALTER DATABASE [ExpensiveEquipment] SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE [ExpensiveEquipment] SET RECOVERY FULLGOALTER DATABASE [ExpensiveEquipment] SET MULTI_USERGOALTER DATABASE [ExpensiveEquipment] SET PAGE_VERIFY CHECKSUMGOALTER DATABASE [ExpensiveEquipment] SET DB_CHAINING OFFGOALTER DATABASE [ExpensiveEquipment] SETFILESTREAM( NON_TRANSACTED_ACCESS = OFF )GOALTER DATABASE [ExpensiveEquipment] SET TARGET_RECOVERY_TIME = 0 SECONDSGOEXEC sys.sp_db_vardecimal_storage_format N'ExpensiveEquipment', N'ON'b)数据表创建代码USE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[EquipManage]( [EquipID] [nchar](10) NULL,[StaffID] [nchar](10) NULL,[LastOut] [datetime] NULL,[LastIn] [datetime] NULL) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Equipments_OBJS]( [EquipID] [nchar](10) NOT NULL,[Area] [nchar](10) NULL,[BirthDate] [nchar](10) NULL,[Cost] [nchar](10) NULL,[BirthAddress] [nchar](10) NULL,[EquipDesc] [nchar](10) NULL,[Locked] [bit] NULL) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Operation_OPS]( [OperationID] [nchar](10) NOT NULL,[OperationDesc] [nchar](10) NOT NULL,[Operation] [nchar](10) NOT NULL) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Permission]([PermissionID] [nchar](10) NULL,[PermissionDesc] [nchar](10) NULL,[OPSID] [nchar](10) NULL,[OBJSID] [nchar](10) NULL) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[PermissionsAssign]( [RoleID] [nchar](10) NOT NULL,[PRDesc] [nchar](20) NOT NULL,[PermissionID] [nchar](10) NOT NULL ) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[ROLES]([RoleID] [nchar](10) NOT NULL,[RolesDesc] [nchar](10) NULL,[RoleName] [nchar](10) NULL,CONSTRAINT [PK_ROLES] PRIMARY KEY CLUSTERED([RoleID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Staff_USERS]([StaffID] [nchar](10) NOT NULL,[Name] [nchar](10) NOT NULL,[Sex] [nchar](10) NULL,[Birthday] [nchar](10) NULL,[Password] [nchar](10) NULL,[College] [nchar](10) NULL,[IsLocked] [bit] NOT NULL,CONSTRAINT [PK_Staff_USERS] PRIMARY KEY CLUSTERED([StaffID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOUSE [ExpensiveEquipment]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[UsersAssign]([Staff_RoleAsID] [nchar](10) NULL,[StaffID] [nchar](10) NULL,[RoleID] [nchar](10) NULL,[IsActive] [bit] NULL) ON [PRIMARY]GOC)存储过程以及触发器创建代码SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[AddPermission]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@newPerID nchar(10),@newPerDesc n char(10),@newOPSID nchar(10),@newOBJSID nchar(10)ASDeclare @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits=(select count(*) from Permission where @newPerID=PermissionID and @newPerDesc =PermissionDesc)if @exits<1begininsert into Permission values(@newPerID,@newPerDesc,@newOPSID,@newOBJSID)return 1endelse return 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONCREATE PROCEDURE [dbo].[AddRole]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@newRoleID nchar(10),@newRoleDesc nchar(10),@newRoleName nchar(10)ASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @EXITS=(select count(*) from ROLES where @newRoleID=RoleID and @newRoleName=RoleName and @newRoleDesc=RolesDesc )if @EXITS<1begininsert into ROLES values(@newRoleID,@newRoleDesc,@newRoleName)return 1endelse return 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[AddUser]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@newID nchar(010),@newName nchar(10),@newSex nchar(10),@newBirthday nchar(10),@newPassword nchar(10),@newCollege nchar(10),@newIsLocked bitASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits=(select count(*) from Staff_USERS where @newID=StaffID)if @exits<1begininsert into Staff_USERS values(@newID,@newName,@newSex,@newBirthday,@newPassword,@newCollege,@newIsL ocked)return 1endelse return 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[AssignUser]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@newUAID nchar(10),@newstaffid nchar(10),@newroleid nchar(10)ASDECLARE @COUNT INT,@EXITS INTBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @EXITS=(select count(*) from UsersAssign where @newstaffid=StaffID and @newroleid=RoleID )if @EXITS<1begininsert into UsersAssign values(@newUAID,@newstaffid,@newroleid,0)return 1endelsereturn 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[CancleUserAssign]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@cstaffid nchar(10),@croleid nchar(10)ASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits =(select count(*) from UsersAssign where @cstaffid=StaffID and @croleid=RoleID)if @exits>0begindelete from UsersAssign where @cstaffid=StaffID and @croleid=RoleIDreturn 1endelse return 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[CreateSession]@StaffID nchar(10),@ROLE nchar(10),@uaid nchar(10) outputASDECLARE@value as int set @value=0BEGINselect @value =(select count(*) from UsersAssign where @StaffID =StaffID and @ROLE =RoleID )IF@value>=1BEGINupdate UsersAssign set IsActive = 1 where @StaffID=StaffID and @ROLE=RoleIDselect @uaid=(Select Staff_RoleAsID from UsersAssign where @StaffID=StaffID and @ROLE=RoleID)return 1ENDELSERETURN 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeleteEquip]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@EquipID nchar(10)ASDECLARE @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0begindelete from Equipments_OBJS where @EquipID=EquipIDreturn 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeletePermission]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@PermissionID nchar(10)ASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits =(select count(*) from Permission where @PermissionID=PermissionID) if @exits>0begindelete from Permission where PermissionID=@PermissionIDreturn 1endelse return 0endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeleteRole]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@RoleID nchar(10)ASDECLARE @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0begindelete from ROLES where @RoleID=RoleIDreturn 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeleteSession]@StaffID nchar(10),@Role nchar(10)ASBEGINIF (select count(*) from UsersAssign where @StaffID=StaffID and @Role =RoleID)>=1 Beginif (select IsActive from UsersAssign where @StaffID=StaffID and @Role =RoleID)='1' Beginupdate UsersAssign set IsActive='0' where @StaffID=StaffID and @Role =RoleIDreturn 1endelsereturn 0endelsereturn 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeleteUser]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@deleteStaffID nchar(10)ASDECLARE @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0begindelete from Staff_USERS where @deleteStaffID=StaffIDreturn 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[EquipInBound]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@EquipID nchar(10),@Area nchar(10),@BirthDate nchar(10),@cost nchar(10),@BirthAddress nchar(10),@EquipDesc nchar(10),@Locked bitASDECLARE @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0begininsert into Equipments_OBJS values (@EquipID,@Area,@BirthDate,@cost,@BirthAddress,@EquipDesc,@Locked) return 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[HavePermission]@UAID nchar(10),@OPSID nchar(10),@Count int outputASDeclare@RoleID as nchar(10)BEGINselect @RoleID=(select RoleID from UsersAssign where Staff_RoleAsID=@UAID)select @Count =(select count(*) from Permission where PermissionID in ((select PermissionID from Permission where OPSID=@OPSID) Intersect( select PermissionsAssign.PermissionID from PermissionsAssign where @RoleID=RoleID)))IF @Count > 0Return 1ELSE return 0ENDSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InitUse]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@count int outputASDeclare @uaid nchar(10)BEGINexecute dbo.CreateSession @StaffID,@Role,@uaid outputexecute dbo.HavePermission @uaid, @OPSID, @count outputif @count >0print '初始化完毕，即将执行操作'elseprint '权限不足，无法完成操作'ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[LoanEquipHigh]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@EquipID nchar(10)ASDeclare @uaid nchar(10) ,@count int,@IsLocked bit,@date datetimeBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputselect @IsLocked =(select Locked from Equipments_OBJS where EquipID=@EquipID) select @date=GETDA TE()if @count >0 and @IsLocked <1begininsert into EquipManage values(@EquipID,@StaffID,@date,null)return 1endelse return 0ENDSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[LoanEquipLOW]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@EquipID nchar(10)ASDeclare @uaid nchar(10) ,@count int,@IsLocked bit,@date datetimeBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputselect @IsLocked =(select Locked from Equipments_OBJS where EquipID=@EquipID)select @date=GETDA TE()if @count >0 and @IsLocked <1begininsert into EquipManage values(@EquipID,@StaffID,@date,null)return 1endelse return 0ENDGO/****** Object: StoredProcedure [dbo].[Login] Script Date: 2013/7/6 15:02:08 ******/ SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[Login]@StaffID nchar(10),@Password nchar(10)ASDECLARE@IsLocked as bit set @IsLocked=(select IsLocked from Staff_USERS where @StaffID=StaffID ) BEGINIF @IsLocked < 1RETURN -1ELSERETURN (Select Count(*) from Staff_USERS where @StaffID=StaffID and @Password=Password)ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[QueryAssignedUser]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10)ASdeclare @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count output if @count>0beginselect * from UsersAssignreturn 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[QueryEqupMannage]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10)ASdeclare @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count output if @count>0beginselect * from EquipManagereturn 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[QueryUserPermission]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@qroleid nchar(10)ASdeclare @count intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect * from Permission where PermissionID in (select PermissionID from PermissionsAssign where @qroleid=RoleID)return 1endelse return 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[ReturnEquip]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@EquipID nchar(10)ASDECLARE @uaid nchar(10) ,@count int,@IsLocked bit,@date datetime,@IsLoaned nchar(19) BEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputselect @date=GETDA TE()select @IsLoaned =(select Count(*) from EquipManage where @StaffID=StaffID and LastIn is null)if @count >0 and @IsLoaned >0beginupdate EquipManage set LastIn=@datereturn 1endelsereturn 0ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[UpdateEquipState]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@upequipid nchar(10),@state bitASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits =(select count(*) from Equipments_OBJS where @upequipid=EquipID) if @exits>0beginupdate Equipments_OBJS set Locked=@statereturn 1endelse return 0endelse return 0endGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[UpdateUserState]@StaffID nchar(10),@Role nchar(10),@OPSID nchar(10),@upstaffid nchar(10),@state bitASDECLARE @count int,@exits intBEGINexecute dbo.InitUse @StaffID,@Role,@OPSID,@count outputif @count>0beginselect @exits =(select count(*) from Staff_USERS where @upstaffid=StaffID) if @exits>0beginupdate Staff_USERS set IsLocked=@statereturn 1endelse return 0endelse return 0endGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[EquipManage]([EquipID] [nchar](10) NULL,[StaffID] [nchar](10) NULL,[LastOut] [datetime] NULL,[LastIn] [datetime] NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Equipments_OBJS]([EquipID] [nchar](10) NOT NULL,[Area] [nchar](10) NULL,[BirthDate] [nchar](10) NULL,[Cost] [nchar](10) NULL,[BirthAddress] [nchar](10) NULL,[EquipDesc] [nchar](10) NULL,[Locked] [bit] NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Operation_OPS]([OperationID] [nchar](10) NOT NULL,[OperationDesc] [nchar](10) NOT NULL,[Operation] [nchar](10) NOT NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Permission]([PermissionID] [nchar](10) NULL,[PermissionDesc] [nchar](10) NULL,[OPSID] [nchar](10) NULL,[OBJSID] [nchar](10) NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[PermissionsAssign]( [RoleID] [nchar](10) NOT NULL,[PRDesc] [nchar](20) NOT NULL,[PermissionID] [nchar](10) NOT NULL ) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[ROLES]([RoleID] [nchar](10) NULL,[RolesDesc] [nchar](10) NULL,[RoleName] [nchar](10) NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Staff_USERS]([StaffID] [nchar](10) NOT NULL,[Name] [nchar](10) NULL,[Sex] [nchar](10) NULL,[Birthday] [nchar](10) NULL,[Password] [nchar](10) NULL,[College] [nchar](10) NULL,[IsLocked] [bit] NOT NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[UsersAssign]([Staff_RoleAsID] [nchar](10) NULL,[StaffID] [nchar](10) NULL,[RoleID] [nchar](10) NULL,[IsActive] [bit] NULL) ON [PRIMARY]GOUSE [master]GOALTER DATABASE [ExpensiveEquipment] SET READ_WRITEGO五、RBAC应用场景描述a)场景一系统管理员对违规操作的用户进行限制登录操作调用存储过程UpdateUserState后，传入参数依次为00001，role1,20,0909103303,1此存储过程使用了用户ID为00001的系统管理员权限role1，执行的操作为操作ID20的修改用户状态，经验证获得权限后，将用户ID为0909103303限制登录。