Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ashiq.

Asked: January 22, 2003 - 12:42 pm UTC

Last updated: September 16, 2009 - 4:38 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hello Tom,

I got a problem regarding the public synonym.I've 4 users like,
RMSREAL, RMSBASE, BILL & YUN .In this , RMSREAL and RMSBASE are ditto(like they're 2 oranges) .Only difference is RMSREAL has more records where as RMSBASE have less records,but other than that everything is same like number of table , packages etc... .

Where as BILL and YUN doesn't own any objects but have access to all objects of RMSREAL as well RMSBASE .So i've created public synonym for all the objects(like procedure and packages) for RMSREAL objects.

But some part of time i want BILL to access the objects the RMSBASE and YUN will continue to access the objects of RMSREAL .If i drop the public synonym created for objects of RMSREAL and create for RMSBASE,then BILL will start accessing the RMSBASE objects.But here my problem is still i want YUN to access the objects of RMSREAL.I've drop the public synonym everything will go for toss.
Then we thought of granting the objects directly to users.Like if BILL is connected then prefix RMSREAL in all the objects and if YUN is connected ,prefix with RMSBASE.But its need a lot of code changing in front End (front end is VB , ASP )and i strongly believe its not good idea.

And we're running our application in Oracle8i as SQL server2k
Front end guys saying we're able to do this in SQL server without any any code changes ,why not in Oracle. I strongly believe there is way to over come this.
From Front End they'll access procedures , packages and in thru XML they'll directly refer the tables.
Plz explaing me in detail how to go abt with detail explanation.

thanx

and Tom said...

drop the synonyms -- get rid of them. they are a bad idea anyway.

Either via an on-logon trigger in the database OR (better yet) in the application -- issue:

alter session set current_schema = RMSBASE

for Bill and

alter session set current_schema = RMSREAL

for Yun. You could create a table:

create table default_schemas
( username varchar2(30) primary key,
schema varchar2(30)
)
/


and query:

select nvl( max(schema), user ) from default_schemas where username = user;


upon logon and set the current_schema to whatever that returns... That'll achieve your goal.


Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why Synonms are Bad

Ashiq Shamsudeen, January 22, 2003 - 1:45 pm UTC

Thanx for explaing .But why synonyms are very bad idea?? So u mean to say to grant direct access to tables itself rather than synonyms.Then want for synonmns are useful if its bad idea ??

Tom Kyte
January 22, 2003 - 1:51 pm UTC

Your question above points out the failing of synonyms. There can only be one of them. Your namespace gets heavily polluted.

You still have to do the same amount of granting. synonyms do not bypass security -- they just change the name of something.

Public synonym is bad

Robert Xuequn Xu, January 22, 2003 - 4:39 pm UTC

I'd say that using *public* synonym is very bad. They should be reserved for data dictionary use only. If I have to use synonyms I would use private ones.

make it generic

Charlie, January 23, 2003 - 11:47 am UTC

Hi Tom,

If I want everybody who logged in the database use schema 'SCOTT', how can I do that?
"after logon on database" trigger seems not working. My DB version is 8.1.7.0.0. Any thoughts?

Thanks,

Tom Kyte
January 23, 2003 - 11:58 am UTC

"seems not working" -- seems not descriptive.  I just:

create trigger make_me_query_scott
after logon on database
begin
    execute immediate 'alter session set current_schema=scott';
end;
/


and 

ops$tkyte@ORA817DEV> connect /
Connected.
ops$tkyte@ORA817DEV> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA817DEV> select * from user_objects where object_name = 'EMP';

no rows selected

ops$tkyte@ORA817DEV> select * from dba_synonyms where synonym_name = 'EMP';

no rows selected

ops$tkyte@ORA817DEV> set linesize 50
ops$tkyte@ORA817DEV> desc emp
 Name                    Null?    Type
 ----------------------- -------- ----------------
 EMPNO                   NOT NULL NUMBER(4)
 ENAME                            VARCHAR2(10)
 MGR                              NUMBER(4)
 HIREDATE                         DATE
 SAL                              NUMBER(7,2)
 COMM                             NUMBER(7,2)
 DEPTNO                           NUMBER(2)
 JOB                              VARCHAR2(10)

ops$tkyte@ORA817DEV> alter session set current_schema=ops$tkyte;

Session altered.

ops$tkyte@ORA817DEV> desc emp;
ERROR:
ORA-04043: object emp does not exist



works for me. 

Which schema gets the trigger?

David Gibbs, January 23, 2003 - 1:20 pm UTC

Tom

You showed the creation of the trigger. Which schema should own it? System, (in this case) Scott or someone else. Would this trigger affect all users logging into the database?

Thanks

Tom Kyte
January 23, 2003 - 1:25 pm UTC

I owned it (ops$tkyte)

As written -- it would affect all users - you would use an if statement to restrict it to whomever you wanted...

 

dba

Charlie, January 23, 2003 - 2:19 pm UTC

create trigger make_me_query_scott
after logon on database
begin
execute immediate 'alter session set current_schema=scott';
end;
/


and

ops$tkyte@ORA817DEV> connect /
Connected.
ops$tkyte@ORA817DEV> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA817DEV> select * from user_objects where object_name = 'EMP';

no rows selected

ops$tkyte@ORA817DEV> select * from dba_synonyms where synonym_name = 'EMP';

no rows selected

ops$tkyte@ORA817DEV> set linesize 50
ops$tkyte@ORA817DEV> desc emp
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
JOB VARCHAR2(10)

ops$tkyte@ORA817DEV> alter session set current_schema=ops$tkyte;

Session altered.

ops$tkyte@ORA817DEV> desc emp;
ERROR:
ORA-04043: object emp does not exist



works for me.

>>>>>>
I think I found the problem --> "ops$tkyte" is a DBA. Let's try this:
- login as a dba to create trigger "make_me_query_scott"
- logout
- login as a normal user who is not a dba or does not have "select any table" system privilege
- "desc emp" will barf

Also, I found that if user b is a dba and he created a logon trigger which uses "alter session set current_schema=b" <-- his own schema. Then This will fail all users. Nobody can describe his table.

Tom Kyte
January 23, 2003 - 7:15 pm UTC

Umm -- did David "answer your question" below? Cause I'm not really sure what if any question you have here?

Tianhua Wu, January 23, 2003 - 4:44 pm UTC

I would say synonym has its own advantages. It is not bad all te time. For example, if you have a package that use certain objects many many times, and you want to change that to different objects (tables at different database, eg.), and you know you will change them from time to time, I would use synonyms. (Of course there are other ways doing it)

Follow up to Charlie's observation

David Gibbs, January 23, 2003 - 5:45 pm UTC

Charlie

You stated:

"I think I found the problem --> "ops$tkyte" is a DBA. Let's try this:
- login as a dba to create trigger "make_me_query_scott"
- logout
- login as a normal user who is not a dba or does not have "select any table"
system privilege
- "desc emp" will barf

Also, I found that if user b is a dba and he created a logon trigger which uses
"alter session set current_schema=b" <-- his own schema. Then This will fail
all users. Nobody can describe his table."

Your problem is because you set current_schema but did not grant any rights to other users. As it states in the SQL Reference guide:

"CURRENT_SCHEMA = schema
The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement. CURRENT_SCHEMA is a session parameter only, not an initialization parameter.

This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session."

If you granted select on emp to all users your query would not "barf". The reason OPS$TKYTE couldn't see the table is because he didn't qualify EMP as SCOTT.EMP.


How to set dynamically

Ashiq Shamsudeen, January 24, 2003 - 5:13 am UTC

Hello Tom,
From your answer u asked to create trigger ,but for me still i've some problem .Sometimes Bill will connect to RMSREAL
and sometimes RMSBASE.If u hard coding ('alter session set current_schema=scott') the current_schema value to RMSREAL ,then how can i change it for current_value to RMSBASE?

thanx

Tom Kyte
January 24, 2003 - 7:31 am UTC

your keyboard is seriously broken. It is dropping really important letters from time to time making it really hard to read. but anyway...

well, given that you have no way of reading BILL's mind when he logs in (thats scheduled for version 20 of Oracle I think ;) you'll need to give Bill a push button or something in your application that lets Bill pick the schema Bill wants to use -- or something like that.

You know what to do know -- use alter session to switch the current_schema. You can use it anywhere in your application you want to.

Information Hiding

Sankar, January 24, 2003 - 9:45 am UTC

We are developing a Application Using Public Synonyms. We have created all objects in a User A, And We have created Public Synonyms In the user B. And Granted to B to all users.From our point of view there are 2 advantages.

1) No need to prefix the Username everywhere while programming.

