如何增加安全控制

思想：

用户管理：中是可以为用户分配管理角色（可以让某人拥有某些角色）

角色管理：中是 ：1可以为角色分配做某事的权限，2可以将某角色分配给某些用户

 

☻☞以下是对项目负责人表中记录的增删改操作添加触发器，触发器的作用是增删改的同时自动的对SEC_UserRole表中的用户角色进行有条件的删除和修改----------

注意：更新做的是先删除再插入的操作。

 CREATE  TRIGGER UserRole_Insert

 ON INVEST_ProjectLead

 FOR INSERT,UPDATE,DELETE

 AS

 BEGIN

  BEGIN TRAN

 DECLARE  @CreatedBy varchar(38),

          @LastUpdatedBy varchar(38),

     @UserId VARCHAR(38),

               @RoleId varchar(38),

          @Type varchar(1);   --0.insert数据时触发 1.update  2.delete

 

 SELECT @RoleId=SR.Id FROM SEC_Role AS SR WHERE SR.Name = '项目负责人';

 SET @Type='0'

 IF EXISTS(SELECT TOP 1 1 FROM INSERTED) AND EXISTS(SELECT TOP 1 1 FROM DELETED) --UPDATE

 BEGIN

  SET @Type='1'                 

  SELECT

   @CreatedBy=DELETED.CREATORID

  ,@LastUpdatedBy=DELETED.CREATORID

  ,@UserId=DELETED.ProjectLeaderID

  FROM DELETED;

 

 END

 ELSE IF EXISTS(SELECT TOP 1 1 FROM INSERTED) AND NOT EXISTS(SELECT TOP 1 1 FROM DELETED)--INSERT

 BEGIN

 SET @Type='0'

  SELECT

   @CreatedBy=INSERTED.CREATORID

  ,@LastUpdatedBy=INSERTED.CREATORID

  ,@UserId=INSERTED.ProjectLeaderID

  FROM INSERTED;        

 END

 ELSE IF NOT EXISTS(SELECT TOP 1 1 FROM INSERTED) AND EXISTS(SELECT TOP 1 1 FROM DELETED)--DELETE

 BEGIN

 SET @Type='2'

  SELECT

   @CreatedBy=DELETED.CREATORID

  ,@LastUpdatedBy=DELETED.CREATORID

  ,@UserId=DELETED.ProjectLeaderID

  FROM DELETED;

 END

 

 

 IF(@Type='0') --0.insert

 BEGIN

 IF NOT EXISTS (SELECT top 1 1 FROM SEC_UserRole WHERE UserId=(SELECT ProjectLeaderID FROM INSERTED ))

 BEGIN

  INSERT INTO SEC_UserRole

  (

UserId,

RoleId,

CreatedBy,

CreatedDate,

LastUpdatedBy,

LastUpdatedDate

  )

  VALUES

  (

@UserId, 

@RoleId,

@CreatedBy,

getdate(),

@LastUpdatedBy,

getdate()

  )

  END

  ELSE

  BEGIN

  IF NOT EXISTS(SELECT TOP 1 1 FROM INVEST_ProjectLead WHERE ProjectLeaderID = @UserId)

  BEGIN

  UPDATE SEC_UserRole

  SET

  LastUpdatedBy = @LastUpdatedBy,

  LastUpdatedDate = getdate()

  WHERE UserId = @UserId

      END

  END

  END

  IF(@Type='2')--2.delete

  BEGIN

     IF NOT EXISTS(SELECT TOP 1 1 FROM INVEST_ProjectLead WHERE ProjectLeaderID = @UserId)

     BEGIN

     DELETE FROM SEC_UserRole

     WHERE UserId = @UserId

     END

  END

  IF(@Type='1')--1.update

  BEGIN

     IF NOT EXISTS(SELECT TOP 1 1 FROM INVEST_ProjectLead WHERE ProjectLeaderID = @UserId)

     BEGIN

     UPDATE SEC_UserRole

  SET                      

      LastUpdatedBy = @LastUpdatedBy,

      LastUpdatedDate = getdate()

  WHERE UserId = @UserId

  END

  END         

  

 IF (@@ERROR<>0)

  BEGIN

     ROLLBACK TRAN;

  END;

 ELSE

  BEGIN

     COMMIT TRAN;

  END

END

 

//控件验证

 <asp:DropDownList runat="server" ID="dlForum_Plate" DataSource="Forum_Plate" DataTextField="PlateName" OnTextChanged

                        DataValueField="PlateID" data-validator="required[请选择板块]">

                    </asp:DropDownList>

 

 

  <asp:TextBox runat="server" ID="txtTitle" class="inputClass" Style="width: 50%;"

                        data-validator="required,length[1,100]"></asp:TextBox>

                    &nbsp;<span class="red">*文章标题不能为空</span>