RE: create synonym for other acct without logging to the user

grace,

if you want a sample of how to change a password and then set it back, check The DBA Handbook by Kevin Loney -- he has a script called "become_another_user.sql" there...

BUT (strong warning here)... if you are running 8.x and using password history in profiles, then you cannot change the password back to what it was... which defeats the purpose.

And you are right, there is no reason to get this complicated. You can either (as someone else suggested) grant "create any synonym" to the owner of the object, they can then create a synonym for that object in anyone else's schema. Or you can do it yourself, using an account that has the DBA role.

In both cases, the syntax is "create synonym <user2.synonym_name> for <user1.object_name>"

Now, having said that, remember to ALSO grant object privs (select, update, insert etc as required) or they will have a synonym on an object they can't access.

Another way around the problem would be to create a role, grant the object privs to the role, and then create a public synonym for the object.

Rachel

>From: grace lim <mglim_at_softhome.net>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: create synonym for other acct without logging to the user
>Date: Thu, 10 Aug 2000 18:14:46 -0800
>
>can you provide me a sample to give me an idea..... i don't understand why
>i have to change password first to create a synonym that points to an
>object own by other account.
>
>At 09:31 AM 8/10/00 -0800, you wrote:
> >As a DBA, you can temporarily change a user's password. Of course, this
> >should be allowed by your corporate policy.
> >But assuming this is permitted, you can use a SQL script ( or create a
> >procedure to do the same ) to
> >1. change the user's password
> >2. create the synonym
> >3. restore the user's password
> >
> >
> >Tom Harleman
> >11080 Willowmere Dr.
> >Indianapolis, IN 46280
> >317-844-2884 Home
> >317-843-9122 Home Office
> >
> >
> >-----Original Message-----
> >Sent: Thursday, August 10, 2000 4:14 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >gurus,
> >
> >is there a way i could create a synonym of an object for other accounts
> >without actually logging to each account?
> >
> >thanks
> >--
> >Author: grace lim
> > INET: mglim_at_softhome.net
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
> >--
> >Author: Thomas L. Harleman
> > INET: tharleman_at_iquest.net
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >
> >
>--
>Author: grace lim
> INET: mglim_at_softhome.net
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).