Using Proxy Connections in Oracle SQL Developer

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:

A relatively new feature, Proxy User Authentication allows you to connect using someone else’s credentials.

Or as Oracle-Base puts it:
Since Oracle 9i Release 2 it has been possible to create proxy users, allowing you to access a schema via a different username/password combination. This is done by using the GRANT CONNECT THROUGH clause on the destination user.

So I’m going to alter a user, SCOTT, to allow for this.

ALTER USER SCOTT GRANT CONNECT THROUGH HR;

Now, how do I tell SQL Developer to do this?
Method One
You can use the standard Oracle connect strings for proxy users, PROXY_USER[ME].

I’m SCOTT, but I want to login using HR’s stuff.

Pretty straightforward, if you know the syntax.

Method Two
You an also use the Advanced connection properties button to explicitly setup the PROXY details.

Just put in the user name

What about that proxy password field?

Well, IF you had altered SCOTT this way, you’d need to also know their password as well.

ALTER USER scott GRANT CONNECT THROUGH hr AUTHENTICATED USING PASSWORD;

With this, you’re saying, ok, i’ll let you connect through HR, but I’m still gonna need SCOTT’s password too.

What’s that ‘Distinguished Name’ stuff?

Well, if you want to use a mid tier to authenticate your user, and you want to go through Oracle Internet Directory (OID), then you’re going to need to use the Distinguished Name (DN) information.

That’ll look something like this…

CREATE USER jeff IDENTIFIED GLOBALLY AS 'CN=jeff,OU=americas,O=oracle,L=redwoodshores,ST=ca,C=us';
ALTER USER jeff GRANT CONNECT THROUGH scott AUTHENTICATED USING DISTINGUISHED NAME;

I think it’s very handy that the Database Docs like to use my name for the example, but I really don’t know more here than what the Docs say – I have no experience here using this sort of authentication. But, this is where you’d go to set up your SQLDev connection once it’s going.

Tell Others About This Story:

Comments 5

  1. As a work-around for PL\SQL apps which use mod_plsql to authenticate db users, would be possible to set up the ORDS_PUBLIC_USER in the middle-tier as a proxy user to authenticate db users?

    1. thatjeffsmith Post
      Author
  2. Nice post! For a real world example if proxy authentication in action, I use proxy authentication extensively in my workplace. It is a good way to allow developers to have access to a common application schema without having to give them shared credentials. They use their personal credentials to proxy in to the app schema, which means no one is sharing passwords with people who haven’t been cleared, and the proxy username is captured in the audit trail so we know which developers did what in the app schema. Makes everyone’s life easier: developers only have to remember one username and password, auditing is improved, and I don’t have to track who has access to what because now it’s all documented in the data dictionary.

    1. thatjeffsmith Post
      Author
    2. Hi Pete! This sounds exactly like what I am trying to do right now. I am able to proxy in to the app schema, but I don’t have any access to tables, procedures, packages, etc. Is there something else I need to do? Privileges? I was under the impression that by using this type of connection I wouldn’t need to grant schema object privileges.

Leave a Reply

Your email address will not be published. Required fields are marked *