logo标签列表

找出员工用户有访问权限的最快方法是什么？

sqloracle
5
我有一个员工表，其中包含员工的一般信息。另一个用户表用于存储用户信息。用户可以创建员工。
当用户创建员工时，用户将分配部门、产品、子产品和区域给员工。
用户本身可以访问特定的部门、产品、子产品和区域。
例如，用户A可以访问D1部门、P1产品（区域=亚洲、美洲）、P2产品（区域=亚洲）和P3产品（区域=亚洲、美洲）。
部门是产品的父级。每个部门可以有多个产品。
当我说用户A可以访问产品P1（区域=亚洲、美洲）时，这意味着用户A可以添加产品为P1且区域为亚洲或美洲的员工。
他不能为P1产品或其他任何产品添加员工到其他地区。
假设用户A已经在数据库中添加了500名员工，另一个用户B也添加了500名员工等等。
如何编写高效的查询以获取我可以访问的员工？
请注意，可能会有与我拥有相同访问权限的其他用户添加员工，我也应该能够看到这些员工。
以下是我所拥有的数据库架构。
        --------------------------------------------------------
    --  DDL for Table BI_DIVISION
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_DIVISION" 
       (    "DIVISION_ID" NUMBER(*,0) NOT NULL 
        "DIVISION_NAME" VARCHAR2(4000) 
       ) ;

    --------------------------------------------------------
    --  DDL for Table BI_PRODUCT
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_PRODUCT" 
       (    "PRODUCT_ID" NUMBER(*,0) NOT NULL , 
        "PRODUCT_NAME" VARCHAR2(4000), 
        "DIVISION_ID" NUMBER(*,0) 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_SUB_PRODUCT
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_SUB_PRODUCT" 
       (    "SUB_PRODUCT_ID" NUMBER(*,0) NOT NULL, 
        "SUB_PRODUCT_NAME" VARCHAR2(4000), 
        "PRODUCT_ID" NUMBER(*,0), 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_REGION
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_REGION" 
       (    "REGION_ID" NUMBER(*,0) NOT NULL, 
        "REGION_NAME" VARCHAR2(4000) NOT NULL ENABLE 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_EMPLOYEE
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_EMPLOYEE" 
       (    "EMP_ID" NUMBER(*,0) NOT NULL , 
        "DIVISION_ID" NUMBER(*,0), 
        "PRODUCT_ID" NUMBER(*,0), 
        "SUB_PRODUCT_ID" NUMBER(*,0), 
        "REGION_ID" NUMBER(*,0) ,
        "CONFIDENTIAL" VARCHAR2(1) DEFAULT 'Y' 
       );


    --------------------------------------------------------
    --  DDL for Table BI_USER
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_USER" 
       (    "USER_ID" NUMBER(*,0) NOT NULL, 
        "FIRSTNAME" VARCHAR2(4000), 
        "LASTNAME" VARCHAR2(4000) 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_USER_ACCESS
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_USER_ACCESS" 
       (    "USER_ACCESS_ID" NUMBER(*,0) NOT NULL, 
        "USER_ID" NUMBER(*,0), 
        "DIVISION_ID" NUMBER(*,0), 
        "PRODUCT_ID" NUMBER(*,0), 
        "SUB_PRODUCT_ID" NUMBER(*,0), 
        "REGION_ID" NUMBER(*,0), 
        "ACCESS_LEVEL" NUMBER(*,0), 
        "CONFIDENTIAL" VARCHAR2(1) DEFAULT 'Y' 
       ) ;

    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (1,'DIVISION 1');
    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (2,'DIVISION 2');

    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 1',1,1);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 2',1,2);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 3',2,3);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 4',2,4);

    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (1,'SUB PRODUCT 1', 1);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (2,'SUB PRODUCT 2', 1);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (3,'SUB PRODUCT 3', 2);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (4,'SUB PRODUCT 4', 2);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (5,'SUB PRODUCT 5', 3);


    Insert into BI_REGION (REGION_ID,REGION_NAME) values (1,'Americas');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (2,'Asia');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (3,'Germany');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (4,'Japan');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (5,'Pacific');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (6,'ROE');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (7,'United Kingdom');

    Insert into BI_USER (USER_ID,FIRSTNAME,LASTNAME) values (1,'Adam,'Smith);
    Insert into BI_USER (USER_ID,FIRSTNAME,LASTNAME) values (2,'Steve','Jones');

    -- user with user id = 1 has access to division 1 , product 1 , sub product 1 in regons americas, asia, germany with ACCESS_LEVEL = write access (2) and also access to confidential data 
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,1,2,'Y');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,2,2,'Y');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,3,2,'Y');

    -- user with user id = 1 has access to division 1 , product 2 , sub product 4 in regons americas, asia, germany with ACCESS_LEVEL = write access (2) and also NO access to confidential data 
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,1,2,'N');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,2,2,'N');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,3,2,'N');

    -- employees in division 1 , product 1, sub product 1 and region americas and not confi.
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (1,'1','1','1',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (2,'1','1','1',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (3,'1','1','1',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (4,'1','1','1',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (5,'1','1','1',7,'N');

    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (11,'1','1','2',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (12,'1','1','2',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (13,'1','1','2',3,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (14,'1','1','2',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (15,'1','1','2',3,'N');

    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (111,'2','3','5',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (112,'2','3','5',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (113,'2','3','5',3,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (114,'2','3','5',4,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (115,'2','3','5',5,'N');

以下是我目前编写的查询，但我不确定这是否是最佳方法。

    SELECT 
  *
FROM 
  BI_EMPLOYEE e 
JOIN BI_USER_ACCESS uad On uad.DIVISION_ID = e.DIVISION_ID and uad.USER_ID = 137
JOIN BI_USER_ACCESS uap On uap.PRODUCT_ID = e.PRODUCT_ID and uap.USER_ID = 137
JOIN BI_USER_ACCESS uasp On uasp.SUB_PRODUCT_ID = e.SUB_PRODUCT_ID and uasp.USER_ID = 137
JOIN BI_USER_ACCESS uar On uar.REGION_ID = e.REGION_ID  and uar.SUB_PRODUCT_ID = e.SUB_PRODUCT_ID and uar.USER_ID = 137

编辑1：

我已经更新了我的问题，并提供了数据库脚本和一些样例数据。

为什么关闭这个问题？ - ajm
我们缺少 BI_USER_ACCESS 表。您能否提供一些示例数据（INSERT...）和期望的结果。您还可以删除与问题无直接关系的所有列。 - Vincent Malgrat
Vincent Malgrat。好的，请给我一些时间。被卡在其他事情上了:( - ajm
@Vincent Malgrat。我已经更新了我的问题，并附上了脚本。 - ajm
3个回答
1
用户可以访问的员工列表将通过以下查询给出：
SELECT * 
  FROM bi_employee e
 WHERE EXISTS (SELECT NULL
                 FROM bi_user_access ua
                WHERE ua.division_id = e.division_id
                  AND ua.product_id = e.product_id
                  AND ua.sub_product_id = e.sub_product_id
                  AND ua.region_id = e.region_id
                  AND (e.confidential = 'N' OR ua.confidential = 'Y')
                  AND ua.user_id = :user_id);

通过您的数据样本，用户1可以访问员工1至4。

谢谢你的回答。但是我真的需要检查除法和乘法吗？因为子产品是最低级别的（除法是乘法的父级，而乘法是子产品的父级）。 - ajm
1
如果（1）sub_product_id是唯一的，并且（2）您在bi_user_access上的索引是在sub_product_id上，则可以仅检查subproduct_id。 - Vincent Malgrat
sub_product_id 在所有产品中是唯一的。但我没有任何索引。 - ajm
表 bi_user_access 中的列 division_id 和 product_id 可以被删除，因为这些数据已经可以在表 bi_product 和 bi_subproduct 中找到 :) - Vincent Malgrat
1

你的问题明确地询问了获取用户可访问员工列表的最快方式是什么。所以我会回答这个问题。

几年前，我曾在一个类似的系统上工作过，那时我们需要非常快速地评估这些信息。原则是相同的，但我们有更多的标准（部门、产品、地区、国家、城市、单位等）。

如果性能真的很关键，那么将查询结果实体化到表格中是值得的，例如：ACL_CACHE(USER_ID, EMP_ID)

然后，获取用户可访问的员工的查询就变得非常简单：

SELECT EMP_ID
FROM ACL_CACHE
WHERE USER_ID = ####

如果您想根据用户的访问级别限制他们能看到的结果时，您也可以将ACL_CACHE表加入其他查询中。

这个方法非常有效，在处理大量员工和/或用户时带来了实质性的好处。我们通常使用的是~500,000条记录。

显然，缺点是需要保持ACL_CACHE表的最新状态。这意味着其他一些交易会变得稍微慢一些。例如，当添加一个新员工时，您还需要为所有可以看到新员工的用户在ACL_CACHE表中添加记录。

根据我们的经验，这类交易的额外延迟对用户来说并不明显，而为了使所有只读交易快上一个数量级而做出的牺牲是非常值得的。

或者，您可以将更新ACL_CACHE表作为夜间任务进行批量更新，如果您可以接受数据最多为24小时“旧”。由于您的表名为“HEADCOUNT_BI”，我猜如果你的人头数报告始终准确到昨晚，这也许是可以接受的。

0

此查询将USER_ACCESS表与EMPLOYEE表连接。它正在过滤提供的USER_ID（137）的USER_ACCESS表，然后加入到EMPLOYEE表中，只返回具有与USER_ACCESS表中存在的相同DIVISION_ID和PRODUCT_ID记录的员工。

select  e.*
from    BI_USER_ACCESS a
join    BI_EMPLOYEE e
on      a.DIVISION_ID = e.DIVISION_ID
and     a.PRODUCT_ID = e.PRODUCT_ID
where   a.USER_ID = 137

它只选择EMPLOYEE数据（e.*），但是如果需要，您可以（例如）将USER_ACCESS表连接到USER表并返回用户数据。但是，仅基于USER_ACCESS中的2个字段返回EMPLOYEE数据，应该就可以了。

这样是否符合您的要求？

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接