SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Secu_AdminOptItem
(
     @uid      int
,      @OptID int
,      @ObjID int
)

Returns @Secu_AdminOptItem table
(
     ID int Primary Key not null
,      ItemName nvarchar(40)
,      Val int not null
)
as
Begin
     Declare @AdminRightsVal int

     insert @Secu_AdminOptItem select ID , cname , Power( 2 , MaskPos ) from XcmsOptItem where OptID=@OptID

     Select @AdminRightsVal=val from AdminRights where UserType='Administrator' and UserID=@uid and OptID=@OptID and ObjID=@ObjID

     IF @AdminRightsVal is not null
     Begin      
    
           update @Secu_AdminOptItem set Val= 1-( @AdminRightsVal & Val - Val )
           update @Secu_AdminOptItem set Val=0 where Val<>1

     End
     Else
           update @Secu_AdminOptItem set Val=0

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Secu_OptItem
(
     @UserType varchar(20)
,      @uid      int
,      @OptID int
,      @ObjID int
)

Returns @Secu_OptItem table
(
     ID int Primary Key not null
,      ItemName nvarchar(40)
,      Val int not null
)
as
Begin
     Declare @RightsVal int

     insert @Secu_OptItem select ID , cname , Power( 2 , MaskPos ) from XcmsOptItem where OptID=@OptID

     Select @RightsVal=val from AdminRights where UserType=@UserType and UserID=@uid and OptID=@OptID and ObjID=@ObjID

     IF @RightsVal is not null
     Begin      
    
           update @Secu_OptItem set Val= 1-( @RightsVal & Val - Val )
           update @Secu_OptItem set Val=0 where Val<>1

     End
     Else
           update @Secu_OptItem set Val=0

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Func_SplitInt
(
     @InputStr varchar(250)
)

Returns @Func_SplitInt table
(
     val int
)

as
Begin

     Declare @str varchar(250)
     Declare @substr varchar(250)
     Declare @iLen int
     Declare @iStart int
     set @str=RTrim(Ltrim(@InputStr))

     set @iStart=CHARINDEX(  ',' , @str )
     set @iLen=Len( @str )
     IF @iStart>0
     Begin
           set @substr=substring( @str , 1 , @iStart-1 )
           set @str=substring( @str , @iStart+1 , @iLen-@iStart )
     End
     Else
     Begin
           set @substr=@str
           set @str=''
     End

     set @substr=RTRIM( LTRIM( @substr ) )
     insert @Func_SplitInt select id=cast( @substr as int )

     While Len( @str )>0
     Begin
           ---------------- Loop Begin ---------------

     set @iStart=CHARINDEX(  ',' , @str )
     set @iLen=Len( @str )
     IF @iStart>0
     Begin
           set @substr=substring( @str , 1 , @iStart-1 )
           set @str=substring( @str , @iStart+1 , @iLen-@iStart )
     End
     Else
     Begin
           set @substr=@str
           set @str=''
     End

     set @substr=RTRIM( LTRIM( @substr ) )
     insert @Func_SplitInt select id=cast( @substr as int )

           ---------------- Loop End ----------------

     End

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Func_catalog_SplitInt
(
       @id      int
)

Returns @Func_catalog_SplitInt table
(
     val int
)

as

Begin

     Declare @InputStr varchar(250)
     Declare @sql varchar(250)

     select @InputStr=navstr from catalog where id=@id
    
     insert @Func_catalog_SplitInt select * from dbo.Func_SplitInt( @InputStr )
    

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Func_special_SplitInt
(
       @id      int
)

Returns @Func_special_SplitInt table
(
     val int
)

as

Begin

     Declare @InputStr varchar(250)
     Declare @sql varchar(250)

     select @InputStr=navstr from special where id=@id
    
     insert @Func_special_SplitInt select * from dbo.Func_SplitInt( @InputStr )
    

Return
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function dbo.Secu_OptVal
(
     @UserType varchar(20)
,      @UserID int
,      @OptID int
,      @ObjID int
)  
RETURNS int
AS  
Begin

     Declare @RetVal int
     Declare @Obj int
     Declare @Val int
     Declare @SchemaName varchar(20)

     set @RetVal=0

     select @SchemaName=SchemaName from XcmsOpt where id=@OptID
    
     If @SchemaName='catalog'
           Declare Val_cursor cursor for select Val from dbo.Func_catalog_SplitInt( @ObjID )
          
     If @SchemaName='special'
           Declare Val_cursor cursor for select Val from dbo.Func_special_SplitInt( @ObjID )

    
     OPEN Val_cursor

     FETCH NEXT FROM Val_cursor INTO @Obj
     select @Val=val from AdminRights where UserType=@UserType and UserID=@UserID and OptID=@OptID and ObjID=@Obj
     If @Val is not null
           set @RetVal=@Val

     WHILE @@FETCH_STATUS = 0
     Begin

     FETCH NEXT FROM Val_cursor INTO @Obj
     select @Val=val from AdminRights where UserType=@UserType and UserID=@UserID and OptID=@OptID and ObjID=@Obj
     If @Val is not null
           set @RetVal=@Val

     End

     Close Val_cursor
     DEALLOCATE Val_cursor

return( @RetVal )
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

过滤用户输入字符串中的非法字符的函数

不同场合用不同的安全过滤函数。此为　LevA 等级过滤。

在 Xinsoft CMS 中，根据不同的场合定义了不同的用户输入安全等级。

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Create function dbo.Secu_InputFilter_LevA (
      @InputStr nvarchar(250)
)

Returns nvarchar(250)

as

Begin

      Declare @RetVal nvarchar(250)

      Set @RetVal=@InputStr

      Set @RetVal=Lower( @RetVal )
      Set @RetVal=Replace( @RetVal , '''' , '' )
      Set @RetVal=Replace( @RetVal , ';' , '' )
      Set @RetVal=Replace( @RetVal , '--' , '' )
      Set @RetVal=Replace( @RetVal , 'and' , '' )
      Set @RetVal=Replace( @RetVal , 'or' , '' )
      Set @RetVal=Replace( @RetVal , 'declare' , '' )

      RETURN( @RetVal )

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

集成前面的一些功能的最终调用函数

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Function Secu_PowerOptItem
(
     @UserType varchar(20)
,      @uid      int
,      @OptID int
,      @ObjID int
)
Returns @Secu_PowerOptItem table
(
     ID int Primary Key not null
,      ItemName nvarchar(40)
,      Val int not null
)
as
Begin

     Declare @RightsVal int
    
     insert @Secu_PowerOptItem select ID , cname , Power( 2 , MaskPos ) from XcmsOptItem where OptID=@OptID
     select @RightsVal=dbo.Secu_OptVal( @UserType , @uid , @OptID , @ObjID  )

     IF @RightsVal is not null
     Begin
    
           update @Secu_PowerOptItem set Val= 1-( @RightsVal & Val - Val )
           update @Secu_PowerOptItem set Val=0 where Val<>1

     End
     Else
           update @Secu_PowerOptItem set Val=0

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

存储过程：查询某一项子操作的权限

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Create Procedure Proc_Secu_PowerOptItemVal

           @UserType varchar(20)
     ,      @UserID int
     ,      @OptItemID int
     ,      @ObjID int

As

Begin

     Declare @OptID int
     Declare @RetVal int
    
     select @OptID=OptID from XcmsOptItem where ID=@OptItemID
     select @RetVal=val from dbo.Secu_PowerOptItem( @UserType , @UserID , @OptID , @ObjID ) where id=@OptItemID
    
     Return( @RetVal )
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

上一页  [1] [2] [3]