tst_proxy_DBA.sql

-- ---------------------------------------------------------------
-- tst_proxy_DBA.sql
-- Pete Finnigan
-- 18-Nov-2010
--
-- This simple script tests the use of a proxy account to allow
-- access and to modify that schema without access to
-- the schema password but also allowing accountability of the 
-- actual end user who modifies the schema.
--
-- This example can be used to create a central DBA account
-- that has "power" but not the DBA role. This account then should
-- have an impossible password (it cannot be locked). The actual 
-- DBA's (people) should have client accounts with very limited 
-- day to day privileges. Then when they need power they proxy 
-- to the central DBA. This access is then audited as the real
-- end user. This allows power without duplication of privileges
-- and also allows accountability for a shared account.
--
-- the client is called proxy_client and the central DBA is 
-- called dbap
--
-- ---------------------------------------------------------------

var sessionid_no number
var sqlstr varchar2(300)

spool tst_proxy_DBA.lis

/*
Connect as system and remove the schema and end user accounts
Note: Change the system password in this script for your own
system password
*/

connect system/oracle1@//192.168.58.131:1521/orcl

--
-- create a dummy DBA user
--

doc
 Connect as SYSTEM and drop the central DBA account (if it exists) and
 then create the central DBA account and grant it the DBA role. The DBA
 is not neded to make proxy work; its just an example DBA account

 SQL> drop user dbap cascade;

 SQL> create user dbap identified by dbap;

 SQL> grant create session to dbap;

 SQL> grant dba to dbap;

# 

pause

drop user dbap cascade;

create user dbap identified by dbap;

grant create session to dbap;

grant dba to dbap;


doc 

 Drop the client user that will be used to proxy to the central DBA account
 Then re-create it. Grant only create session and then alter the DBA account
 to allow the client to connect through it.

 SQL> drop user proxy_client;

 SQL> create user proxy_client identified by proxy_client;

 SQL> grant create session to proxy_client;

 SQL> alter user dbap grant connect through proxy_client

#

pause

-- drop the client user

drop user proxy_client;

/*
Create the end user PROXY_CLIENT
*/

-- create the client (proxy)

create user proxy_client identified by proxy_client;

grant create session to proxy_client;

/* 
Allow the proxy access to the central DBA - actually reverse proxy
as we are allowing the schema to be accessed by the end user.
This is the opposite way to normal use of this feature
*/


alter user dbap grant connect through proxy_client;

doc

 Audit connections on behalf of the DBA to the client

 SQL> audit create procedure by proxy_client on behalf of dbap;

 SQL> select * from dba_priv_audit_opts where proxy_name is not null;

#

pause

/*
Turn on audit to capture the client user creating a procedure as
the DBA
*/


audit create procedure by proxy_client on behalf of dbap;

--
-- check if the audit is enabled
--


select * from dba_priv_audit_opts where proxy_name is not null;

/*
Connect as the end user to the schema using proxy authentication.
NOTE: we do not need to have the DBA password to connect as DBA
*/

doc

 Connect as the proxy client to the DBA account and get the session ID so that
 we can see the generated audit records. Print this out and then create a procedure
 to test DBA activities

 SQL> Sconnect proxy_client[dbap]/proxy_client@//192.168.58.131:1521/orcl
 SQL> @check

 SQL> begin
 SQL>	select to_number(sys_context('USERENV','SESSIONID')) into :sessionid_no from sys.dual;
 SQL> end;
 SQL> /

 SQL> set serveroutput on size 1000000
 SQL> begin
 SQL> 	dbms_output.put_line('DEBUG:'||:sessionid_no);
 SQL> end;
 SQL> /
 
 SQL> create procedure ad as
 SQL> begin
 SQL> null;
 SQL> end;
 SQL> /

#

pause

connect proxy_client[dbap]/proxy_client@//192.168.58.131:1521/orcl
@check

begin
	select to_number(sys_context('USERENV','SESSIONID')) into :sessionid_no from sys.dual;
