Skip to Main Content
  • Questions
  • connecting as another user without their password

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Hani .

Asked: May 02, 2000 - 1:20 pm UTC

Answered by: Tom Kyte - Last updated: September 14, 2015 - 2:26 am UTC

Category: - Version:

Viewed 100K+ times! This question is

You Asked

I have an amazing trick, all what I need if it possible
to connect as x user
without changing his password if I am SYS, SYSTEM or internal.



and we said...


You *have* this amazing trick or you would *like to have* this amazing trick.

You cannot have this amazing trick because you do have to change their password for a second -- but -- you can change it right back (without ever knowing what it was). Is that enough of a trick?

See the SQLPlus script at </code> http://asktom.oracle.com/Misc/su.html <code>



and you rated our response

  (40 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

July 14, 2004 - 12:45 pm UTC

Reviewer: A reader

Very useful. However, if we are using password verification routines that prevent reuse of a password this may not work. Also if profiles are used to force password resetting every N days, this might interupt that schedule.

Is there a better way? I assume that, instead of logging in a a new user, we can mimic his environment by setting the current schema to his and using dbms_session to set the privileges..........Not sure what I am talking about, but is there any such way?


Tom Kyte

Followup  

July 14, 2004 - 9:56 pm UTC

an appropriately privileged person can do all of that -- yes.

you can


alter session set current_schema=them;


then, anytime the schema name ISN'T supplied (eg: create procedure p -- no schema), the schema will be "them"

July 15, 2004 - 12:09 am UTC

Reviewer: A reader

But what about the privileges? Setting the current_scheam does not set the privileges same as the other user, does it? How do we have an environment with equivalent privs?

Tom Kyte

Followup  

July 15, 2004 - 12:05 pm UTC

i said "an appropriately privileged user"

either your account has it -- or it doesn't.

July 15, 2004 - 12:15 pm UTC

Reviewer: A reader

Dear Tom,

Sorry if I am not able to get across to you what I mean. I am talking about a "Not appropriately privileged user" tryign to mimic another users environment.

I cannot use the change passwod script which you provided above, because of the password reset function. What I am lookig for is:

1. change the current schema to the new user (I know how to do it)
2. Temporarily assume the same privilege as the other user. (I am asking you if it is possible to do it and if yes how).

Thanks.

Tom Kyte

Followup  

July 15, 2004 - 5:45 pm UTC

no, it is not possible you would have to actually become that user.


login as another user

July 29, 2004 - 7:14 am UTC

Reviewer: Andy from Germany

I've received a request to allow certain "superusers" to log-in from our app as another user without knowing that user's password. Could I adapt your su.sql script so that the following happens:

1. I log in as "joe", and later (i.e. as a seperate action) request a change to user "bob".
2. A stored procedure in the db looks up in a table to see if "joe" is one of the recognised superusers, and, if so, executes something similar to su.sql to temporarily change bob's password, re-connect me as "bob", and set his password back.

How would I do the "re-connect me as bob" bit? Do I need definer rights so that "joe" can execute the procedure with alter user privileges even though he does not have them?

Tom Kyte

Followup  

July 29, 2004 - 11:53 am UTC

you cannot do this via a stored procedure.

a stored procedure runs in a session.
you cannot change the session without stopping the plsql.
but the plsql is trying to change the session.
catch 22

your client code has to do this.

I don't get it.

July 29, 2004 - 1:10 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

Not to change the subject,
But of what conceivable legitimate use could this be?
Why can't the “superuser” just do what ever it is they are going to do on their own login?
Is there some purpose for doing something like this other than to confound auditing?
Chuck



Having trouble getting to your osi directory.

March 07, 2005 - 11:12 am UTC

Reviewer: Stewart W. Bryson from Atlanta, GA USA

I haven't been able to get to the </code> http://asktom.oracle.com/~tkyte/Misc <code>directory lately. I thought you might like to know.

Thanks.

Tom Kyte

Followup  

March 07, 2005 - 7:48 pm UTC

change osi to asktom, hostname went away



More information

March 07, 2005 - 11:14 am UTC

Reviewer: Stewart W. Bryson from Atlanta, GA USA

Sorry... the error I get is "connection refused" using either Firefox or IE.

Here is the link

March 07, 2005 - 9:05 pm UTC

Reviewer: Neelz from Japan

Hi Stewart

Here is the link

</code> http://asktom.oracle.com/~tkyte/Misc/su.html <code>

Thanks Neez

March 08, 2005 - 3:34 pm UTC

Reviewer: Stewart W. Bryson from Atlanta, GA USA


To provide the grants ...

September 21, 2005 - 9:59 am UTC

Reviewer: Jim Lyons from Italy

Hi, there is a need for me to do something like this because the schema "S" containing the objects needs to have OPS$ prefixed to it, to allow mainenance users to log in without a password. This would necessitate a carefull scrutiny of the application in case there are references to <schema>.<object_name> anywhere.
A workaround would be to leave the schema as "S" and create another schema OPS$S with a logon trigger to set CURRENT_SCHEMA to "S". So I only need to sort out the grants now. My idea was to create a dynamic procedure in the "S" schema to do them and execute it from the logon procedure in the "OPS$" schema. This would avoid maintenance every time a new object was created in the "S" schema.
This might help someone but any better ideas would be appreciated.

Tom Kyte

Followup  

September 21, 2005 - 7:34 pm UTC

OPS$ is OPTIONAL

ops$tkyte@ORA9IR2> show parameter os_authent_prefix;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
ops$tkyte@ORA9IR2> alter system set os_authent_prefix='' scope=spfile;

System altered.

ops$tkyte@ORA9IR2> connect / as sysdba;
Connected.
ops$tkyte@ORA9IR2> startup force
ORACLE instance started.

Total System Global Area  152114296 bytes
Fixed Size                   451704 bytes
Variable Size             117440512 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
ops$tkyte@ORA9IR2> create user tkyte identified externally;

User created.

ops$tkyte@ORA9IR2> grant connect to tkyte;

Grant succeeded.

ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> show user;
USER is "TKYTE"
ops$tkyte@ORA9IR2>


You don't need to use OPS$ 

September 28, 2005 - 3:58 am UTC

Reviewer: Jim Lyons from Italy

Hi Tom,
with OPS$ I can have the best of both worlds!
(or "the wife drunk and the barrel full" as they say here in Italy).

With os_authent_prefix=OPS$ I can create the user as:
CREATE USER OPS$U1 IDENTIFIED BY <pwd>;
and either login from the OS user U1 with:
sqlplus /
or from any OS or remote user with:
sqlplus OPS\$U1/<pwd>@<tnsalias> (or maybe without the "\").

Unfortunately this dosen't work when the os_authent_prefix=''.
I can either define the user as:
CREATE USER U1 IDENTIFIED BY <pwd>;
Then I can login as a remote user with:
sqlplus U1/<pwd>@<tnsalias>
But I can't login as OS user U1 with:
sqlplus /

Else I can do:
CREATE USER U1 IDENTIFIED EXTERNALLY;
Then I can login from OS user U1 with:
sqlplus /
But I can't login remotely with:
sqlplus U1/<pwd>@<tnsalias>

Either the wife drunk OR the barrel full!

Unless you could help me to find a way to help me Tom I'm back where I started from.
Thanks, Jim.


Tom Kyte

Followup  

September 28, 2005 - 9:54 am UTC

did not know you needed both - it was not apparent.

sorry - you need the prefix to have both at the same time.

Thanks

September 29, 2005 - 2:44 am UTC

Reviewer: A reader

Thanks Tom - I wasn't clear.

Is su.sql still appropriate for 9i, 10g, 11g?

August 18, 2007 - 2:07 pm UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Tom,

Do you still consider your su.sql script for connecting as another user, that you gave in this thread, at the link http://asktom.oracle.com/~tkyte/Misc/su.html appropriate for currently supported Oracle versions (9i, 10g, 11g) or is there a better way now? I see that this is a very old thread and was wondering if it still applies. Recently, somebody on the OraFAQ forums, who said he had sys privileges, asked for a way to connect as another user, in order to create a private database link to a remote database, without having to ask the user for his password or permanently change the user's password, which would affect other things, or supply the remote password to the user. In response, I supplied a link to your script. A fellow moderator expressed concern that this might not be appropriate for current versions and that by supplying your script, I might be providing someone with a method for hacking or some such illegitimate purpose. My initial thinking was that if you thought it was appropriate to provide the script publicly on your website, then that was good enough for me. However, it was pointed out by my fellow moderator that, as the years go by, and things change from version to version, your point of view sometimes changes and you may not remove or update old threads. So, I would appreciate it if you would please share your current thoughts on this topic.

Thanks,
Barbara

Tom Kyte

Followup  

August 22, 2007 - 10:15 am UTC

well I would disagree with "providing someone with a method for hacking or some such illegitimate purpose. " entirely - if you have select on dba_users, and you have the EXCESSIVELY POWERFUL "alter user" privilege - you can of course become that user. And you can set the password back.

This is well known, this is documented, this is the way it works.

And thus, you would NOT grant alter user to just anybody.

So, this is still "valid", but there are other ways in the year 2007.

proxy users for example, consider:

ops$tkyte%ORA10GR2> alter user scott grant connect through ops$tkyte;

User altered.

ops$tkyte%ORA10GR2> connect ops$tkyte[scott]/foobar;
Connected.
scott%ORA10GR2> select user from dual;

USER
------------------------------
SCOTT

scott%ORA10GR2> connect scott/foobar;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
scott%ORA10GR2> connect ops$tkyte/foobar;
Connected.
ops$tkyte%ORA10GR2>


Excellent! Thanks for the follow-up!

August 22, 2007 - 1:37 pm UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Tom,

Thanks for responding and providing the newer method.

Regards,
Barbara

Re: Is su.sql still appropriate for 9i, 10g, 11g?

August 22, 2007 - 2:13 pm UTC

Reviewer: Michel Cadot from France


Tom, Barbara,

I am the "fellow moderator" Barbara was talking about (although I'm more an agitator).
Here are some points I want to clarify if you have some time to waste.
I said that su.sql was first created to make easier DBA work when they have to grant privileges on objects and this was only possible by object owner.
Now this is useless for this purpose (but database link).
Now in this very specific case, the poster wanted a way to create a private database link without knowing the password of the user and without changing it as clients use this user.
I asked him several times why he does not ask the password to the user and he never answered.
This leads me to say to Barbara that giving this link may be "providing someone with a method for hacking or some such illegitimate purpose".
Maybe it was not for hacking (in the sense of connecting to a user without permission as he is SYS and can do what he wants - and connecting as SYS to create a database link is a quite bad thing but this is another question), maybe it was just to hide an erroneous "drop database link" he made but I thought that giving the link in this case was not a good thing.
Of course, hiding a script is not a (good) security policy, of course if connecting to a user is not permitted this should be audited as SYS operations should be but this is also another point.

To connect this to another thread where we said that "grant any object privilege" privilege does not allow to grant yourself an object privilege. Does creating and publishing a script to achieve this a good or bad thing?

Regards
Michel

Tom Kyte

Followup  

August 22, 2007 - 2:52 pm UTC

...
I said that su.sql was first created to make easier DBA work when they have to
grant privileges on objects and this was only possible by object owner....

correct.

... Now this is useless for this purpose ...

not useless, so much as "not necessary, not the best way" :)

.... Does
creating and publishing a script to achieve this a good or bad thing? ...

if it can be done, it will be done - not exactly sure what you meant there though?


Oh, and I updated the page su.html to point here now to the "better" su with proxy users for those that need it for whatever reason.

Is su.sql still appropriate for 9i, 10g, 11g?

August 23, 2007 - 4:54 am UTC

Reviewer: Michel CADOT from France


not exactly sure what you meant there though?
I didn't mean anything just raising a point to think. Does this be published or not? It is open for each one.

The problem with proxy authentication is that between "grant connect through" and "revoke connect through" the user cannot connect and, unlike the previous su.sql where the user can himself change back his password, he cannot revoke this "connect through" by himself.

Regards
Michel

Tom Kyte

Followup  

August 23, 2007 - 11:31 am UTC

ops$tkyte%ORA10GR2> alter user scott grant connect through ops$tkyte;

User altered.

ops$tkyte%ORA10GR2> connect ops$tkyte[scott]/foobar;
Connected.
scott%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> connect scott/tiger;
Connected.



scott may certainly connect - it would be defeating the purpose if they could not.


and the comparision to su as far as setting password 'back', if the password was set to something they didn't know, they would not actually be able to set it back as they would be locked out of their account.


the proxy is, in my opinion, a better/safer way

you still need someone with ALTER USER (hugely hugely big privilege that gives you the KEYS TO THE KINGDOM) in both cases - so the fact that the user cannot revoke is OK with me (the other person can always give themselves the ability to go back into their account whenever they like after all)

Is su.sql still appropriate for 9i, 10g, 11g?

August 23, 2007 - 5:11 am UTC

Reviewer: Michel CADOT from France


Sorry, my bad, I apologize, the user is still able to directly connect after the "grant connect through". I made a wrong test.
So this is definitively a better way to do it.

Regards
Michel

Tom Kyte

Followup  

August 23, 2007 - 11:32 am UTC

arg - one day I'll read ahead :)

I only read a review at a time.... I should start peeking forward to see if it was already addressed...

Is 'su' still appropriate for 11g ?

August 28, 2007 - 5:54 am UTC

Reviewer: Vinay Pai from London

Hi Tom,

I just read an article by Pete Finnigan :

http://www.petefinnigan.com/weblog/archives/00001085.htm

which talks about some changes to the dba_users table in 11g - specifically that the password column is not displayed anymore.

So doesn't the old 'su' technique become invalid for this release onward ?

Thanks
Vinay
Tom Kyte

Followup  

September 04, 2007 - 12:42 pm UTC

yes, it does (become invalid)

Using proxy to connect as another user

November 07, 2007 - 11:54 am UTC

Reviewer: Alex from Chicago, IL USA

Tom,
I tried new approach (using proxy) you suggested on 10g and it works perfectly, but in 9i and 8i my connect string is ignored and I am connected as myself rather than other user. I checked documentation for all versions and found reference to PROXY in CONNECT command only in 10g. Is there a way to use proxy connection in 9i and 8i and if yes, is there something special I must do?

Thanks,
Alex.
Tom Kyte

Followup  

November 07, 2007 - 6:08 pm UTC

not in sqlplus

the proxy connection feature was added in 8i - any OCI client *could* do it, but sqlplus itself did not implement it until 10g

so, yes, you can use a proxy connection in 8i, 9i.
but no, you cannot do it from sqplus.

different behavior on proxy user

November 12, 2007 - 6:39 pm UTC

Reviewer: dario from Buenos Aires, Argentina

Hello Tom:
first al all sorry for my english.
I see a different behavior in my oracle database:
Oracle Database 10g (...) Release 10.1.0.4.0 - Prod
(...)

I granted connect through

alter user someuser grant connect through other_system;

connect other_system/pass@oracle
Conectado. :-)
connect other_system[someuser]/pass@oracle
Conectado.
select user from dual;
USER
------------------------------
SYSTEM_ESIDIF

Database host is a linux and client is windows.
"SQL*Plus: Release 10.2.0.3.0 - Production on..."

What could be the cause? Is there any parameter I could change? Do I have to configure "domains" and "realms"?

Thank you.


Tom Kyte

Followup  

November 16, 2007 - 2:07 pm UTC

well, I have no idea why it would say system_esidif since you *never used that*.

cut and paste, just like I do. then maybe we'll see something useful.

different behavior on proxy user (doh)

November 20, 2007 - 4:26 pm UTC

Reviewer: dario from Buenos Aires, Argentina... 75.2°F ~ 24°C :-)

well I didn't use other_system... I did use system_esidif and then changed all (almost) after cut and paste.

start over
cut and paste on other system, a 10r2.

SQL*Plus: Release 10.2.0.3.0 - Production on Mar Nov 20 17:29:43 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL> create user identified by user;
create user identified by user
            *
ERROR en lÝnea 1:
ORA-01935: missing user or role name


SQL> create user user1 identified by user1;

Usuario creado.

SQL> create user userproxy identified by userproxy;

Usuario creado.

SQL> grant connect, resource to user1, userproxy;

Concesi¾n terminada correctamente.

SQL> alter user user1 grant connect through userproxy;

Usuario modificado.

SQL> connect user1/user1;
ERROR:
ORA-12560: TNS:error del adaptador de protocolo


Advertencia: íYa no estß conectado a ORACLE!
SQL> connect user1/user1@secsapi
Conectado.
SQL> connect userproxy/userproxy@secsapi
Conectado.
SQL> connect userproxy[user1]/userproxy@secsapi
Conectado.
SQL> select user from dual;

USER
------------------------------
USER1

(!!!!!!!!!!!)

I saw it... it didn't worked... and it works here!
So... I have read that you can't do it with system or sys...
So... i will use system_esidif (which didn't want to publish...)

SQL> connect dario/dario@pcarga_si
Conectado.
SQL> connect system_esidif/system_esidif@pcarga_si
Conectado.
SQL> alter user dario grant connect through system_esidif;

Usuario modificado.

SQL> connect system_esidif/system_esidif@pcarga_si
Conectado.
SQL> connect system_esidif[dario]/system_esidif@pcarga_si
Conectado.
SQL> select user from dual;

USER
------------------------------
SYSTEM_ESIDIF

Aha!!! I'm crazy, but for others reasons.

The first one is a 10r2 database and the last is 10r1, both hosted on linux, using the same sqlplus client on windows.

I did granted dba role to userproxy (which system_esidif have) and results didn't change.
SQL> connect system@secsapi
Introduzca la contrase±a:
Conectado.
SQL> grant dba to userproxy;

Concesi¾n terminada correctamente.

SQL> connect userproxy[user1]/userproxy@secsapi
Conectado.
SQL> select user from dual;

USER
------------------------------
USER1

I added some other especial roles and privileges.

SQL> connect system@secsapi
Introduzca la contrase±a:
Conectado.
SQL> grant select_catalog_role to userproxy;

Concesi¾n terminada correctamente.

SQL> grant create any table, select any table, unlimited tablespace, grant any o
bject privilege to userproxy;

Concesi¾n terminada correctamente.

SQL> connect userproxy[user1]/userproxy@secsapi
Conectado.
SQL> select user from dual;

USER
------------------------------
USER1

still works on 10r2.

The user system_esidif has some others roles, but they do not have system privileges, and I think they are not related to this behavior.
SQL> select * from dba_sys_privs where grantee = 'R_PCARGA_ESIDIF_IUD';

no rows selected

SQL> select * from dba_sys_privs where grantee = 'R_SEG_PCARGA';

no rows selected

SQL> select * from dba_sys_privs where grantee = 'R_CONV_PCARGA_ESIDIF';

no rows selected

I saw your reply to other thread, and I did look for it in metalink:
...
REPRODUCIBILITY: 

consistent with 9208 db, works fine with 10.2 db 

....

SUPPORTING INFORMATION: 

behavior seems specific to sqlplus 
...
Would it mean it does not work on 10r1?

Using 10r1 sqlplus client on linux (database host) gets funkier...

SQL> connect system_esidif[dario]/system_esidif
ERROR:
ORA-01017: invalid username/password; logon denied

Thank you for your reading...

Tom Kyte

Followup  

November 21, 2007 - 11:26 am UTC

sqlplus 10gr2 was the first release to do this.

and it wants a 10gr2 and above database.

It is not funkier in 10gr1 sqlplus - it was not supported, not a feature yet.

sqlplus looks to the database it connects to and figures out what it wants to be able to do - this was 10gr2 for sqlplus.

Grant privilege to user of different Database.

March 31, 2008 - 4:41 am UTC

Reviewer: Munty from India

Hi Tom,
How can I grant object privilege to the user of different database.

My workarea's mumbai user and portfolio user of portfolio databases are connected through portfolio database link(Not a public database link).
How can I grant insert,delete,select object privilege of the tables in the mumbai user of my workarea database to the portfolio user of portfolio database.

I had tried different versions of grant:

grant select on employees to portfolio
grant select on employees to portfolio@portfolio

But all are giving me error : Command not properly ended or user doesnot exist.

Can't we grant object privileges across databases.
Tom Kyte

Followup  

March 31, 2008 - 9:37 am UTC

... How can I grant object privilege to the user of different database. ...

well, in order for that user of a different database to use this grant, they'd need to log into this database, hence they are actually a user of THIS database.

so, grant to that account.


Whatever account is used by that database link - what it connects AS - that is what you would grant to.

externally authenticated user as a proxy

July 16, 2008 - 9:28 am UTC

Reviewer: Marco from Canada

Hi,

if the proxy user is an externally identified one, how shall I provide a password when connecting. For example:

alter user scott grant connect through ops$oracle;
connect ops$oracle[scott]/<?> <-- what to provide as a password, or syntax changes in that case

Thanks

Tom Kyte

Followup  

July 16, 2008 - 11:14 am UTC


sys%ORA11GR1> alter user scott grant connect through ops$tkyte;

User altered.

sys%ORA11GR1> connect [scott]
Connected.
scott%ORA11GR1>

externally authenticated user as a proxy

July 16, 2008 - 12:06 pm UTC

Reviewer: Marco from Canada

Thanks Tom. I see you used 11GR2. I wonder, why I had a problem in 10GR2. Maybe not version related, but the cause is not obvious to me.



SYS@BIFREP >select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production


SYS@BIFREP >select username from dba_users where username in ('OPS$ORACLE','SCOTT');

USERNAME
------------------------------
OPS$ORACLE
SCOTT

SYS@BIFREP >! id
uid=300(oracle) gid=301(dba)

SYS@BIFREP >alter user scott grant connect through ops$oracle;

User altered.

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

Tom Kyte

Followup  

July 17, 2008 - 11:23 am UTC

seems to be a sqlplus parsing issue, yes - that was corrected in 11g. Use of the 11g client against 10g would likely allow it to "work"

How SQL Prompt Changed?

July 17, 2008 - 6:26 am UTC

Reviewer: A reader

In your previous followup :

sys%ORA11GR1> alter user scott grant connect through ops$tkyte;

User altered.

sys%ORA11GR1> connect [scott]
Connected.

scott%ORA11GR1>

The Sqlprompt changed from sys%.. to scott.. Do we need to setup anything for that or is it new feature of 11g? I am still using your old @connect method to change the sqlprompt

Thanks


Tom Kyte

Followup  

July 17, 2008 - 11:46 am UTC

since 10g, we run the login.sql every time you connect.

my @connect just ran the login.sql now sqlplus runs it for you.

Re : Previous followup "How SQLPrompt Changed"

July 17, 2008 - 6:48 am UTC

Reviewer: A reader

Please ignore my previous followup. I just realised that
from Oracle 10g SQL*Plus attempts to execute glogin.sql and login.sql after each successful connection.

Thanks

This does not work when you are connected "/ as sysdba"

July 24, 2008 - 5:04 am UTC

Reviewer: Laurent from france

Hi Tom,

Old su.sql script works when you are connected "/ as sysdba"
Any inputs on the new method when logged in that way?

Thanks,
Laurent


$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jul 24 10:36:40 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

sys@mydb> alter user any_user grant connect through sys;
alter user any_user grant connect through sys
*
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYS'


sys@mydb> alter user any_user grant connect through "/ as sysdba";
alter user any_user grant connect through "/ as sysdba"
*
ERROR at line 1:
ORA-01918: user '/ as sysdba' does not exist


sys@mydb>

Tom Kyte

Followup  

July 24, 2008 - 10:54 am UTC

use a different user. do not use sysdba, no reason to.

How can we revert back???

July 26, 2008 - 7:34 pm UTC

Reviewer: Bhushan from India

hello Thomas,
Please correct me anywhere if i am wrong
1) ALTER USER B GRANT CONNECT THROUGH A
Gives the power to A to connect as B without knowing B's password. Right?
2) ALTER USER b REVOKE CONNECT THROUGH a
Deprives the privilege of A to connect as B Right?

