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.
Method Two
You an also use the Advanced connection properties button to explicitly setup the PROXY details.
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.
11 Comments
The Method 1 works, the method 2 (use db Proxy) no longer works in 22.2
The major difference between 1 and 2 is that 2 requires create session but 1 doesn’t
In 1 you have
user=”hr[scott]”
password=”xxx”
Conn=DriverManager.getConnection(url, properties)
In 2 you have
user=”hr”
password=”xxx”
Conn=DriverManager.getConnection(url, properties)
OC = (OracleConnection) conn;
OC.openProxySession…
So in 2 user HR must have CREATE SESSION privilege
It is a jdbc thing, not related to SQL Dev, sorry for the confusion
Hello! Does anybody know if there is a way to use Proxy Connections in SQL Developer Web?
Yes, I know the answer.
It’s currently not supported. You need to be able to login as the REST enabled schema USER account.
It workes if u write your login and pass on 1st tab and main role login on 2nd window (without password)
Thanks for this post. I think it should be “ME[PROXY_USER]”
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?
No.
But, hopefully the next version of ORDS will allow MOD-PLSQL folks to use DB authentication for their REST calls.
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.
Thanks for sharing this Pete! This type of info adds real value to others who end up here.
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.