金蝶K3用户数据权限创建修改存储过程

金蝶K3用户数据权限创建修改存储过程

金蝶 K3、KIS 可以设置数据权限，应用场景例如：业务员只能查看维护“部分客户的资料”（对这些自己跟进的客户的资料、单据有全部权限，对其它客户资料不可见）。之前写过一篇关于此SQL算法的文章，http://tangzhongxin.blog.163.com/blog/static/892196120154188425/，现将其完整存储过程贴出，复制粘贴可用。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
DECLARE @customerId AS INT
SELECT @customerId = FItemID FROM dbo.t_Organization WHERE FShortNumber LIKE '0Test'
EXEC K3Plus_用户数据权限_客户_新增 @customerId
*/
-- =============================================
ALTER PROCEDURE [dbo].[K3Plus_用户数据权限_客户_新增]
@organization_FItemId AS INT
AS
BEGIN
SET NOCOUNT ON;

--100(max user)
DECLARE @maxValue INT
SELECT TOP 1 @maxValue = FValue FROM t_SystemProfile WHERE FCategory = 'BOS' AND FKey = 'AccessDataNumber'
--SELECT @maxValue

--新增
IF NOT EXISTS (SELECT    *
 FROM      Access_t_Organization
 WHERE     FItemID = @organization_FItemId )
INSERT  INTO Access_t_Organization
( FItemID ,
 FParentIDX ,
 FDataAccessView ,
 FDataAccessEdit ,
 FDataAccessDelete
)
VALUES  ( @organization_FItemId ,
 0 ,
 CONVERT(VARBINARY(7200), REPLICATE(CHAR(0), 7200)) ,
 CONVERT(VARBINARY(7200), REPLICATE(CHAR(0), 7200)) ,
 CONVERT(VARBINARY(7200), REPLICATE(CHAR(0), 7200))
)

--待遍历的初始权限列表
DECLARE @tRight AS TABLE(FAccess INT,FAccessUUID INT,FByteNum INT,FBitNo INT)
INSERT @tRight
       ( FAccess ,
         FAccessUUID ,
         FByteNum ,
         FBitNo
       )
SELECT  t1.FAccess ,
t2.FAccessUUID ,
( FAccessUUID / 8 + 1 ) FByteNum ,
POWER(2, 7 - ( FAccessUUID + 7 ) % 8) FBitNo
FROM    t_User t2
LEFT JOIN t_DataTypeRight t1 ON t1.FUserID = t2.FUserID
AND t1.FType = 1
AND t1.FClassID = 1
WHERE   t2.FAccessUUID > 0
AND ( t1.FAccess & 16 <> 0
 OR t1.FAccess & 8 <> 0
 OR t1.FAccess IS NULL
 --OR t2.FUserID = 16394 --Administrator
)
--SELECT * FROM @tRight

----------------------------------------------------------------------
--使用游标遍历
DECLARE @FAccess AS INT
DECLARE @FAccessUUID AS INT
DECLARE @FByteNum AS INT
DECLARE @FBitNo AS INT

--声明游标
DECLARE cur CURSOR READ_ONLY
FOR SELECT FAccess,FAccessUUID,FByteNum,FBitNo FROM @tRight

--打开游标
OPEN cur

--读取第1行数据(将值存入变量)
FETCH NEXT FROM cur
INTO @FAccess,@FAccessUUID,@FByteNum,@FBitNo