However when the 'Revoke connect through A' command is RUN the session which is already connected to the DB with
a[b]/a still continues to function normally and be connected to db as B?? How is that?

DB version:10.2.0.3.0
Regards,
Bhushan
Tom Kyte

Followup  

August 03, 2008 - 2:08 pm UTC

1) correct

2) correct


how is it that a session already connected continues to function? Because it already used the privilege to connect thru, it doesn't need it anymore.


Just like revoking create session from a user wouldn't log them off - they already used the create session privilege, they are not bothered by the lack of it unless and until they attempt to create another session.

Is Tom on Leave...?

August 02, 2008 - 4:35 pm UTC

Reviewer: A reader


Tom Kyte

Followup  

August 03, 2008 - 2:06 pm UTC

umm, no? you can see that there were followups made most days this last week...

I was in Richmond VA on Monday with a customer all day, then flew to Denver for Tuesday, then flew to Trenton New Jersey for Wednesday, then flew to Indiana for Thursday and spent most of the morning on Friday waiting for a plane to fly me back to Virginia....


I do not respond to every single review/followup, I do not see them all, I see most of them. And many times, if I don't have a quit answer right there, right then, I skip it.

Trying to make it work...

October 28, 2008 - 5:15 pm UTC

Reviewer: JMaresca from Carlsbad, CA USA

