Choose alternatives for public and private synonyms

In many large applications, a common way of separating the end user from the schema owner is to use private synonyms for application schema objects.

For example, user A and user B both have their own login accounts. Schema user X has two tables, T1 and T2, so the application designers set up private synonyms for both A and B that reference X.T1 and X.T2, like this:

connect X/X

create table T1(…);

create table T2(…);

connect A/A

create synonym T1 for X.T1;

create synonym T2 for X.T2;

connect B/B

create synonym T1 for X.T1;

create synonym T2 for X.T2;

With two schema objects and two users, you have only four private synonyms. When you have a large number of users and application objects, you'll probably see a slow degradation of database performance. The performance problem will mostly be during the parse phase.

Every time a user wants to query T1, the parser must query across a large set of synonyms and put each synonym in the library cache along with its dependency structure. This would even affect queries such as select * from dual. You should avoid synonyms and use alternatives that depend on your application design.

The best alternative is to always fully qualify a table with its schema name. This alternative is only effective if you can guarantee the schema name will not change between installations and that there's only one schema with the given name in the database. In most applications where the designers carefully hide the SQL from the end user, there is no real benefit to coding select * from T1 in the code when select * from X.T1 would work in your situation.

In cases where there may be multiple schemas but each end user only accesses one schema at a time, the ALTER SESSION SET CURRENT_SCHEMA command is better than a synonym. This command makes the default schema for unqualified tables go to a particular schema. For example:

connect A/A
alter session set current_schema = X;
select * from T1;

This final query will select values from X.T1 and doesn't require any synonyms, while still obeying granted privileges on database objects. Even when it isn't possible to update an application so it always issues the alter session after a connect, you can code a database LOGON trigger to automatically set the current schema for a user.

create or replace trigger logon_trg
    on database after logon

begin
    if user in ('A','B') then
        execute immediate 'alter session set current_schema=X';
    end if;

end;

When user A or B logs in, his default schema will set automatically to X. The query select * from T1 will choose X.T1 automatically without requiring a synonym lookup or lock in the library.

Even in cases where the end user is accessing multiple schemas, or the names of database objects change between the end user and the application, you can still use views, which users can share in a central account:

create view T1 as select * from X.T1;

All of these alternatives rely on privileges the user gets to access a specific table. However, this may be more access than an application needs to give. A user who gets SELECT on a table in another schema can view all the columns and rows in that table or view any utility that can execute generic SQL.

An alternative that restricts exactly what data and operations the user can perform against the data is to define PL/SQL functions, procedure, and packages to wrap up specific access to an object. The PL/SQL code runs with the owner's privilege, and unqualified names resolve according to the code owner's schema. If the code owner is also the schema object owner, this eliminates the need for synonyms or even hard-coded schema names.

create or replace function t1_get_count return integer

as
    l_count integer;

begin
    select count(*) into l_count from t1;
    return l_count;

exception
    when others then return 0;

end;

/

If you use "invoker rights" with AUTHID CURRENT_USER, then unqualified tables will resolve in the current user's schema, and you'll have to use another way of resolving those names at runtime.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.