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 ??
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,
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
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.
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
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?....
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
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
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?
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.
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>
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?
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.