如何根据USERDETAILS表角色控制交互式报表数据显示范围？

问题场景 #

现有交互式报表需展示多部门数据，涉及D365_PURCHASE_INVOICE_STAGING表的COMPANY列（对应部门标识D365LE）；USERDETAILS用户表包含D365LE（员工所属部门）和ROLE（标识是否为管理员）列。需求为：

• 若用户是管理员，报表显示所有数据
• 若为普通用户，仅显示其所属部门的数据

原普通用户过滤SQL（可正常运行）：

select * from D365_PURCHASE_INVOICE_STAGING 
where upper(company) = (SELECT u.D365LE FROM USERDETAILS u WHERE upper(u.username) = upper(:app_user))

尝试的SQL可编译但无数据返回：

select s.* from D365_PURCHASE_INVOICE_STAGING s
INNER JOIN USERDETAILS u
    on s.COMPANY = u.D365LE
where (u.ROLE = 'Admin')
or 
(upper(s.COMPANY) = (SELECT u.D365LE FROM USERDETAILS WHERE upper(u.USERNAME) = upper(:app_user)))
and (u.ROLE <> 'Admin')

错误原因 #

1. INNER JOIN限制数据范围：管理员的USERDETAILS记录中D365LE是其自身所属部门，INNER JOIN会仅保留与管理员部门匹配的报表数据，无法实现展示全量数据的需求。
2. 逻辑运算符优先级错误：AND优先级高于OR，原WHERE条件实际被解析为：

   (u.ROLE = 'Admin') OR [ (upper(s.COMPANY) = ...) AND (u.ROLE <> 'Admin') ]
   

   这导致普通用户的过滤逻辑混乱——需要同时满足关联任意非管理员用户的部门、且当前用户不是管理员，完全偏离需求。

正确解决方案 #

方案1：通过子查询获取当前用户权限信息 #

先查询当前用户的角色和所属部门，再根据角色判断过滤条件：

SELECT s.*
FROM D365_PURCHASE_INVOICE_STAGING s
WHERE 
    -- 管理员展示全量数据
    EXISTS (SELECT 1 FROM USERDETAILS u WHERE upper(u.username) = upper(:app_user) AND u.ROLE = 'Admin')
    OR
    -- 普通用户仅展示所属部门数据
    upper(s.COMPANY) = (SELECT u.D365LE FROM USERDETAILS u WHERE upper(u.username) = upper(:app_user))

方案2：关联当前用户的单条记录（更简洁） #

仅关联当前用户的USERDETAILS记录，避免关联整张表带来的逻辑混乱：

SELECT s.*
FROM D365_PURCHASE_INVOICE_STAGING s
CROSS JOIN (
    SELECT u.ROLE, u.D365LE 
    FROM USERDETAILS u 
    WHERE upper(u.username) = upper(:app_user)
) current_user
WHERE 
    current_user.ROLE = 'Admin'
    OR upper(s.COMPANY) = upper(current_user.D365LE)

说明 #

• 两种方案均避免了不必要的表关联，直接针对当前用户的权限做判断，逻辑清晰且性能更优
• 统一使用upper()处理字符串，避免大小写不匹配导致的过滤错误

内容的提问来源于stack exchange，提问作者Ryan