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.