I have read every entry, and I want to use proxy connections from now on (when possible). You said that the proxy logins are supposed to work in 9i rdbms, as long as you use an 11g client. Well, I am trying that, and the client accepts the syntax and appears to successfully log me in, when I check the session user ("show user"), I get my personal account, not the proxy account. I am able to login successfully through a proxied connection into a 10g database with the same client without a problem.

SQL> connect jmaresca[aradmin]@remdev
Enter password:
Connected.
SQL> show user
USER is "ARADMIN"
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> connect jmaresca[sysadm]@hrdev
Enter password:
Connected.
SQL> show user
USER is "JMARESCA"


Why isn't it working? Any ideas?

Thanks in advance,

JMaresca

Jeff
Tom Kyte

Followup  

October 28, 2008 - 7:09 pm UTC

what version of sqlplus
and what is the release of hrdev exactly?

Connecting with Proxy Users

October 28, 2008 - 9:48 pm UTC

Reviewer: Jeff from Carlsbad, CA USA

Sorry for not including this before...

sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 28 13:33:52 2008


hrdev

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production




Tom Kyte

Followup  

October 29, 2008 - 7:45 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058#532965300346548200

this is a sqlplus'ism, found Bug 5601665 on it - sqlplus instituted this logging on technique in 10g and only supports it against 10g and up - sqlplus only supports it in 10g and up, that is not that it wasn't a method you could program to connect to in 9i, rather that sqlplus won't do it.


