OraFAQ Forum: Server Administration » Externally Authenticated user login syntax as a Proxy user

Home » RDBMS Server » Server Administration » Externally Authenticated user login syntax as a Proxy user (Oracle,11gR1,RHEL5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Externally Authenticated user login syntax as a Proxy user [message #482750]

Mon, 15 November 2010 12:49

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi Experts,

I created an externally authenticated user in database. And can login without password with below syntax.

SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"

This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.

connect scott[proxy_user]/password_for_scott@TESTDB

So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?

Thanks.

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482765 is a reply to message #482750]

Mon, 15 November 2010 13:27

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Only available in 11gR2.

Regards
Michel

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482767 is a reply to message #482765]

Mon, 15 November 2010 13:31

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Syntax is:

SQL> connect [michel]/
Connected.

SQL> select user, sys_context('userenv','proxy_user') proxy from dual;
USER                           PROXY
------------------------------ --------------------
MICHEL                         OPS$MICHEL

SQL> @v

Version Oracle : 11.2.0.1.0

Regards
Michel

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482786 is a reply to message #482767]

Mon, 15 November 2010 14:56

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Thanks Michel!!

Mentioned syntax worked for 11.1.0.6.0 as well!

Issue I'm having now is, I can connect from windows remote client as externally. But the same syntax for proxy doesn't work from CLIENT pc.

Means, below works from windows client pc:

SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"

But proxy connection syntax doesn't work from CLIENT pc

SQL> connect [proxy_user]/ @TESTDB
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /

and the same worked from database OS:

sqlplus [proxy_user]/
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 15 15:48:52 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

SQL> show user;
USER is "SCOTT"

My oracle client is Release 10.2.0.1.0

Do you think, client version is a reason for not working?

Thanks.

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482788 is a reply to message #482765]

Mon, 15 November 2010 16:18

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi Michel,

I was able to login using TOAD, just the username as [proxy_user] and it worked! but not working with sqlplus and not even with SQLDEV!

As toad works, means, the client version is fine! Not sure, what syntax it's using at backend...

Any idea?

Thanks.

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482830 is a reply to message #482786]

Tue, 16 November 2010 01:39

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Mentioned syntax worked for 11.1.0.6.0 as well!

Good, I thought it was introduced only in 11.2; damn memory!

Quote:

My oracle client is Release 10.2.0.1.0

Do you think, client version is a reason for not working?

Yes, it is ONLY a matter of SQL*Plus program.
Proxy user was introduced in 8.1.7 (if I recall clearly) and you can use it since 9.2 with an OCI client (including with with external authentification ,iirc)... if the program implements it.
SQL*Plus was upgraded to use proxy user in 10g(R2?) and with external authentication only in 11g.

Regards
Michel

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482928 is a reply to message #482830]

Tue, 16 November 2010 19:44

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi Michel,

Thanks for your feedback.

I was able to login using TOAD. Just using the syntax you gave me, ie, [proxy_user] as username and blank password.

But the same doesn't work with sqlplus/sqldev. TOAD was using the same sqlplus version.

So , I'm guessing, TOAD is using a backend syntax that should work with sqlplus as well.

Not getting any syntax help from online. Do you have any suggession regarding this?

Thanks for your guidence Smile

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #482947 is a reply to message #482928]

Wed, 17 November 2010 01:10

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

TOAD was using the same sqlplus version

I'm not a TOAD user but TOAD (GUI interface, not SQL*Plus one) is not using SQL*Plus for its connection, it uses (I think) OCI.

Regards
Michel

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #483295 is a reply to message #482947]

Fri, 19 November 2010 14:54

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi Michel,

I was playing with SQLDEVELOPER and finally got it working.

> I had to select "Proxy Connection" put the proxy user name & blank password.
> And on main connection window, I put "/" as password and can login.

But still searching the syntax for SQL*PLUS. I'll share the syntax if I find any.

Thanks Smile

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #483296 is a reply to message #483295]

Fri, 19 November 2010 15:15

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback.
The syntax for SQL*Plus is the one I mentionned and showed in my previous post and it does not work before SQL*Plus 11g, I am sure, don't waste too much time trying to find another one.

Regards
Michel

Report message to a moderator

Re: Externally Authenticated user login syntax as a Proxy user [message #483299 is a reply to message #483296]

Fri, 19 November 2010 15:43

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Thanks Michel for your guidence.

I was hoping if there are any options to mark a post as "Helpful" or "Answered" something like this.

Anyway, your guidence was awesome.

Thanks again Smile

Report message to a moderator

Previous Topic:	Are parallel queries supported in Oracle XE
Next Topic:	which document contains further information about CKPT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 21:16:26 CDT 2024