2) As we create synonym we hide the owner, So we thought it is a additional security, Is it so?....




Tom Kyte
January 24, 2003 - 11:07 am UTC

You do not create public synonyms in B, you created public synonyms -- IN PUBLIC.

There is no such thing as a public synonym owned by "b".

1) alter session set current_schema accomplishes the same without polluting the namespace

2) security through obscurity? hardly. You either HAVE ACCESS (via grant) or not. Besides, all_synonyms will show, well, it'll show you the owner.

If you believe this is a "security" measure -- well, hope you don't work on a publicly accessible internet site and .....


Here, read this too:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7483411948917 <code>

shows the problems someone who used synonyms is faced with today.

Followup on ALTER SESSION SET CURRENT_SCHEMA command

Dan Kefford, January 24, 2003 - 11:45 am UTC

Dear Tom...

Indeed, we also use public synonyms to avoid prefixing object references with their respective schema owners.

How would you handle a situation in which one schema needed to reference objects belonging to more than one other schemas? It appears that the ALTER SESSION command can only specify a single schema. Is this wrong? If not, would you then consider this a design issue in that perhaps the objects in question ought to be owned by the same schema?

Many thanks in advance,

dan

Tom Kyte
January 24, 2003 - 12:54 pm UTC

perhaps an integrating schema that had views to the necessary objects...

