Tuesday, 18 October 2011

Proxy Connections

While looking at some issues with specific connection upgrades, I've been playing with proxy connections in SQL Developer, and while easy to do, can be interesting to get your head around. There are a number of things to do which are important. So, Lets start with a proxy user called proxy and a target user called target. (Nice and original)
drop user proxy cascade;
drop user target cascade;
create user proxy identified by proxy;
create user target identified by target;
alter user target grant connect through proxy;
grant create session to proxy;
grant connect, resource to target;
connect target/target;
create table target (id number);
insert into target values (1);
connect proxy[target]/proxy;
show user
select * from target;

This set of commands run as Sys in the worksheet will create the two users. The proxy privilege is granted using

alter user target grant connect through proxy;

The target user is granted resource role to create a table, in this case, we call it target and put some data in it. Next, we can connect to the target user, through the proxy using

connect proxy[target]/proxy;select * from target;

This all gives us this feedback including the user which is actually connected.

user PROXY dropped.user TARGET dropped.user PROXY created.user TARGET created.user TARGET altered.grant succeeded.grant succeeded.Connectedtable TARGET created.1 rows inserted.ConnectedUSER is TARGETID-- 1 Connection created by CONNECT script command disconnected

We can also set this up in SQL Developer using the connection dialog. Given the users have been created as above and the appropriate privileges have been granted, we can set this up in the dialog

Once we make the connection, we can expand the table tree and see the target table from the user we proxied into to.

The data is exactly the same as the data from the Worksheet script output as well.

One thing I did forget to mention was the ability to create distinguished proxies as well. You do the same thing with the connection panels and but switch to disctinguished name. You can set up a distinguished user doing the following.

drop user dproxy cascade;
create user dproxy identified globally as 'CD=dproxy,OU=europe,O=oracle,L=omagh,ST=tyrone,C=ie';
alter user dproxy grant connect through barry authenticated using distinguished name;

No comments: