Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: equivalent of su in oracle?

Re: equivalent of su in oracle?

From: Howard J. Rogers <howardjr_at_www.com>
Date: 2000/07/25
Message-ID: <397cf845@news.iprimus.com.au>#1/1

You can achieve what you want to do by surreptitiously log on as the user concerned.

First, select username, password from dba_users. Copy out the hexadecimal version of the password for the relevant user into a text file somewhere.
Now, issue the command 'alter user mildred identified by newpassword' -you'll now be able to connect as Mildred, because you've just reset her password.

Grant all the privileges you like on whatever objects she owns (you have become her, after all!)

Finally, 'alter user mildred identified by values 'old password hexadecimal string';

When Mildred next tries to log on, she will not notice a thing -the password will be as it always was for her.

I have to say, however, that logging on as other people, and granting permissions to others on objects in their schemas, is usually considered a bit dodgy -and impolite to boot. There are good reasons why object owners are the only people authorised to grant permissions on their objects.

Incidentally, you can avoid all this hassle by simply contacting Mildred (or whoever) in the first place, and asking her nicely whether she will grant permissions on her objects to you, the DBA, "with grant option". That makes you an authorised person for the subsequent granting of those permissions to anyone else you fancy. And it's all above board and legititmate.

Regards
HJR "Richard Chen" <qchen_at_snet.net> wrote in message news:397AF5DB.7DDEC6BF_at_snet.net...
> 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 Tue Jul 25 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US