金蝶K3用户数据权限之二次开发接入
金蝶K3可以设置用户的数据权限，例如业务员对客户信息是敏感的，全部业务员虽然都可以打开销售订单模块，但不是他跟进的客户的订单是无法在序时簿中查看到的。
一、介绍
在金蝶软件内部是可以很容易设置的，启用某一核算项目的数据权限，设置用户的数据权限即可。
软件界面是这样子的：
数据库是这样子的，一个 binary(100) 字段保存了全部用户对此客户是否有数据操作权限：
二、实现
1、浏览权限（一次查询全部用户和客户太多太慢，自己可再修改SP参数按客户查全部用户，或按用户查全部客户）
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
DECLARE @FUserID AS INTEGER
SELECT @FUserID = FUserID FROM dbo.t_User WHERE FName='用户名称'
DECLARE @CustomerId AS INTEGER
SELECT @CustomerId = FItemID FROM dbo.t_Organization WHERE FShortNumber='客户编码'
EXEC K3Plus_用户数据权限_客户_浏览 @FUserID,@CustomerId
*/
-- =============================================
ALTER PROCEDURE 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
GO
2、修改权限
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 '用户名称'
SELECT @customerId = FItemID FROM dbo.t_Organization WHERE FShortNumber LIKE '客户编码'
EXEC K3Plus_用户数据权限_客户_更新 @userId,@customerId,1,1,0
*/
-- =============================================
ALTER PROCEDURE 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
GO
3、新增客户核算项目时，增加初始数据权限行
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
DECLARE @customerId AS INT
SELECT @customerId = FItemID FROM dbo.t_Organization WHERE FShortNumber LIKE '客户编码'
EXEC K3Plus_用户数据权限_客户_新增 @customerId
*/
-- =============================================
ALTER PROCEDURE 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
GO
三、完了
此文非全部原创，参考了一个论坛里SQL关于位运算的代码，但已找不到其网址了，故无法转载其网址。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