--用while循环控制游标活动
WHILE(@@fetch_status=0)
BEGIN
--操作
IF(@FByteNum=1)
BEGIN
UPDATE  t1
SET     t1.FDataAccessView   =                                                      CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessView,  @FByteNum, 1) | @FBitNo)  + SUBSTRING(t1.FDataAccessView,  @FByteNum + 1, @maxValue - @FByteNum) ,
t1.FDataAccessEdit   =                                                      CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessEdit,  @FByteNum, 1) | @FBitNo)  + SUBSTRING(t1.FDataAccessEdit,  @FByteNum + 1, @maxValue - @FByteNum) ,
t1.FDataAccessDelete =                                                      CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessDelete,@FByteNum, 1) | @FBitNo)  + SUBSTRING(t1.FDataAccessDelete,@FByteNum + 1, @maxValue - @FByteNum)
FROM    Access_t_Organization t1
WHERE   FItemID = @organization_FItemId
END
ELSE IF(@FByteNum>1)
BEGIN
UPDATE  t1
SET     t1.FDataAccessView   = SUBSTRING(t1.FDataAccessView,   1, @FByteNum - 1) +  CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessView,  @FByteNum, 1) | @FBitNo) + SUBSTRING(t1.FDataAccessView,   @FByteNum + 1, @maxValue - @FByteNum) ,
t1.FDataAccessEdit   = SUBSTRING(t1.FDataAccessEdit,   1, @FByteNum - 1) +  CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessEdit,  @FByteNum, 1) | @FBitNo) + SUBSTRING(t1.FDataAccessEdit,   @FByteNum + 1, @maxValue - @FByteNum) ,
t1.FDataAccessDelete = SUBSTRING(t1.FDataAccessDelete, 1, @FByteNum - 1) +  CONVERT(BINARY(1), SUBSTRING(t1.FDataAccessDelete,@FByteNum, 1) | @FBitNo) + SUBSTRING(t1.FDataAccessDelete, @FByteNum + 1, @maxValue - @FByteNum)
FROM    Access_t_Organization t1
WHERE   FItemID = @organization_FItemId
END



--在循环体内将读取其余行数据(将值存入变量)
FETCH NEXT FROM cur
INTO @FAccess,@FAccessUUID,@FByteNum,@FBitNo
END

--关闭游标
CLOSE cur

--删除游标
DEALLOCATE cur

----------------------------------------------------------------------

END

二、修改

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
DECLARE @userId AS INT
DECLARE @customerId AS INT
SELECT @userId = FUserID FROM dbo.t_User WHERE FName LIKE 'Momo'
SELECT @customerId = FItemID FROM dbo.t_Organization WHERE FShortNumber LIKE '0Test'
EXEC K3Plus_用户数据权限_客户_更新 @userId,@customerId,1,1,0
*/
-- =============================================
ALTER PROCEDURE [dbo].[K3Plus_用户数据权限_客户_更新]
@user_FItemId         AS INT,
@organization_FItemId AS INT,
@right_view           AS BIT = NULL,
   @right_edit           AS BIT = NULL,
   @right_delete         AS BIT = NULL
AS
BEGIN
SET NOCOUNT ON;

--1.用户(权限唯一ID标识)
DECLARE @user_AccessUUID AS INT
SELECT @user_AccessUUID = FAccessUUID FROM t_User WHERE FUserID = @user_FItemId

--------------------------------------------------------------------------------

--2.客户(用户老权限data)
DECLARE @access_dat_view   AS BINARY(100)
DECLARE @access_dat_edit   AS BINARY(100)
DECLARE @access_dat_delete AS BINARY(100)
SELECT
@access_dat_view   = FDataAccessView,
@access_dat_edit   = FDataAccessEdit,
@access_dat_delete = FDataAccessDelete
FROM dbo.Access_t_Organization WHERE FItemID=@organization_FItemId

--------------------------------------------------------------------------------

--3.哪个字节byte
DECLARE @byteIndex_view   AS INT --view
DECLARE @byteIndex_edit   AS INT --edit
DECLARE @byteIndex_delete AS INT --delete
SELECT @byteIndex_view   = SUBSTRING(@access_dat_view,  @user_AccessUUID/8+1,1)
SELECT @byteIndex_edit   = SUBSTRING(@access_dat_edit,  @user_AccessUUID/8+1,1)
SELECT @byteIndex_delete = SUBSTRING(@access_dat_delete,@user_AccessUUID/8+1,1)

--------------------------------------------------------------------------------

