Users to roles and system privileges-优快云博客

本文介绍了一个Oracle数据库脚本，该脚本使用Oracle的connect by SQL语法来显示系统权限、角色和用户之间的层级关系。通过两个不同的角度：从用户及其拥有的权限开始，或者从权限出发查看拥有该权限的角色及用户，提供了详细的层级视图。此外，还提供了一个用于展示对象权限的脚本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

This is a script that shows the hierarchical relationship between system privileges, roles and users.

It makes use of Oracles connect by SQL idiom.

Vladimir Gabzovski notifies me, that the following script doesn't run on 8.1 (ORA-01472). I want to thank him for pointing this out.

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users

This is also possible the other way round: showing the system privileges in relation to roles that have been granted this privilege and users that have been granted either this privilege or a role:

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

Object privileges

select
  case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select 
      null          p1, 
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from 
      dba_objects
    where
       owner not in 
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS', 
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */ 
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */ 
  union
    select 
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;