set verify off feed off lines 130 pages 150
col dependentes format a130
col privilegiados format a60
define p_owner=&1.
define p_tabela=&2.
with
temp_obj as
(
  SELECT object_id   FROM all_objects
  WHERE owner        = upper('&p_owner')
  and   object_name  = upper('&p_tabela')
  and   object_type  in( 'TABLE', 'VIEW', 'MATERIALIZED VIEW' )
),
dependencias as
(
  SELECT
    level nivel
   ,p.object_id obj
   ,p.referenced_object_id refobj
  FROM public_dependency p
  connect by prior p.object_id = p.referenced_object_id
  start with p.referenced_object_id = (SELECT object_id FROM temp_obj)
  union all
  SELECT 0, t.object_id, null
  FROM temp_obj t
),
dependencias2 as
(
  SELECT t.nivel, t.obj, t.refobj, ob.object_type, ob.owner, ob.object_name
  FROM dependencias t, dba_objects ob
  WHERE ob.object_id = t.obj
  /* and ob.object_type NOT IN ( 'SYNONYM', 'TRIGGER' ) */
  and ob.object_type NOT IN ( 'TRIGGER' )
  and  not exists
   (SELECT 1 FROM dependencias i WHERE i.obj = t.obj and i.refobj = t.refobj  and i.nivel < t.nivel )
),
get_privs as
(
  SELECT distinct obj, p.grantee grantee
  FROM (SELECT distinct obj, owner, object_name FROM dependencias2) t, dba_tab_privs p
  WHERE p.owner = t.owner
  and   p.table_name = t.object_name
),
group_privs as
(
  SELECT obj, '-->Privilegios: ' || lower( stragg(grantee) ) grantee
  FROM get_privs
  group by obj
),
dependencia_privs as
(
  SELECT 0, 0 obj, obj refobj, grantee objeto FROM group_privs
  union all
  SELECT nivel, obj, refobj, object_type||' '||owner||'.'||object_name FROM dependencias2
  order by 1, 4
)
SELECT
  /* lpad( '|->', 3*(level-1), '|  ' ) || t.objeto dependentes */
  lpad( '  ', 2*(level-1), '  ' ) || t.objeto dependentes
FROM dependencia_privs t
connect by prior obj = refobj
start with refobj is null
/

prompt

WITH DIRETO AS
(
  SELECT DISTINCT DECODE( U.TYPE#, 1, 'USER ', 'ROLE ') || GRANTEE GRANTEE, 'TABLE ' || upper('&p_tabela') GRANTED
  FROM DBA_TAB_PRIVS P, SYS.USER$ U
  WHERE TABLE_NAME = upper('&p_tabela') AND OWNER= upper('&p_owner')
  AND P.GRANTEE= U.NAME
  UNION
  SELECT 'TABLE ' || upper('&p_tabela'), NULL
  FROM  DUAL
),
ALL_PRIVS AS
(
  SELECT DECODE( U.TYPE#, 1, 'USER ', 'ROLE ') || R.GRANTEE GRANTEE, 'ROLE ' || R.GRANTED_ROLE GRANTED
  FROM DBA_ROLE_PRIVS R, SYS.USER$ U
  WHERE 'ROLE ' || R.GRANTED_ROLE IN ( SELECT DISTINCT GRANTEE FROM DIRETO )
  AND R.GRANTEE = U.NAME
  UNION
  SELECT * FROM DIRETO
)
SELECT lpad( ' ', 2*(level-1), ' ' ) || p.grantee privilegiados
FROM all_privs p
connect by prior grantee = granted
start with granted is null
/

undefine 1 2 p_owner p_tabela
set verify on feed 6 pages 66
prompt

col dependentes clear
col privilegiados clear