end;
/

set serveroutput on size 1000000
begin
	dbms_output.put_line('DEBUG:'||:sessionid_no);
end;
/
/* 
Create a procedure in the schema as the central DBA user
*/

--
-- no need to drop the procedure as we do a drop schema cascade above
--

create procedure ad as
begin
null;
end;
/

/* 
Now connect as SYSTEM - CHANGE THE PASSWORD - and check the object
was created and whether an audit trail exists
*/


doc 
 Connect as SYSTEM and check the audit records generated

 SQL> connect system/oracle1@//192.168.58.131:1521/orcl

 SQL> select owner,object_name,object_type from dba_objects
 SQL> where object_name='AD';
 
 SQL> set serveroutput on size unlimited

 SQL> declare
 SQL> 	--
 SQL> 	cursor c_main(s in number) is
 SQL> 	select  username,
 SQL> 		comment_text,
 SQL> 		timestamp,
 SQL> 		action_name,
 SQL> 		obj_name,
 SQL> 		priv_used,
 SQL> 		proxy_sessionid
 SQL> 	from dba_audit_trail
 SQL> 	where sessionid=s;
 SQL> 	--
 SQL> 	cursor c_prox (s in number) is
 SQL> 	select username,
 SQL> 		sessionid,
 SQL> 		comment_text
 SQL> 	from dba_audit_trail
 SQL> 	where sessionid=s;
 SQL> 	--
 SQL> 	lv_prox c_prox%rowtype;
 SQL> begin
 SQL> 	for lv_main in c_main(:sessionid_no) loop
 SQL> 		open c_prox(lv_main.proxy_sessionid);
 SQL> 		fetch c_prox into lv_prox;
 SQL> 		close c_prox;
 SQL> 		
 SQL> 		dbms_output.put_line('----------------------------------------------------------');
 SQL> 		dbms_output.put_line('User:'||lv_main.username);
 SQL> 		dbms_output.put_line('Action: '||lv_main.action_name);
 SQL> 		dbms_output.put_line('Timestamp:'||lv_main.timestamp);
 SQL> 		dbms_output.put_line('object:'||lv_main.obj_name);
 SQL> 		dbms_output.put_line('Privilege:'||lv_main.priv_used);
 SQL> 		dbms_output.put_line('Proxy:'||lv_prox.username);
 SQL> 		dbms_output.put_line('----------------------------------------------------------');
 SQL> 	end loop;
 SQL> end;
 SQL> /

#

pause
-- see what evidence is left in the audit trail
connect system/oracle1@//192.168.58.131:1521/orcl

select owner,object_name,object_type from dba_objects
where object_name='AD';

set serveroutput on size unlimited

declare
	--
	cursor c_main(s in number) is
	select  username,
		comment_text,
		timestamp,
		action_name,
		obj_name,
		priv_used,
		proxy_sessionid
	from dba_audit_trail
	where sessionid=s;
	--
	cursor c_prox (s in number) is
	select username,
		sessionid,
		comment_text
	from dba_audit_trail
	where sessionid=s;
	--
	lv_prox c_prox%rowtype;
begin
	for lv_main in c_main(:sessionid_no) loop
		open c_prox(lv_main.proxy_sessionid);
		fetch c_prox into lv_prox;
		close c_prox;
		
		dbms_output.put_line('----------------------------------------------------------');
		dbms_output.put_line('User:'||lv_main.username);
		dbms_output.put_line('Action: '||lv_main.action_name);
		dbms_output.put_line('Timestamp:'||lv_main.timestamp);
		dbms_output.put_line('object:'||lv_main.obj_name);
		dbms_output.put_line('Privilege:'||lv_main.priv_used);
		dbms_output.put_line('Proxy:'||lv_prox.username);
		dbms_output.put_line('----------------------------------------------------------');
	end loop;
end;
/

/*
Turn the audit back off
*/
-- turn off audit
noaudit create procedure by proxy_client on behalf of dbap;

--

spool off