Privilege Matrix
This pivot query returns a matrix of privileges, grantees and grantors on tables.
select
owner,
table_name,
grantee,
grantor,
max(case when privilege = 'SELECT' then 'sel' end) sel,
max(case when privilege = 'INSERT' then 'ins' end) ins,
max(case when privilege = 'UPDATE' then 'upd' end) upd,
max(case when privilege = 'DELETE' then 'del' end) del,
max(case when privilege = 'ALTER' then 'alt' end) alt,
max(case when privilege = 'READ' then 'rea' end) rea,
max(case when privilege = 'QUERY REWRITE' then 'qrr' end) qrr,
max(case when privilege = 'DEBUG' then 'dbg' end) dbg,
max(case when privilege = 'ON COMMIT REFRESH' then 'ocr' end) ocr,
max(case when privilege = 'FLASHBACK' then 'flb' end) flb
from
dba_tab_privs
where
owner not in ('SYS', 'SYSTEM', 'APPQOSSYS', 'DBSNMP', 'GSMADMIN_INTERNAL', 'XDB', 'WMSYS')
group by
owner,
table_name,
grantee,
grantor
order by
owner,
table_name,
grantee,
grantor;
Grantors and grants
The following example shows that the same grant on the same object can be granted by different grantors.
If the grants are to be revoked completely, the must be revoked from each grantor separately.
connect / as sysdba
create user grantor_one identified by pw;
create user grantee_one identified by pw;
create user tab_owner identified by pw quota unlimited on users;
grant create session,
create table
to tab_owner;
grant create session
to grantor_one;
grant create session
to grantee_one;
connect tab_owner/pw
create table table_one (foo number);
grant select on table_one to grantor_one with grant option;
grant insert, update, delete on table_one to grantor_one;
grant select, insert on table_one to grantee_one;
connect grantor_one/pw
grant select on tab_owner.table_one to grantee_one;
connect / as sysdba
select
grantor,
grantable
-- hierarchy /* ?? */
from
dba_tab_privs
where
owner = 'TAB_OWNER' and
grantee = 'GRANTEE_ONE' and
privilege = 'SELECT' and
table_name = 'TABLE_ONE'
;
connect grantee_one/pw
select * from tab_owner.table_one;
connect tab_owner/pw
revoke all on table_one from grantee_one;
connect / as sysdba
select
grantor,
grantable
-- hierarchy /* ?? */
from
dba_tab_privs
where
owner = 'TAB_OWNER' and
grantee = 'GRANTEE_ONE' and
privilege = 'SELECT' and
table_name = 'TABLE_ONE'
;
connect grantee_one/pw
select * from tab_owner.table_one;
connect / as sysdba
drop user grantor_one cascade;
drop user grantee_one cascade;
drop user tab_owner cascade;