You see -- I do 99.99999% of everything in PLSQL where this becomes basically moot. you execute a schema.package.procedure and there you go. it does the rest.

Good discussion

Mike, January 24, 2003 - 1:50 pm UTC

ALTER SESSION SET CURRENT_SCHEMA vs. public synonyms. Eye-opening debate/discussion. I've always used public synonyms but I'm seeing Tom's point about using SET CURRENT_SCHEMA instead. Interesting...

good explanation

G.Natarajan, February 05, 2003 - 9:47 am UTC

Tom It was a very good explanation. But I still didnt get the solution for a question asked by some one. My problem is If my application( or a single program) need to access the objects of two different schema's how can i use it in my application, th way you mentioned by adding trigger called 'after logon '. Now, we are using public synonyms so i need not to bother which schema i have to refer for a particular object, and it will be a costly option to change the code to replace the synonyms with "schema.object_name" format.. thanx

Tom Kyte
February 05, 2003 - 12:01 pm UTC

ack. I give up.


Here is a story for you. App installed itself. Created a public synonym IND via drop/create. Later, an upgrade occurs to the database. I'll let you guess what happens.


Any. You have an application that uses public synonyms. You need add ONE LINE of code:

alter session set current_schema=FOO

and then you can drop all public synonyms that point to foo.....


The answer to the question of "if my app needs to access the objects of two different schemas" can be answered via views AND/OR private synonyms.


but do what you will -- at your own risk.

A reader, February 06, 2003 - 4:01 am UTC

If proper grants are given, then synonyms can be used for querying from the database without altering data and by hiding the table from the user using the synonym, can't they be?


Tom Kyte
February 06, 2003 - 8:29 am UTC

of course they can -- but "hiding the table" is strong, hiding the schema would be more appropriate.

but the problem with PUBLIC synonyms is namespace pollution. How many public synonyms EMP can there be in a database?

what happens when new application "foo" comes along and wants EMP as its own synonym?

To avoid Creating SYNONYMS

Riyaz H., May 07, 2003 - 11:59 am UTC

Hi Tom, I have a question for you.

MY INTENTION: Avoid Creating SYNONYMS (because it leads to lot of confusion & difficult to maintain as you also stressed in many places)

MY OBJECTIVE IN NUTSHELL:
------------------------
1. I need to write a SINGLE PROCEDURE
2. Inside that, tables of both the users ( a & b) will be accessed. (sepeartely)
3. Grant permission will be given
4. I need to switch the user using 'alter sesion' command
5. I don't want to use synonyms.

I want to write a procedure similar to below: (a & b are schemas). This is basically I am doing TO AVOID CREATING SYNONYMS

create or replace procedure p_1 authid current_user
as
n1,n2 number;
begin
alter session set current_schema=a;
select count(1) into n1 from t1;
alter session set current_schema=b;
select count(1) into n1 from t2;
end;

But the above procedure is giving error. (inspite of giving 'grant all' permission from t2 to t1)

WHAT IS THE WAY OUT?

Thanks in advance.

Tom Kyte
May 07, 2003 - 1:46 pm UTC

please -- ask once, you already asked this elsewhere and I addressed it there, you'll have to go find it again.

Is this related to synonyms

Kulguru, May 14, 2003 - 2:51 pm UTC

Tom I can describe this object , but cannot select from it .. why is this so...please give your input


