Полезные штуки для работы с ролями

Список привелегий можно получить из таблиц:
System_Privilege_Map - системные привелегии
Table_Privilege_Map - объектные привелегии

Список ролей в системе можно получить 2 способами: dba_roles, sys.user$

Список привелегий в сыром виде
sysauth$ - системные привелегии, роли
objauth$ - объектные привелегии

Список привелегий в обработанном виде
В обработанном виде, что бы посмотреть что чему грановано можно воспользоваться вьюхами: dba_tab_privs (All grants on objects in the database), dba_sys_privs (System privileges granted to users and roles), dba_role_privs (Roles granted to users and roles), dba_col_privs (All grants on columns in the database). Они представляют собой простую надстройку над сырыми таблицами, например:
create or replace view dba_tab_privs
(grantee, owner, table_name, grantor, privilege, grantable, hierarchy)
as
select ue.name, u.name, o.name, ur.name, tpm.name,
decode(mod(oa.option$,2), 1, 'YES', 'NO'),
decode(bitand(oa.option$,2), 2, 'YES', 'NO')
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
table_privilege_map tpm
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and oa.privilege# = tpm.privilege
and u.user# = o.owner#;

Как видно, в dba_tab_privs не попадают некоторые привелегии (col# IS NULL). В моей базе это привелегии
GRANT UPDATE(some_column) ON table

Кроме того, для того, что бы сравнить все привелегии, доступные пользователю, придется строить древовидные структуры из роле (привелегия дана одной роли, роль - другой роли и т.д)

Вьюха dba_role_privs построена на табличке sysauth$:
select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
decode(min(option$), 1, 'YES', 'NO'),
decode(min(u1.defrole), 0, 'NO', 1, 'YES',
2, decode(min(ud.role#),null,'NO','YES'),
3, decode(min(ud.role#),null,'YES','NO'), 'NO')
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee#=ud.user#(+)
and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
and u2.user#=sa.privilege#
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name;

Мои привелегии
Сужением указанных выше вьюх являются вьюхи:

• all_tab_privs - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
• all_col_privs - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee

Обратить внимание, что вьюх с all_sys и all_role (как раз те, что строятся на sysauth$) нет

• user_tab_privs - Grants on objects for which the user is the owner, grantor or grantee. Как видно, от all_tab_privs отличается отсутствием PUBLIC
• user_col_privs - Grants on columns for which the user is the owner, grantor or grantee
• user_sys_privs - System privileges granted to current user
• user_role_privs - Roles granted to current user

Полученные и розданные привелегии
Можно получить из вьюх:

• all_col_privs_made - Grants on columns for which the user is owner or grantor
• all_col_privs_recd - Grants on columns for which the user, PUBLIC or enabled role is the grantee
• all_tab_privs_made - User's grants and grants on user's objects
• all_tab_privs_recd - Grants on objects for which the user, PUBLIC or enabled role is the grantee
• user_col_privs_made - All grants on columns of objects owned by the user
• user_col_privs_recd - Grants on columns for which the user is the grantee
• user_tab_privs_made - All grants on objects owned by the user
• user_tab_privs_recd - Grants on objects for which the user is the grantee

Для этого верно, что xxx_made + xxx_recd = xxx, например:
SELECT grantee, grantor, table_name FROM user_tab_privs
MINUS
SELECT grantee, grantor, table_name FROM user_tab_privs_made
MINUS
SELECT USER grantee, grantor, table_name FROM user_tab_privs_recd

Где посмотреть привелегии для текущей сессии:

• table_privileges - Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee. От all_tab_privs отличается только большим набором колонок.
• session_privs - Privileges which the user currently has set. Во вьюхе содержаться системные привелегии. Сделана на основе sys.v$enabledprivs
• session_roles - Roles which the user currently has enabled. Отличается от user_role_privs тем, что роли могут быть задизейблены или отключены, например:

SQL> SELECT COUNT(*) FROM User_Role_Privs;

COUNT(*)
----------
28
SQL> SELECT COUNT(*) FROM session_roles;

COUNT(*)
----------
28
SQL> CREATE OR REPLACE PROCEDURE p IS
2 n NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO n FROM User_Role_Privs;
5 dbms_output.put_line('User_Role_Privs Count = ' || n);
6
7 SELECT COUNT(*) INTO n FROM session_roles;
8 dbms_output.put_line('session_roles Count = ' || n);
9 END;
10 /

Procedure created
SQL> EXEC p

User_Role_Privs Count = 28
session_roles Count = 0

PL/SQL procedure successfully completed


• column_privileges - Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee. От all_col_privs отличается только большим набором колонок.

Вьюхи для ролей
За исключением dba_roles, dba_role_privs, user_role_privs, session_roles, описанных выше, можно отметить следующие вьюхи:

• role_role_privs - Roles which are granted to roles
• role_sys_privs - System privileges granted to roles
• role_tab_privs - Table privileges granted to roles

Во вьюхи выбираются только роли, доступные подключенному пользователю.