我对简单权限应用的研究

来源：互联网发布：淘宝店货款要15天到账编辑：程序博客网时间：2024/05/22 08:13

需要用的表和关系

--网站业务分类和功能对照表
USE [sui211]
GO
/****** 对象: Table [dbo].[BITSWEBPAGE] 脚本日期: 05/07/2009 16:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BITSWEBPAGE](
[PAGENO] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[HREFURL] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PAGEMEMO] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[PAGENAME] [varchar](60) COLLATE Chinese_PRC_CI_AS NOT NULL,
[HAVECHILD] [numeric](1, 0) NOT NULL,
[PARENTPAGENO] [numeric](6, 0) NULL,
CONSTRAINT [PK_BITSWEBPAGE] PRIMARY KEY CLUSTERED
(
[PAGENO] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--组织机构表
USE [sui211]
GO
/****** 对象: Table [dbo].[OrgCata] 脚本日期: 05/07/2009 16:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrgCata](
[OrgId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[OrganizeNo] [numeric](18, 0) NULL,
[OrgName] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[OrgLevel] [int] NULL,
[OrgRoot] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[OrgParentID] [numeric](18, 0) NOT NULL,
[OrgCreateTime] [datetime] NULL CONSTRAINT [DF_OrgCata_OrgCreateTime] DEFAULT (getdate()),
[Orgstate] [tinyint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--角色表

USE [sui211]
GO
/****** 对象: Table [dbo].[ORGROLE] 脚本日期: 05/07/2009 16:28:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ORGROLE](
[ROLENO] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[ROLENAME] [varchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ROLEMEMO] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[HAVECHILD] [numeric](1, 0) NOT NULL,
[STATUS] [numeric](1, 0) NOT NULL,
[issuper] [tinyint] NULL CONSTRAINT [DF_ORGROLE_issuper] DEFAULT (0)
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[defaultValue]', @objname=N'[dbo].[ORGROLE].[HAVECHILD]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[defaultValue]', @objname=N'[dbo].[ORGROLE].[STATUS]' , @futureonly='futureonly'

--角色和业务之间的对应关系

USE [sui211]
GO
/****** 对象: Table [dbo].[ROLENOPRIVE] 脚本日期: 05/07/2009 16:29:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ROLENOPRIVE](
[ROLENO] [numeric](6, 0) NOT NULL,
[PAGENO] [numeric](6, 0) NOT NULL
) ON [PRIMARY]

USE [sxnz]
GO
/****** 对象: Table [dbo].[USERS] 脚本日期: 05/08/2009 15:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[USERS](
[USERNAME] [varchar](18) COLLATE Chinese_PRC_CI_AS NULL,
[MOBILE] [varchar](18) COLLATE Chinese_PRC_CI_AS NULL,
[STATUS] [varchar](1) COLLATE Chinese_PRC_CI_AS NULL,
[UPASSWORD] [varchar](18) COLLATE Chinese_PRC_CI_AS NULL,
[SEX] [varchar](18) COLLATE Chinese_PRC_CI_AS NULL,
[INDATE] [datetime] NULL CONSTRAINT [DF_USERS_INDATE] DEFAULT (getdate()),
[OUTDATE] [datetime] NULL,
[ISMANAGER] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[USERTYPE] [int] NULL,
[ID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[ORGANIZENO] [numeric](8, 0) NULL,
[UserAutho1] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[UserAutho2] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[userOrgOwer] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[chinaName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[buMen] [int] NULL,
CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE    Proc [dbo].[AddOrgCata] -- exec AddOrgCata 1,1,'test',''
@ParentID int ,@cataName varchar(300)
as
begin
declare @CurrentID int,@level int
declare @RootId varchar(20)
insert into Orgcata(OrgName,OrgParentId) values(@cataName,@ParentID)
select @CurrentID=@@identity
if @parentID=0
begin
  update Orgcata set Orgroot=@CurrentID,Orglevel=1 where Orgid=@CurrentID
end
else
begin
  select @level=Orglevel,@rootId=Orgroot from Orgcata where Orgid=@ParentID
  set @level=@level+1
  set @RootId=@RootId+','+convert(varchar(20),@CurrentID)
  update Orgcata set Orgroot=@rootid,Orglevel=@level where Orgid=@CurrentID
end
end

--权限用到得表值函数

create   FUNCTION [dbo].[f_pageno](@pageno varchar(6))
RETURNS @t_Level TABLE(pageno varchar(6),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @pageno,@Level
WHILE @@ROWCOUNT>0
BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.pageno,@Level
  FROM bitswebpage a,@t_Level b
  WHERE a.Parentpageno=b.pageno
   AND b.Level=@Level-1
END
RETURN
END

create FUNCTION [dbo].[f_OrganizeNo](@organizeno varchar(6))
RETURNS @t_Level TABLE(organizeno varchar(6),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @organizeno,@Level
WHILE @@ROWCOUNT>0
BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.organizeno,@Level
  FROM orgorganize a,@t_Level b
  WHERE a.ParentOrganizeno=b.organizeno
   AND b.Level=@Level-1
END
RETURN
END

--select * from [dbo].[f_split]('12,12,23,23,43,',',')

CREATE   function   [dbo].[f_split](@c   varchar(2000),@split   varchar(2))
returns   @t   table(id int identity(1,1),col int)
as
    begin

      while(charindex(@split,@c)<>0)
        begin
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')
        end
      insert   @t(col)   values   (@c)
      return
    end

--权限系统用的标量值函数-----------------------------------------------------

CREATE   FUNCTION [dbo].[get_pageno](@pageno int,@roleno int)
RETURNS int
AS
BEGIN
declare @IsChecked int
if exists(select 1 from (select pageno from rolenoprive
where roleno=@roleno) a where pageno=@pageno)
  set @IsChecked=1
else
  set @IsChecked=0
RETURN @IsChecked
END

create FUNCTION [dbo].[getRootID](@rootid varchar(10))
RETURNS varchar(10)
AS
BEGIN

   if CHARINDEX(',',@rootid)>0
   begin

          set @rootid=SUBSTRING(@rootid,1,CHARINDEX(',',@rootid)-1)
   end

RETURN (@rootid)
END

CREATE FUNCTION [dbo].[find_regular_expression]
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写，默认为false
)
RETURNS bit --返回结果0-false，1-true
AS
BEGIN

--0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
DECLARE @hr integer

--用于保存返回的对象令牌，以便之后对该对象进行操作
DECLARE @objRegExp integer DECLARE @objMatches integer

--保存结果
DECLARE @results bit

/*
创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate,并确定机器中有VBScript.RegExp类库
*/
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
/*
以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：
    Dim regEx,Match,Matches         '建立变量。
    Set regEx = New RegExp         '建立一般表达式。
    regEx.Pattern= patrn         '设置模式。
    regEx.IgnoreCase = True         '设置是否区分大小写。
    regEx.Global=True                             '设置全局可用性。
    set Matches=regEx.Execute(string)             '重复匹配集合
    RegExpTest = regEx.Execute(strng)      '执行搜索。
   for each match in matches                    '重复匹配集合
   RetStr=RetStr &"Match found at position "
   RetStr=RetStr&Match.FirstIndex&".Match Value is '"
   RetStr=RetStr&Match.Value&"'."&vbCRLF Next
   RegExpTest=RetStr

*/
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
--调用对象方法
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
--释放已创建的 OLE 对象
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
END
RETURN @results
END