momentarily confused...

October 29, 2008 - 2:02 pm UTC

Reviewer: Jeff from Carlsbad, CA USA

Thanks Tom,

I misunderstood, but it is clear now. I installed the 11g client thinking the 9i client was the problem. I didn't get that it was still a limitation in the latest version of sqlplus.

Regards,

ora-01031

November 10, 2008 - 4:57 pm UTC

Reviewer: Sam from OH

Hi Tom,

Please take a look at the following:

1* alter user somuser grant connect through dba_user
SQL> /
alter user somuser grant connect through dba_user
*
ERROR at line 1:
ORA-01031: insufficient privileges


The dba_user already has the dba priv granted to it.

Database Version: 10.2.0.4

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 10 14:32:44 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

What additional priv do I need to accomplish this ?
Tom Kyte

Followup  

November 11, 2008 - 3:53 pm UTC

but the user issuing the command does not have the ALTER USER privilege (and that is a hugely powerful one, be cautious who you give it to)

su on 11.2 ?

May 25, 2010 - 4:51 am UTC

Reviewer: Sokrates

using su on V<=10.2 for ages and now willing to upgrade to 11.2:
any chance to port su to 11.2 ?
(I fear: no) ?
Tom Kyte

Followup  

May 25, 2010 - 7:41 am UTC

