筛选具有单表审核权的用户
select DISTINCT w2.FSubSys AS FSubSys,
t3.FTypeName,
--t4.FName AS FItemName,
--t4.FDescription AS FDescription,
t3.FUserName
--t3.FObjectType,
-- t3.FObjectID,
-- t4.FIndex
FROM
(
SELECT t1.FObjectType,
t1.FObjectID,
t1.FAccessMask,
t1.FName AS FTypeName,
t2.FUserID,
t2.FName AS FUserName,
t1.FGroupID
FROM
(
SELECT a.*,
o.FName AS FName,
y1.FGroupID
FROM t_AccessControl a,
t_ObjectType o,
(
SELECT DISTINCT FObjectType,
FObjectID,
FGroupID
FROM t_ObjectAccess
) y1
WHERE a.FObjectType = o.FObjectType
AND a.FObjectID = o.FObjectID
AND a.FObjectType = y1.FObjectType
AND a.FObjectID = y1.FObjectID
) t1 ,
(
SELECT FUserID,
FName
FROM t_User
WHERE
(
FUserID > 16394
OR
(
FUserID > 9
AND FUserID < 16384
)
)
) t2
WHERE t1.FUserID = t2.FUserID
UNION
SELECT t1.FObjectType,
t1.FObjectID,
t1.FAccessMask,
t1.FName AS FTypeName,
t2.FUserID,
t2.FName AS FUserName,
t1.FGroupID
FROM
(
SELECT a.*,
o.FName AS FName,
y1.FGroupID
FROM t_AccessControl a,
t_ObjectType o,
(
SELECT DISTINCT FObjectType,
FObjectID,
FGroupID
FROM t_ObjectAccess
) y1
WHERE a.FObjectType = o.FObjectType
AND a.FObjectID = o.FObjectID
AND a.FObjectType = y1.FObjectType
AND a.FObjectID = y1.FObjectID
) t1 ,
(
SELECT g.FUserID,
FGroupID,
FName
FROM t_Group g,
t_User u
WHERE g.FUserID = u.FUserID
) t2
WHERE t1.FUserID = t2.FGroupID
) t3 ,
t_ObjectAccessType t4,
t_GroupAccessType w2,dbo.t_User u
WHERE NOT
(
t3.FObjectType = 3
AND t3.FObjectID = 20
)
AND t3.FObjectType = t4.FObjectType
AND t3.FObjectID = t4.FObjectID
AND t4.FAccessUse <> 262144
AND t4.FAccessMask <> 262144
AND (t3.FAccessMask & t4.FAccessMask = t4.FAccessMask)
AND t3.FGroupID = w2.FGroupID AND t4.FName ='审核' AND t4.FDescription='审核' AND u.FForbidden=0 AND u.FUserID= t3.FUserID ORDER BY FSubSys,t3.FTypeName,t3.FUserName
权限对象表
SELECT t4.FObjectType,t4.FObjectID,t4.FIndex,* FROM t_ObjectAccessType t4 WHERE t4.FName ='审核' AND t4.FDescription='审核'
用户组权限类表
SELECT * FROM t_GroupAccessType w2
多级审核权限人员列表
SELECT b.FName '表名',c.FName '审核人', a.FCheckLevel '审核级别',c.FForbidden '是否禁用'
FROM dbo.t_MultiLevelCheck a
LEFT JOIN ICTransactionType b
ON FID = FBillType
LEFT JOIN t_User c
ON FUserID = a.FCheckMan WHERE c.FForbidden=0;
用FForbidden删除已离职人员