权限管理----用户与模块关系

用户与模块关系功能，您可以写成跟用户与角色关系一样。不过下面实现方法会看到更多的权限控制。

介面如下截图：

 

数据表结构如下：

代码

SET  ANSI_NULLS  ON
GO

SET  QUOTED_IDENTIFIER  ON
GO

CREATE   TABLE   [ dbo ] . [ UsersModule ] (
     [ UsersId ]   [ int ]   NOT   NULL ,
     [ ModuleId ]   [ smallint ]   NOT   NULL ,
     [ Purview ]   [ int ]   NOT   NULL
)  ON   [ PRIMARY ]

GO

ALTER   TABLE   [ dbo ] . [ UsersModule ]    WITH   CHECK   ADD    CONSTRAINT   [ FK_UsersModule_Module ]   FOREIGN   KEY ( [ ModuleId ] )
REFERENCES   [ dbo ] . [ Module ]  ( [ ModuleId ] )
ON   UPDATE   CASCADE
ON   DELETE   CASCADE
GO

ALTER   TABLE   [ dbo ] . [ UsersModule ]   CHECK   CONSTRAINT   [ FK_UsersModule_Module ]
GO

ALTER   TABLE   [ dbo ] . [ UsersModule ]    WITH   CHECK   ADD    CONSTRAINT   [ FK_UsersModule_Users ]   FOREIGN   KEY ( [ UsersId ] )
REFERENCES   [ dbo ] . [ Users ]  ( [ UsersId ] )
ON   UPDATE   CASCADE
ON   DELETE   CASCADE
GO

ALTER   TABLE   [ dbo ] . [ UsersModule ]   CHECK   CONSTRAINT   [ FK_UsersModule_Users ]
GO

  

接下来，我们得写分配权限的存储过程：

代码

SET  ANSI_NULLS  ON
GO

SET  QUOTED_IDENTIFIER  ON
GO

CREATE   PROCEDURE   [ dbo ] . [ usp_UsersModule_AssignModuleAction ]
(
     @UsersId   int ,
     @ModuleId   smallint ,    
     @Browsing   bit ,
     @Searching   bit ,
     @Creating   bit ,
     @Editing   bit ,
     @Updating   bit ,
     @Deleting   bit ,
     @Erasing   bit ,
     @Submiting   bit ,
     @Auditing   bit ,
     @Reporting   bit ,
     @Receiving   bit ,
     @Delivering   bit     
)
AS
BEGIN   TRANSACTION
     DECLARE   @Purview   int   = 0     
    
     IF   @Browsing   =   1
         SET   @Purview   =   @Purview   +   1
     IF   @Searching   =   1
         SET   @Purview   =   @Purview   +   2
     IF   @Creating   =   1   
         SET   @Purview   =   @Purview   +   4
      IF   @Editing   =   1  
         SET   @Purview   =   @Purview   +   8
      IF   @Updating   =   1  
         SET   @Purview   =   @Purview   +   16
      IF   @Deleting =   1   
         SET   @Purview   =   @Purview   +   32
      IF   @Erasing   =   1
         SET   @Purview   =   @Purview   +   64
      IF   @Submiting   =   1
         SET   @Purview   =   @Purview   +   128
      IF   @Auditing   =   1
         SET   @Purview   =   @Purview   +   256
      IF   @Reporting   =   1
         SET   @Purview   =   @Purview   +   512          
     --如果有更多操作权限，可以此添加 
       IF   @Purview   > 0   AND   @Browsing   = 0
         SET   @Purview   =   @Purview   + 1
    
IF  ( SELECT   count ( * )   FROM   [ UsersModule ]   WHERE   [ UsersId ]   =   @UsersId   AND   [ ModuleId ]   =   @ModuleId  ) > 0
     BEGIN         
         UPDATE   [ UsersModule ]   SET   [ Purview ] = @Purview   WHERE   [ UsersId ]   =   @UsersId   AND   [ ModuleId ]   =   @ModuleId  
         IF   @@ERROR <> 0             
             ROLLBACK   TRANSACTION     
     END
ELSE
     BEGIN
         INSERT   INTO   [ UsersModule ]  ( [ UsersId ]  , [ ModuleId ] , [ Purview ]  )  VALUES  ( @UsersId  , @ModuleId  , @Purview )
         IF   @@ERROR <> 0             
             ROLLBACK   TRANSACTION     
     END
         COMMIT   TRANSACTION

GO