/*
 * @desc    Revoke EXECUTE privilege on VULNERABLE PACKAGES from PUBLIC
 * @date    30.08.2011 
 * 
 * @author  Petr Juhanak petr(at)juhanak.cz
 * @tested  Oracle DB 10.2.0.5
 * 
 * @desc
 *   1) Always perform database backup to be able revert changes in dictionary   
 */  



/*
 * find dependecies for dangerous packages
 */ 
set linesize 200
set pages 999
column c2 heading "object|name"          format a40
column c3 heading "referencing|object"   format a40

select c2,c3, dependency_type from (
select   owner || '.' || name || ' (' || type || ')' as c2,
   referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')' as c3,
	owner, referenced_owner as rowner, type, referenced_type as rtype, dependency_type
from dba_dependencies
where 
type LIKE 'PACKAGE%'
and lower(NAME) IN ('utl_http',
'utl_tcp',
'utl_file',
'utl_inaddr',
'utl_smtp',
'utl_dbws',
'dbms_lob',
'dbms_random',
'dbms_obfuscation_toolkit',
'dbms_crypto_toolkit',
'dbms_advisor',
'dbms_ldap',
'dbms_ldap_utl',
'dbms_job',
'dbms_sql',
'dbms_scheduler',
'dbms_ddl',
'dbms_epg',
'dbms_xmlgen',
'dbms_aw_xml',
'ctxsys.drithsx',
'ordsys.ord_dicom')
)
where owner != rowner
/

/* 
 * WHAT EXECUTE PUBLIC HAS 
 */
set linesize 200 pages 999
col grantee for a8
col privilege for a10
col table_name for a30
--
select grantee, privilege, table_name from dba_tab_privs 
where GRANTEE='PUBLIC' AND PRIViLEGE='EXECUTE' 
--and (table_name LIKE 'DBMS_%' or table_name LIKE '%UTL_%' ) ORDER BY table_name
AND lower(TABLE_NAME) IN ('utl_http',
'utl_tcp',
'utl_file',
'utl_inaddr',
'utl_smtp',
'utl_dbws',
'dbms_lob',
'dbms_random',
'dbms_obfuscation_toolkit',
'dbms_crypto_toolkit',
'dbms_advisor',
'dbms_ldap',
'dbms_ldap_utl',
'dbms_job',
'dbms_sql',
'dbms_scheduler',
'dbms_ddl',
'dbms_epg',
'dbms_xmlgen',
'dbms_aw_xml',
'ctxsys.drithsx',
'ordsys.ord_dicom');

/*
 *  Following revoke commands will invalidate some system packages
 */ 
revoke execute on utl_http 	from public force; 
revoke execute on utl_tcp  	from public force;
revoke execute on utl_file 	from public force; 
revoke execute on utl_inaddr 	from public force; 
revoke execute on utl_smtp 	from public force; 
revoke execute on dbms_lob 	from public force; 
revoke execute on dbms_random 	from public force; 
revoke execute on dbms_obfuscation_toolkit from public force;
revoke execute on dbms_crypto_toolkit  from public force;
revoke execute on dbms_advisor  from public force; 
revoke execute on dbms_ldap 	from public force; 
revoke execute on dbms_ldap_utl from public force; 
revoke execute on dbms_ddl 	from public force; 
revoke execute on dbms_aw_xml 	from public force; 
revoke execute on ctxsys.drithsx from public force; 
revoke execute on ordsys.ord_dicom from public force;

revoke execute on dbms_sql 	from public force;
revoke execute on dbms_job 	from public force;
revoke execute on dbms_xmlgen 	from public force;
revoke execute on dbms_epg 	from public force;
revoke execute on dbms_scheduler from public force;
revoke execute on utl_dbws 	from public force;

-- revoke and recompile
revoke execute on HTTPUriType from public force;
  alter type SYS.HTTPURITYPE COMPILE BODY;
  alter package SYS.URIFACTORY COMPILE BODY;


/*
 *  GRANT missing EXECUTE on PLSQL package
 */ 
set linesize 200 pages 999
col text for a80
SELECT DISTINCT'GRANT EXECUTE ON ' || d.owner||'.'||obj.required_package || ' TO ' || obj.owner || ';' as text from DBA_dependencies d,
(SELECT DISTINCT o.owner, o.object_name as package, REPLACE(SUBSTR(e.text,INSTR(e.text,'''')+1,100),''' must be declared','') as required_package 
FROM dba_objects o, dba_errors e  
WHERE o.status='INVALID' 
  AND o.object_type='PACKAGE BODY'
  AND e.owner = o.owner AND e.name = o.object_name
  AND e.text LIKE '%must be declared%'
) obj 
WHERE d.name = obj.required_package AND d.type = 'PACKAGE BODY'

/*
-- show dependecies only
set linesize 200 pages 999
col owner for a25
col package for a25
col error_text for a80
SELECT DISTINCT o.owner, o.object_name as package, REPLACE(SUBSTR(e.text,INSTR(e.text,'''')+1,100),''' must be declared','') as error_text 
FROM dba_objects o, dba_errors e  
WHERE o.status='INVALID' 
  AND o.object_type='PACKAGE BODY'
  AND e.owner = o.owner AND e.name = o.object_name
 -- AND e.text LIKE '%must be declared%'
/

*/

/*
 *  MANUAL RECOMPILATION OF INVALID PACKAGES
 */ 
set linesize 200 pages 999
col object_name for a40
col object_type for a40
col owner for a25
col text for a80
SELECT owner, object_name, object_type, status FROM dba_objects WHERE status='INVALID' order by owner, object_name;
--
-- recompile package body
SELECT 'ALTER PACKAGE '||owner||'.'||object_name|| ' COMPILE BODY;' as text FROM dba_objects WHERE status='INVALID' AND object_type='PACKAGE BODY' order by owner, object_name;

-- recompile whole schema
EXEC DBMS_UTILITY.compile_schema(schema => 'MDSYS');
EXEC DBMS_UTILITY.compile_schema(schema => 'ORDSYS');

-- recompile public synonym
Select 'alter public synonym '||object_name||' compile;' from dba_objects where status <> 'VALID' and owner = 'PUBLIC' and object_type = 'SYNONYM';


/* FINAL CHECK - component status */
set linesize 200 pages 999
col comp_name for a40
col version for a10
col status for a10
SELECT SCHEMA, COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;


/* DEINSTALL PLSQL packages 
 ===========================
oracle interMedia oracle_home/ord/im/admin/README.txt
  * @?\ord\im\admin\imdinst.sql
  * @?\ord\im\admin\imdtyp.sql  
Script To Check If interMedia Is Being Used In Oracle Version 10.2 [ID 458123.1]
  

Steps for Manual De-installation of Oracle Spatial [ID 179472.1]
*/