ctl-f for

connect through


on this page to see a superior approach. No updates for su.sql from me.

For 8i Users

July 22, 2010 - 1:26 pm UTC

Reviewer: 8i User from India

Tried http://asktom.oracle.com/~tkyte/Misc/su.html link, but it was invalid. Also saw some comments that you have re-routed that link back to this page.

Agreed that 8i tricks are not valid (with release 10g, 11g) nor preferred nor the best way to do it. But for those who still sometimes need to do some grants etc. in 8i databases it will help to have that link work.

Needed to use this trick after many years for an 8i database but had forgoten the syntax, so was looking it up here.

Tom, Though I could manage (ofcourse, with the help of some old-time friends), Can you add the contents of that link (for everyone) as a followup to this comment?

less security in 11 ?

November 19, 2012 - 4:20 am UTC

Reviewer: Patrick Lelie from Belgium

We implemented the "connect through " method for releasing new stuff on our databases.  In all our 11 databases this works fine with user system but on a 10 db we get the error 28154.
How can this be explained, is 11 less secure (or 10 too secure :-) )

Testcase (the last one works) with different clients and different databases

edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY10203) $ sqlplus system@osdp

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 19 10:49:44 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user imd_application grant connect through system;
alter user imd_application grant connect through system
                                                 *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYSTEM'


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

