tst_proxy_mult.sql

-- ---------------------------------------------------------------
-- tst_proxy_mult.sql
-- Pete Finnigan
-- 18-Nov-2010
--
-- This simple script demonstrates that proxy clients can connect
-- to more than one central account. So as an example a central
-- DBA or schema could be accessed by more than one client proxy
-- account. Conversly also a single client proxy account could be
-- used to access more than one central powerful account such as
-- a DBA and a SCHEMA account.
--
-- ---------------------------------------------------------------

spool tst_proxy_mult.lis

doc
	Connect as SYSTEM and remove the two proxy client accounts and the
	two proxy schemas.

	Re-create the four accounts

	We are going to connect to two schemas from one client and then
	to one schema from two clients.

	Allow both clients to connect through one schema

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

	SQL> drop user p1_client cascade;
	SQL> drop user p2_client cascade;
	SQL> drop user p1_schema cascade;
	SQL> drop user p2_schema cascade;

	SQL> grant create session to p1_client identified by p1_client;
	SQL> grant create session to p2_client identified by p2_client;

	SQL> grant create session to p1_schema identified by p1_schema;
	SQL> grant create session to p2_schema identified by p2_schema;

	SQL> alter user p1_schema grant connect through p1_client;
	SQL> alter user p1_schema grant connect through p2_client;

#

pause


-- connect sys to admin
connect system/oracle1@//192.168.58.131:1521/orcl

-- remove the accounts
drop user p1_client cascade;
drop user p2_client cascade;
drop user p1_schema cascade;
drop user p2_schema cascade;


-- create two proxy end users
grant create session to p1_client identified by p1_client;
grant create session to p2_client identified by p2_client;

-- create two proxy schemas
grant create session to p1_schema identified by p1_schema;
grant create session to p2_schema identified by p2_schema;

-- allow both client to proxy through one schema
alter user p1_schema grant connect through p1_client;
alter user p1_schema grant connect through p2_client;

-- connect as both clients through schema 1

doc
	connect to each proxy client in turn and proxy to the central
	DBA account. This shows that a single shared DBA account can
	be used by multiple clients


	SQL> connect p1_client[p1_schema]/p1_client@//192.168.58.131:1521/orcl
	SQL> @check

	SQL> connect p2_client[p1_schema]/p2_client@//192.168.58.131:1521/orcl
	SQL> @check


#
pause

connect p1_client[p1_schema]/p1_client@//192.168.58.131:1521/orcl
@check

connect p2_client[p1_schema]/p2_client@//192.168.58.131:1521/orcl
@check

doc

	connect as SYSTEM and allow the second schema to be connected to by the
	first proxy client. This demonstrated that one client account can connect
	to multiple schema/DBA accounts

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

	SQL> alter user p2_schema grant connect through p1_client;

	SQL> select * from proxy_users;

	SQL> connect p1_client[p1_schema]/p1_client@//192.168.58.131:1521/orcl
	SQL> @check

	SQL> connect p1_client[p2_schema]/p1_client@//192.168.58.131:1521/orcl
	SQL> @check

#
pause

-- now allow one client, p1 to connect through schema 1 and 2
connect system/oracle1@//192.168.58.131:1521/orcl

alter user p2_schema grant connect through p1_client;

-- select out the proxy
select * from proxy_users;

-- connect now through bioth schemas
connect p1_client[p1_schema]/p1_client@//192.168.58.131:1521/orcl
@check
-- now the second
connect p1_client[p2_schema]/p1_client@//192.168.58.131:1521/orcl
@check


spool off