Wednesday, December 26, 2007

Oracle Synonyms are unnecessary - Part 1

I've been doing an inordinate amount of responding on the Oracle Forums over the past few weeks. One recent thread related to visibility of tables to a new user. Most of the respondents suggested using synonyms - something that will reduce the flexibility of the application in the long run.

Basically, synonyms are aliases or alternate names, of an object. The use is well documented but ... I find that synonyms can lead to
  1. security risks (inevitably by granting everything to public) and
  2. future-resistant administration (namespace collisions requiring separate instances)

The biggest use of synonyms seems to be to "make the schema qualifier go away in the SQL statement". For those cases, use the session-based current_schema capability. Here is a quick demo:

1) Create a new user r2 with next-to-no privileges, other than to connect.

C:\temp>sqlplus system/oracle
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 26 14:31:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user r2 identified by r2;
User created.
SQL> grant create session to r2;
Grant succeeded.
2) Create a table in schema TEST and make it available to the new user


SQL> connect test/test
Connected.
SQL> create table x ( x number );
Table created.
SQL> create table x ( x number );
Table created.
SQL> grant select on x to r2;
Grant succeeded.
SQL> insert into x values (3);
1 row created.


3) Log on as the new user and select from the table

SQL> connect r2/r2
Connected.
SQL>
alter session set current_schema=test;
Session altered.
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------

X NUMBER
SQL> select * from x;

X
----------
3

SQL> insert into x values (4);
insert into x values (4)
*
ERROR at line 1:
ORA-01031: insufficient privileges


Using current_schema basically tells the system to use 'test' as the current schema, eliminating the need to create synonyms.

It is a basic session command, so it is available for all applications.

3 comments:

Anonymous said...

This won't work in the case where you are selecting from objects in your own and another schema in same statement.

Forbrich said...

My wish list includes a "schema_order_list" session setting to address the limitation you mention.

My point is that there are a number of situations where this will work, and people are not looking at those possibilities at all.

Frank said...

Agree on public synonyms, but not on private synonyms.
But then again - private synonyms are there for the lazy or sloppy programmer, not using schema.object notations.