角色权限设计解决方案(sql脚本)

一、表的说明1具权限项掩码表：包括字段 ID，权限类别，权限明细，权限码，下属权限码 等权限码 用二进制数为标志 如1 2 4 8 16等下属权限码 是该权限所包含的统一权限类别的其他的权限 用二进制数表示权限明细与权限码保持一致 但是用字符串表示 如edit delect 等权限类别是某一功能的表示 如client 等 2 角色表：包括字段，ID，角色名，角色的描述 等 3权限表：ID，权限类别，权限码总和权限类别 跟权限表的权限类别关联权限码总和 是指同一权限类别的权限总和（用二进制表示权限码的意义就是能够通过权限码的总和可以分出具体的那些权限）4 用户表：包括三个或以上字段，ID，用户名，对该用户的描述，其它（如地址、电话等信息）； 5用户部门表：包含 ID，部门编号，部门名称 等 6部门-角色-用户表 ID，部门ID，角色ID，用户ID用来解决一个用户多部门多角色的问题 同一个职员在一个部门也可以拥有多个角色如 用户1-部门1-角色1 用户1-部门2-角色1 用户1-部门3-角色2等等 二、程序解决方法1具权限项掩码表GOif exists (select 1 from sysobjects where id = object_id('t_Object') and type = 'U') drop table t_ObjectGOcreate table t_Object( FObjectno varchar(40) not null, FObjectgroup varchar(40) not null, FObjectorder int not null, FSysID varchar(40) not null default ('system'), F0bjectdetail nvarchar(40) not null default (''), FAccessmask bigint not null default ((0)), FAccessinclude bigint not null default ((0)), FServicetype nvarchar(20) not null default (''), FObjectid bigint not null, constraint [PK_t_Object] primary key clustered ( FSysID asc, Fobjectid asc)with (ignore_dup_key = off) on [primary]) on [primary]GO2 角色表GOif exists (select 1 from sysobjects where id = object_id('t_Role') and type = 'U') drop table t_RoleGOcreate table t_Role( FRoleID bigint not null, FSysID nvarchar(40) not null, FRoleName nvarchar(50) not null, FRoleType nvarchar(40) not null, FRoleNO nvarchar(40) null, FDesc nvarchar(40) null constraint [PK_t_Role] primary key clustered ( FRoleID ASC, FSysID ASC)with (ignore_dup_key = off) on [primary]) on [primary]GO3权限表GOif exists (select 1 from sysobjects where id = object_id('t_RoleRelation') and type = 'U') drop table t_RoleRelationGOcreate table t_RoleRelation( FRoleID bigint not null, FSysID nvarchar(40) not null, FObjectNO nvarchar(40) not null, FAccessMask bigint null constraint [PK_t_RoleRelation] primary key clustered ( FRoleID ASC, FSysID ASC, FObjectNO ASC)with (ignore_dup_key = off) on [primary]) on [primary]GO4 用户表GOcreate table t_Employee( FEmpID bigint not null, FEmpNO nvarchar(50) not null, FEmpName nvarchar(50) not null, FEmpType smallint not null, FSysID varchar(40) not null, FDeptID bigint null DEFAULT ((-1)), FSex nchar(1) null DEFAULT (''), FBirthday datetime null, FMobile nvarchar(50) null, FEmail nvarchar(100) null, FAllowSMS bit not null DEFAULT ('True'), constraint [PK_t_Employee] primary key clustered ( FEmpID ASC, FSysID ASC)with (ignore_dup_key = off) on [primary]) on [primary]GO5用户部门表GOcreate table t_Department ( FDeptID bigint not null, FDeptNo nvarchar(50) not null, FDeptName nvarchar(50) not null, FDesc nvarchar(255) null, FSysID varchar(40) not null, constraint [PK_t_Department] primary key clustered ( FDeptID ASC, FSysID ASC)with (ignore_dup_key = off) on [primary]) on [primary]GO6部门-角色-用户表复制代码GOif exists (select 1 from sysobjects where id = object_id('t_RoleAccess') and type = 'U') drop table t_RoleAccessGOcreate table t_RoleAccess( FRoleAccessID bigint not null, FCoid nvarchar(40) not null, FRoleID bigint not null, FBuID bigint not null, FDeptID bigint not null, FEmpID bigint not null, FHidTag smallint null, FDelTag smallint null, FState smallint null constraint [PK_t_RoleAccess] primary key clustered ( FRoleAccessID ASC, FCoid ASC)with (ignore_dup_key = off) on [primary]) on [primary]GO