sql - 如何检查未分配给任何用户但 oracle 默认用户（root、系统等）的所有角色？

Question

我正在尝试创建一个 SQL 来获取任何自定义（非默认）oracle 用户未使用的所有角色，或者它们正被处于无效状态的用户使用。对我来说，无效状态意味着，以 开头SRV_%并具有其状态的用户与 then 不同OPEN。

到目前为止，我拥有的 sql 是这个：

with
  INVALID_USERS as
    (
      select
        USERNAME
      from
        DBA_USERS
      where
        USERNAME like 'SRV_%'
        and ACCOUNT_STATUS != 'OPEN'
    ),
  VALID_USERS as (
    select
      USERNAME
    from
      DBA_USERS
    where
      USERNAME not like 'SRV_%'
      and (not ACCOUNT_STATUS = 'LOCKED')
  )
select *
from
  DBA_ROLE_PRIVS drolp
  join DBA_ROLES drol ON drol.ROLE = drolp.GRANTED_ROLE
where
  GRANTEE in (select USERNAME from INVALID_USERS)
  and GRANTEE not in (select USERNAME from VALID_USERS);

可能我需要使用 HAVING 从无效用户角色使用中删除有效用户的出现。我被困在那里。

标签： sqloracle

Answer 1

我不知道您的 VALID_USER 和 INVALID_USER 查询是否正确，因为您没有向我们提出您的要求。但我认为这是您的主要查询的问题：

with
  INVALID_USERS as
    (
      select
        USERNAME
      from
        DBA_USERS
      where
        USERNAME like 'SRV_%'
        and ACCOUNT_STATUS != 'OPEN'
    ),
  VALID_USERS as (
    select
      USERNAME
    from
      DBA_USERS
    where
      USERNAME not like 'SRV_%'
      and (not ACCOUNT_STATUS = 'LOCKED')
  )
select *
from
  DBA_ROLES drol
where
  -- role is assigned to AT LEAST ONE "invalid user"
  EXISTS (select 1 from DBA_ROLE_PRIVS drolp where drol.ROLE = drolp.GRANTED_ROLE and GRANTEE in (select USERNAME from INVALID_USERS))
  -- and role is not assigned to ANY "valid users"
  and NOT EXISTS (select 1 from DBA_ROLE_PRIVS drolp where drol.ROLE = drolp.GRANTED_ROLE and GRANTEE in (select USERNAME from VALID_USERS));

查询的 WHERE 逻辑对 DBA_ROLE_PRIVS 中的每一行检查一次，因此 GRANTEE 将始终是一个值 - 一个用户。因此，它只会对您的 VALID_USERS 和 INVALID_USERS 中的任何 GRANTEE 返回 true - 可能不是您想要的。

您需要查询 DBA_ROLE_PRIVS 表两次——一次检查有效用户，一次检查无效用户。您可以使用聚合函数来解决这个问题，尽管您必须将您的条件放在 GROUP BY 之外 - 使其成为子查询或将它们移动到 HAVING 子句，如下所示：

with
  INVALID_USERS as
    (
      select
        USERNAME
      from
        DBA_USERS
      where
        USERNAME like 'SRV_%'
        and ACCOUNT_STATUS != 'OPEN'
    ),
  VALID_USERS as (
    select
      USERNAME
    from
      DBA_USERS
    where
      USERNAME not like 'SRV_%'
      and (not ACCOUNT_STATUS = 'LOCKED')
  )
select GRANTED_ROLE
from
  DBA_ROLE_PRIVS drolp
  join DBA_ROLES drol on drol.ROLE = drolp.GRANTED_ROLE
group by GRANTED_ROLE
having
  -- role is assigned to AT LEAST ONE "invalid user"
  sum(CASE when GRANTEE in (select USERNAME from INVALID_USERS) THEN 1 else 0 end) > 0
  -- and role is not assigned to ANY "valid users"
  and sum(CASE when GRANTEE in (select USERNAME from VALID_USERS) THEN 1 else 0 end) = 0;