------------------------------------------------------------------------------------------------------------------

edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY11202) $ sqlplus system@osdp

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 19 10:51:41 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user imd_application grant connect through system;
alter user imd_application grant connect through system
                                                 *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYSTEM'


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

------------------------------------------------------------------------------------------------------------------
edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY10203) $ sqlplus system@bsta

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 19 10:58:30 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user bst_schema grant connect through system;
alter user bst_schema grant connect through system
                                            *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYSTEM'


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY10203) $ . dbaDUMMY11202
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2.0.2 is /opt/oracle
edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY11202) $ sqlplus system@bsta

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 19 10:59:28 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user bst_schema grant connect through system;
alter user bst_schema grant connect through system
                                            *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYSTEM'

------------------------------------------------------------------------------------------------------------------

edmb203(/DG-OZP-P04/backup01/one_releases)(DUMMY10203) $ sqlplus system@mia01u

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 19 10:54:04 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> alter user mia_schema grant connect through system;

User altered.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> 


Tom Kyte

Followup  

November 19, 2012 - 10:27 am UTC

it is a change in behavior - an enhancement request that was implemented. They rethought the restriction and decided to remove it.

Grant privilege to user of different Database

March 30, 2013 - 8:48 am UTC

Reviewer: Chase from Columbus, OH