--4.bit位
DECLARE @bitIndex    AS INT --授权
DECLARE @bitIndexNot AS INT --取消授权
SELECT @bitIndex    =   POWER(2,7-(@user_AccessUUID+7)%8)
SELECT @bitIndexNot = ~(POWER(2,7-(@user_AccessUUID+7)%8))

--bit所在位置
DECLARE @start_1  AS INT
DECLARE @offset_1 AS INT
DECLARE @start_2  AS INT
DECLARE @offset_2 AS INT
SELECT @start_1   = CASE WHEN ( @user_AccessUUID/8+1 ) > 1   THEN                                  1   ELSE 0 END
SELECT @offset_1  = CASE WHEN ( @user_AccessUUID/8+1 ) > 1   THEN       ( @user_AccessUUID/8+1 ) - 1   ELSE 0 END
SELECT @start_2   = CASE WHEN ( @user_AccessUUID/8+1 ) < 200 THEN       ( @user_AccessUUID/8+1 ) + 1   ELSE 0 END
SELECT @offset_2  = CASE WHEN ( @user_AccessUUID/8+1 ) < 200 THEN 200 - ( @user_AccessUUID/8+1 )       ELSE 0 END
--select @start_1,@offset_1,@start_2,@offset_2

--------------------------------------------------------------------------------

--5.用户新权限data
DECLARE @access_dat_view_2   AS BINARY(100)
DECLARE @access_dat_edit_2   AS BINARY(100)
DECLARE @access_dat_delete_2 AS BINARY(100)

--5.1.用户新权限(view)
SELECT @access_dat_view_2 = @access_dat_view --默认用原值不修改(当为null时)
DECLARE @value_view AS INT
IF(@right_view=1)        --1则授权(按|位或)
SELECT @value_view = @byteIndex_view | @bitIndex
ELSE IF(@right_view=0)   --0则取消授权(按&位与)
SELECT @value_view = @byteIndex_view & @bitIndexNot
IF(@right_view=1 OR @right_view=0)
BEGIN
SELECT @access_dat_view_2 =
SUBSTRING( @access_dat_view,@start_1,@offset_1 ) +
CONVERT( BINARY(1),@value_view) +
SUBSTRING(@access_dat_view,@start_2,@offset_2)
END

--5.2.用户新权限(edit)
SELECT @access_dat_edit_2 = @access_dat_edit --默认用原值不修改(当为null时)
DECLARE @value_edit AS INT
IF(@right_edit=1)        --1则授权(按|位或)
SELECT @value_edit = @byteIndex_edit | @bitIndex
ELSE IF(@right_edit=0)   --0则取消授权(按&位与)
SELECT @value_edit = @byteIndex_edit & @bitIndexNot
IF(@right_edit=1 OR @right_edit=0)
BEGIN
SELECT @access_dat_edit_2 =
SUBSTRING( @access_dat_edit,@start_1,@offset_1 ) +
CONVERT( BINARY(1),@value_edit) +
SUBSTRING(@access_dat_edit,@start_2,@offset_2)
END

--5.3.用户新权限(delete)
SELECT @access_dat_delete_2 = @access_dat_delete --默认用原值不修改(当为null时)
DECLARE @value_delete AS INT
IF(@right_delete=1)      --1则授权(按|位或)
SELECT @value_delete = @byteIndex_delete | @bitIndex
ELSE IF(@right_delete=0) --0则取消授权(按&位与)
SELECT @value_delete = @byteIndex_delete & @bitIndexNot
IF(@right_delete=1 OR @right_delete=0)
BEGIN
SELECT @access_dat_delete_2 =
SUBSTRING( @access_dat_delete,@start_1,@offset_1 ) +
CONVERT( BINARY(1),@value_delete) +
SUBSTRING(@access_dat_delete,@start_2,@offset_2)
END

--------------------------------------------------------------------------------
--9.更新
UPDATE dbo.Access_t_Organization
SET
       FDataAccessView   = @access_dat_view_2,
       FDataAccessEdit   = @access_dat_edit_2,
       FDataAccessDelete = @access_dat_delete_2
WHERE FItemID = @organization_FItemId