SQL> connect test/test@itest
Connected.
SQL> desc test.t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 column1                                                NOT NULL VARCHAR2(16)
 column2                                                NOT NULL NUMBER(16)
 column3                                                NOT NULL NUMBER(6)
 CREATE_USER                                           NOT NULL VARCHAR2(8)
 CREATE_DATE                                           NOT NULL DATE
 MODIFY_USER                                           NOT NULL VARCHAR2(8)
 MODIFY_DATE                                           NOT NULL DATE

SQL> select 'X' from test.t;
select 'X' from test.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist



SQL> set role none
  2  /

Role set.



SQL> desc test.t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 column1                                                NOT NULL VARCHAR2(16)
 column2                                                NOT NULL NUMBER(16)
 column3                                                NOT NULL NUMBER(6)
 CREATE_USER                                           NOT NULL VARCHAR2(8)
 CREATE_DATE                                           NOT NULL DATE
 MODIFY_USER                                           NOT NULL VARCHAR2(8)
 MODIFY_DATE                                           NOT NULL DATE

SQL> select 'X' from test.t;
select 'X' from test.t
                        *
ERROR at line 1:
ORA-00942: table or view does not exist 

What am I missing here?

Sami, April 15, 2004 - 9:05 am UTC

Dear Tom,

Could you please shed some light on this? Thanks in advance.

SQL> connect reports
Enter password: 
Connected.
SQL> desc reports_password;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             VARCHAR2(36)
 CREATEDATE                                         VARCHAR2(30)
 UPDATEDATE                                         VARCHAR2(30)
 HSBC_USER_CATEGORY                                 VARCHAR2(40)
 CID                                                NUMBER(8)
 SEQNO                                              NUMBER

SQL> desc REPORTS.REPORTS_PASSWORD_20APR2004
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             VARCHAR2(36)
 CREATEDATE                                         VARCHAR2(30)
 UPDATEDATE                                         VARCHAR2(30)
 HSBC_USER_CATEGORY                                 VARCHAR2(40)
 CID                                                NUMBER(8)
 SEQNO                                              NUMBER
 
SQL> 

SQL> connect atg56
Enter password: 
Connected.

SQL> show user
USER is "ATG56"

SQL> desc reports_password;
ERROR:
ORA-04043: object REPORTS.REPORTS_PASSWORD_20APR2004 does not exist


 
SQL> connect internal
Connected.

SQL> set lines 200
SQL> select * from dba_tab_privs where grantee='ATG56' and table_name='REPORTS_PASSWORD';
 
GRANTEE     OWNER       TABLE_NAME         GRANTOR     PRIVILEGE    GRA
----------- ----------- ------------------ ----------- ------------ ---
ATG56       REPORTS     REPORTS_PASSWORD   REPORTS     SELECT       YES
 
SQL> 
 

Tom Kyte
April 15, 2004 - 9:33 am UTC

REPORTS.REPORTS_PASSWORD_20APR2004 <> table_name='REPORTS_PASSWORD';




excellent article

ramki, June 26, 2004 - 11:45 pm UTC

Tom this is an excellent article
thanks for the info

RE: Why synonyms are bad?

Duke Ganote, October 06, 2006 - 10:49 am UTC

SESSION 1:

DGANOTE> create table testy ( x number );

Table created.

DGANOTE> grant select on testy to msb;

Grant succeeded.

DGANOTE> create public synonym testy for dganote.testy;

Synonym created.

SESSION 2:

MSB> desc msb.testy;
Name Type Null?
--------- ------ ------
X NUMBER Y

MSB> select count(*) from msb.testy;
select count(*) from msb.testy
*
ERROR at line 1:
ORA-00942: table or view does not exist

MSB> create table testy ( y number );

Table created.

MSB> select count(*) from msb.testy;

COUNT(*)
--------------------
0

Very disconcerting!

A reader, December 20, 2006 - 3:22 am UTC


public synonyms

sam, September 16, 2009 - 10:10 am UTC

Can anybody explain why we should convert public to private synonyms?
we are doing database consolidation . what is the workaround if multiple schemas have same object names?
Tom Kyte
September 16, 2009 - 4:38 pm UTC

Sam -

it seems you already know.....


Q: How many public synonyms named X may you have?
A: One

Q: If you try to consolidate two applications into a single database that both MUST HAVE a synonym X, what can you do?
A: nothing, you cannot consolidate

anything that pollutes the "public" namespace must be used sparingly and carefully, with well thought out naming conventions - application contexts, public database links, public synonyms, directories - and so on.

... what is the workaround if multiple
schemas have same object names? ...

make it so that they do not (if by object names you mean "something in the public namespace")

if you just mean "they both have table T", there is no problem with that at all.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library