I have the same question as Munty above.

To ask my question independently -
I know using Proxy user authentication, schema B can access schema A (just as schema A - by compiling objects in schema A,dropping, creating objects etc)

But, when logged in as original schema i.e. schema A, user can compile code having dblinks,fetch data through dblinks.

As a proxy schema A (i.e. schema B logging in as schema A through proxy Authentication), dblinks are not accessible. Data is not being able to be fetched.

Am I right (do you agree with my above exaplanation)? If so, is there a solution for this?
Tom Kyte

Followup  

April 22, 2013 - 12:43 pm UTC

"current user" dblinks are not - since the password for A is not known when you proxy in (the remote database is thinking "A" is logging in, but the local database doesn't have A's credentials.

If you create a database link with a username/password associated with it - it will "work". It is database links without usernames/passwords (current user dblinks) that would fail in this case.

how to implement above change curent users

April 23, 2013 - 12:14 pm UTC

Reviewer: Jaroslav Tachovsky

Hello,
I need a script to drop all dblinks in database. I can get the list by querying DBA_DB_LINKS, but to drop db link I must be connected as it's owner. Could we use above tricks somehow ?. PLSQL block cannot do the job because of alter session need.
Tom Kyte

Followup  

April 23, 2013 - 1:24 pm UTC

An appropriately privileged DBA like account can grant "connect through" and allow someone to "become" that other account.

ops$tkyte%ORA11GR2> alter user scott grant connect through ops$tkyte;

User altered.

ops$tkyte%ORA11GR2> connect ops$tkyte[scott]/foobar;
Connected.
scott%ORA11GR2> drop database link ora11gr2@scott;

Database link dropped.




see
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23asktom-1906478.html


Thank you :)

February 19, 2014 - 8:32 pm UTC

Reviewer: A reader

Thank you for these details. Helped me a lot. Please increase the font of your queries, it will be helpful.

Can we change the password of DB user

September 09, 2015 - 2:53 pm UTC

Reviewer: Bhuvan from India

Hi TOM,

i have a question. can we change the password of a DB user login with the new password do some work and then set it back to the old password even if we dont know his password? when we are sys or sysdba?

i am not able to open the link
http://asktom.oracle.com/~tkyte/Misc/su.html

Chris Saxon

Followup  

September 14, 2015 - 2:26 am UTC

Scroll back through this question and look for "proxy".

I prefer this method to connect as a user for whom you do not know the password, rather than altering the user