END

三、浏览

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
DECLARE @FUserID AS INTEGER
SELECT @FUserID = FUserID FROM dbo.t_User WHERE FName='Momo'
DECLARE @CustomerId AS INTEGER
SELECT @CustomerId = FItemID FROM dbo.t_Organization WHERE FShortNumber='RPDO' --RPDO,AIEO
EXEC K3Plus_用户数据权限_客户_浏览 @FUserID,@CustomerId
*/
-- =============================================
ALTER PROCEDURE [dbo].[K3Plus_用户数据权限_客户_浏览]
@user_FItemId AS INT,
@organization_FItemId AS INT
AS
BEGIN
SET NOCOUNT ON;

--1.用户
--SELECT * FROM dbo.t_User WHERE FName='ysy' --16404

--2.查询某个用户的(客户)数据权限
SELECT  
t_customer.FItemID    AS '客户ID',
t_customer.FNumber    AS '客户代码' ,
t_customer.FName      AS '客户名称' ,
t_right.UserID        AS '用户ID',
t_right.UserName      AS '用户名称',

t_u2.FDescription     AS '用户说明',
t_u2.FForbidden       AS '用户禁用',
t_u2.FUserTypeID      AS '用户类型ID',
t_u2.FAccessUUID      AS '用户FAccessUUID',
t_u2.FEmpID           AS '用户FEmpID',

t_right.FViewAccess   AS '查询权' ,
t_right.FEditAccess   AS '修改权' ,
t_right.FDeleteAccess AS '删除权'

FROM    ( SELECT    t_access.FItemID ,   --核算项目(客户)ID
t_u.FUserID AS 'UserID',     --用户ID
t_u.FName AS 'UserName' ,      --用户Name

--
MIN(t_access.FParentIDX) AS FParentIDX ,

--查看权限  
( CASE WHEN MAX(SUBSTRING(t_access.FDataAccessView,
 t_u.FAccessUUID / 8 + 1, 1)
& POWER(2, 7 - ( t_u.FAccessUUID + 7 ) % 8)) > 0
  THEN 1
  ELSE 0
 END ) AS FViewAccess ,
       
--修改权限
( CASE WHEN MAX(SUBSTRING(t_access.FDataAccessEdit,
 t_u.FAccessUUID / 8 + 1, 1)
& POWER(2, 7 - ( t_u.FAccessUUID + 7 ) % 8)) > 0
  THEN 1
  ELSE 0
 END ) AS FEditAccess ,
 
--删除权限
( CASE WHEN MAX(SUBSTRING(t_access.FDataAccessDelete,
 t_u.FAccessUUID / 8 + 1, 1)
& POWER(2, 7 - ( t_u.FAccessUUID + 7 ) % 8)) > 0
  THEN 1
  ELSE 0
 END ) AS FDeleteAccess
 FROM      t_user t_u ,
Access_t_Organization t_access
--INNER JOIN dbo.Access_t_Organization t_access ON t_u.FUserID = ( SELECT FUserID FROM dbo.t_User WHERE FName = 'ysy') --16404 -- 用户ID
 --WHERE     t_u.FUserID = ( SELECT  FUserID FROM    dbo.t_User WHERE   FName = 'ysy') --16404 -- 用户ID
 WHERE 1=1
 AND t_u.FUserID = @user_FItemId  --条件:用户
 AND t_access.FItemID = @organization_FItemId --条件:客户
 GROUP BY  t_access.FItemID ,t_u.FUserID,t_u.FName
) t_right        
       
LEFT JOIN dbo.t_Organization t_customer ON t_right.FItemID = t_customer.FItemID
LEFT JOIN dbo.t_User t_u2 ON t_right.UserID=t_u2.FUserID --用户(详细)
WHERE   1 = 1
AND FNumber IS NOT NULL
--AND (t_right.FDeleteAccess=1 OR t_right.FEditAccess =1 OR t_right.FViewAccess =1) --不显示无权限的行
ORDER BY t_customer.FNumber



END