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

From: Thomas L. Harleman <tharleman_at_iquest.net>
Date: Fri, 11 Aug 2000 09:10:24 -0500
Message-Id: <10586.114356@fatcity.com>

Grace,

I've included an example. But as others have pointed out, the best way is to reference the synonym owner and table owner in Rachel's example: create synonym <user2.synonym_name> for <user1.object_name>;

The example is at the end of this message.

Tom Harleman
Now working for ThinkFast (www.thinkfast.com) the premiere business intelligence consulting company.
11080 Willowmere Dr.
Indianapolis, IN 46280
317-844-2884 Home
317-843-9122 Home Office

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of grace lim Sent: Thursday, August 10, 2000 9:15 PM
To: Multiple recipients of list ORACLE-L Subject: RE: create synonym for other acct without logging to the user

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.

• CUT HERE ================================= COL USERNAME NOPRINT NEW_VALUE UID COL PASSWORD NOPRINT NEW_VALUE PWD SET ECHO ON

REM
REM
REM CREATE A TABLE THAT WILL BE USED BY SCOTT REM
REM CREATE USER TEMP IDENTIFIED BY TEMP
/

GRANT CONNECT, RESOURCE TO TEMP
/

CONNECT TEMP/TEMP CREATE TABLE TEMP ( TEMP CHAR(1) )
/

GRANT SELECT ON TEMP TO SCOTT
/

REM
REM
REM TEST WHETHER SCOTT CAN SEE THE TABLE REM
REM CONNECT SCOTT/TIGER DESC TEMP.TEMP DESC TEMP REM
REM
REM TEMPORARILY CHANGE SCOTT'S PASSWORD
REM
REM CONNECT SYSTEM/MANAGER SELECT USERNAME, PASSWORD
FROM DBA_USERS
WHERE USERNAME = 'SCOTT'
/

ALTER USER &&UID IDENTIFIED BY &&PWD
/

REM
REM
REM CONNECT AS SCOTT AND CREATE THE SYNONYM REM
REM CONNECT &&UID/&&PWD CREATE SYNONYM TEMP FOR TEMP.TEMP
/

REM
REM
REM CHANGE SCOTT'S PASSWORD BACK TO THE ORIGINAL PASSWORD REM
REM CONNECT SYSTEM/MANAGER ALTER USER &&UID IDENTIFIED BY VALUES '&&PWD'
/

REM
REM
REM TEST WHETHER SCOTT CAN USE THE NEW SYNONYM REM Received on Fri Aug 11 2000 - 09:10:24 CDT