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;

Tuesday, 11 October 2011

SQL Developer 3.1 EA1 Available

SQL Developer 3.1 EA is now available for download here.   Have a look and try out several of the new features introduced as part of this release.  We have done a lot of work around adding DBA functionality for RMAN, Datapump and other features and vastly improved Database Diff, Database Copy and Database Export.
In Migration, we've introduced a TeraSQL translator and greatly enhance our Sybase TSQL parse. We've also done a lot of work with insight, including templates for most statements directly from published documentation and added column formatting for SQL*Plus.  These are only some of the highlights for this release and we will publish a full list soon.
As usual, you can ask us any questions on the forum or add a feature request.  Let us know what you think!

Wednesday, 5 October 2011

Tuning, Refactoring and Instrumentation

For all those who attended the talk today, this is a promise partially fulfilled in that I had said I would post in more detail about the talk.  At a high level, we talked about three different topics.  Tuning, Refactoring and Instrumentation.  I'll list out the main points here today and flesh these out over the next few days for each of the bullet points.

SQL Developer support several types of tuning activities.  These are:

  • Explain Plan/ Autotrace and Diff
  • Monitoring SQL
  • SQL Tuning Advisor
  • PLSQL Hierarchical Profiler
For Refactoring, there are several options as well, and these grow with every release.
  • Code Surrounding
  • Procedure extraction
  • Local variable renaming
  • Obfuscation
Instrumentation is a way of finding out what an application is doing, who is using it, how its doing and and how long it has taken.  In order to look at this from a database application point of view, we can break application instrumentation into a number of key areas.
  • Debugging
  • Logging
  • Runtime registration
  • Metric Collection
Each of these key areas have specific tools and processes which should be implemented as part of your application development and we will get into those in the section on Instrumentation.