Oracle:查找具有特定权限的所有用户

Question

我在数据库中作为sys用户运行，我想查找所有直接或间接授予CREATE ANY JOB权限的用户。

我知道dba_sys_privs可以找到那些直接授予的特权，但间接的呢？例如，用户的权限由一个角色授予，甚至由另一个角色赋予。

Answer 1

正如RenéNyffenegger所说：

SQL> set pagesize 100
SQL> set ver off
SQL>
SQL> select
2    lpad(' ', 2*level) || c "Privilege, Roles and Users"
3  from
4    (
5    /* THE PRIVILEGES */
6      select
7        null   p,
8        name   c
9      from
10        system_privilege_map
11      where
12        name like upper('%&enter_privliege%')
13    /* THE ROLES TO ROLES RELATIONS */
14    union
15      select
16        granted_role  p,
17        grantee       c
18      from
19        dba_role_privs
20    /* THE ROLES TO PRIVILEGE RELATIONS */
21    union
22      select
23        privilege     p,
24        grantee       c
25      from
26        dba_sys_privs
27    )
28  start with p is null
29  connect by p = prior c;
Enter value for enter_privliege: ALTER ANY TABLE
Privilege, Roles and Users
--------------------------------------------------------------------------------
ALTER ANY TABLE
DBA
SYS
SYSTEM
IMP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
SYS
SYSTEM
SYS
DBA
SYS
SYSTEM
SYS
MDSYS
SYS
WMSYS
17 rows selected.
SQL>