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?

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?

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.

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?

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

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.

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

Hi Stewart

Here is the link

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

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.

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$ 

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.

Thanks Tom - I wasn't clear.

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

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>

Tom,

Thanks for responding and providing the newer method.

Regards,
Barbara

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

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

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.

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

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,
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.

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.

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...

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.

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

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

User altered.

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

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>] | /

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

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

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>

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

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

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

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,

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 ?

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) ?

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?

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> 

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?

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.

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.

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

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