Re: equivalent of su in oracle?

Maybe I did not understand you correctly. But this does not do what I wanted to do. I would like to become a particular user and then grant priviledges for tables belonging to that user to other users. But I tried this and it does not work:

connect system/password
alter session set current_schema = SCOTT grant select on scott.table_name to another_user

The error message is:
ERROR at line 1:
ORA-01031: insufficient privileges

So if my understanding of your info is correct, this is far from being the equivalent of su in oracle.

The reason I am asking this question in the first place is that I frequently

need to create views which require explicit grants of "select" to other schema's tables. Even though I have system account and dba role, I still sometimes have to find other people to tell me what the passwords are
for particular schemas. This is very annoying.

Thanks for any further info.

Richard

Erwin Dondorp wrote:

> Richard Chen wrote:
> > Does anyone know a way in oracle to let the system user become any user
> > without knowing the users' password?
>
> Any Oracle user that has the "BECOME USER" right can do this.
> "BECOME USER" is part of the role "IMP_FULL_DATABASE", which
> again is included in the "DBA" role.
>
> IMP_FULL_DATABASE has this right because you need this functionality
> when you restore a database from an export file, the file contains
> objects of many users, yet you don't need to type the passwords
> for all these users.
>
> The BECOME_USER right gives you the ability to execute the
> ALTER SESSION SET CURRENT_SCHEMA = <schemaname>
>
> Beware 1: your login(username) wil not change, only your schema.
> The data dictionary views (e.g. USER_TABLES) reflect the
> status for the user that is logged in.
>
> Beware 2: This command is undocumented and supposed to be used only
> by the IMP command.
>
> Erwin
> --
> Erwin Dondorp
> <http://www.dondorp.com/>
Received on Sun Jul 23 2000 - 00:00:00 CDT