Thursday, June 5, 2014

Script used to re-grant privilege after revoking from public

Below script is enhanced version to skip these already granted privilege. The original script could be found here: Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

SQL> select unique statements from
  2  (
  3   select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
  4   from dba_dependencies d left join dba_tab_privs p
  5  on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
  6   where d.referenced_owner in ('SYS','PUBLIC')
  7  and d.referenced_type in ('PACKAGE','SYNONYM')
  8  and d.referenced_name in
  9     (
10     'DBMS_RANDOM',
11     'DBMS_EXPORT_EXTENSION',
12     'UTL_FILE',
13     'DBMS_JOB',
14     'DBMS_LOB',
15     'UTL_SMTP',
16     'UTL_TCP',
17     'UTL_HTTP'
18     )
19  and d.owner <> 'SYS'
20  and d.owner <> 'PUBLIC'
21  )
22  order by replace(statements,'-- ','')
23  /

STATEMENTS
--------------------------------------------------------------------------------
-- grant execute on DBMS_JOB to APEX_030200;
-- grant execute on DBMS_JOB to APEX_040200;
grant execute on DBMS_JOB to DBSNMP;
grant execute on DBMS_JOB to XDB;
-- grant execute on DBMS_LOB to APEX_030200;
-- grant execute on DBMS_LOB to APEX_040200;
grant execute on DBMS_LOB to CTXSYS;
grant execute on DBMS_LOB to MDSYS;
-- grant execute on DBMS_LOB to ORDPLUGINS;
-- grant execute on DBMS_LOB to ORDSYS;
-- grant execute on DBMS_LOB to WMSYS;
grant execute on DBMS_LOB to XDB;
-- grant execute on DBMS_RANDOM to APEX_030200;
-- grant execute on DBMS_RANDOM to APEX_040200;
grant execute on DBMS_RANDOM to DBSNMP;
grant execute on DBMS_RANDOM to MDSYS;
-- grant execute on UTL_FILE to APEX_030200;
grant execute on UTL_FILE to MDSYS;
-- grant execute on UTL_FILE to ORACLE_OCM;
-- grant execute on UTL_FILE to ORDPLUGINS;
-- grant execute on UTL_FILE to ORDSYS;
-- grant execute on UTL_FILE to WMSYS;
grant execute on UTL_FILE to XDB;
-- grant execute on UTL_HTTP to APEX_030200;
-- grant execute on UTL_HTTP to APEX_040200;
grant execute on UTL_HTTP to MDSYS;
-- grant execute on UTL_HTTP to ORDPLUGINS;
-- grant execute on UTL_SMTP to APEX_030200;
-- grant execute on UTL_SMTP to APEX_040200;

29 rows selected.

----------------------------------------

select unique statements from
(
select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
from dba_dependencies d left join dba_tab_privs p
on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
where d.referenced_owner in ('SYS','PUBLIC')    
and d.referenced_type in ('PACKAGE','SYNONYM') 
and d.referenced_name in
    (
    'DBMS_RANDOM',
    'DBMS_EXPORT_EXTENSION',
    'UTL_FILE',
    'DBMS_JOB',
    'DBMS_LOB',
    'UTL_SMTP',
    'UTL_TCP',
    'UTL_HTTP'
    )
and d.owner <> 'SYS'
and d.owner <> 'PUBLIC'
)   
order by replace(statements,'-- ','')