Aliases and Synonyms

Aliases and Synonyms

Different databases are organized in quite
different ways. Even the word database itself has
completely different meanings in different RDBMS implementations. For example,
an Oracle database is a totally self-contained and independent entity with its
own set of users, tables, indexes, and other objects invisible to other
databases. Each Oracle database user can have his/her own tables, views,
indexes, and so on. (In Oracle terms
USER and
SCHEMA are often used as synonyms which
adds confusion.) To access objects that belong to another user (or are within
another schema), you have to be granted appropriate permissions (see
Chapter
12) and you also have to use fully qualified names
(schema_name.object_name). For example, if
USER1 wants to select records from
SHIPMENT table that belongs to
USER3, the query would look like this:

The synonyms are especially important when users who don't own
objects need to access the database using an application with embedded SQL
(discussed in
Chapter
15). The programming effort to make such applications work properly
without synonyms would increase tremendously.

Figure 4-6 illustrates Oracle's database
organization.

Figure 4-6: Database organization
in Oracle

The Oracle RDBMS lets you to create
synonyms to deal with the problem. A synonym is a name
that translates into another name whenever it is referenced. In other words, we
can say that a synonym is an alternative name for a database object. You can
create the synonym
SHIPMENT for
USER3.SHIPMENT and use it any time you need
to access the
USER3.SHIPMENT table.

DB2 UDB organization is quite similar; a
database object that is simply a different name for another database object is
called
ALIAS.

The structure of MS SQL Server is
different. There can be many databases within a single SQL Server. Users (or
logins) are created on the server level and can have
access to many databases while the database objects belong to a single owner
(usually called dbo). See
Figure
4-7.

Figure 4-7: Database organization
in MS SQL Server

SQL99

Synonyms and aliases are not a part of
SQL99 standards.

Oracle 9i
CREATE SYNONYM statement

Public versus private
synonyms

In Oracle, you can create public
synonyms accessible to all database users or private ones visible only to their
owners. Use keyword
PUBLIC if you want to create a public
synonym or skip it otherwise. (Keyword
PRIVATE is invalid.)

Types of objects you can create
synonyms for

You can create synonyms for the
following Oracle objects: table, view, sequence, stored procedure, function,
package, materialized view, and Java class schema object. You can also create a
synonym for another synonym.

Creating synonyms for remote
database objects

You can create synonyms for objects
located in remote databases assuming a database link
exists for those databases. More about database links later in this
chapter.

CREATE SYNONYM examples

The following example creates the
public synonym
SHIPMENT for a hypothetical table
USER3.SHIPMENT:

CREATE PUBLIC SYNONYM
shipment FOR user3.shipment;

The next statement illustrates the
creation of private synonym
EMP for a
USERn.EMPLOYEE table in
USER2 schema:

CREATE SYNONYM user2.emp FOR
usern.employee;

Note that you could skip
USER2 if the above statement was issued
by
USER2 him/herself; it is mandatory
though if the synonym
EMP is being created by, say, the
database administrator for
USER2.

Note

Even though synonyms can be very useful, they also can cause
lots of confusion. The most typical situation is when a user has objects in
his/her schema with exactly the same names as public or private synonyms.
Oracle tries to resolve names looking in the users' schema first, and if the
name is found, RDBMS assumes that's the one to use. So, if there are views
USER1.VIEW1 and
USER2.VIEW1 in the database, and
there is also public synonym
VIEW1 for
USER2.VIEW1 that is supposed to be
used in all user queries,
USER1 might have serious program
errors (if the columns of his/her
VIEW1 are different from
USER2.VIEW1). Or, which is sometimes
even worse because it's more difficult to notice, incorrect results (if the
column definitions are identical, but the views use different underlying tables
or join them in a different way).

Tip

In Oracle you can create synonyms for nonexistent objects; if
the objects are created later they can be referred using those synonyms. You
can also create synonyms for objects you don't have privileges to access, but
doing so will not give you an access to those
objects.

DB2 UDB 8.1 CREATE ALIAS/SYNONYM
statement

You can use either the
CREATESYNONYM or the
CREATEALIAS statement. Aliases can be created
for tables, views, or other aliases. The syntax is

CREATE {ALIAS | SYNONYM}
<alias_name> FOR <object_name>

In DB2, you cannot create an alias with
a name identical to one of a table, view, or another alias that already exists
in the current database. That resolves the problem described in the
previous
section about Oracle's synonyms, but makes using aliases in DB2 less
flexible.

Here are examples that create aliases in
DB2:

CREATE ALIAS shipmt FOR
user3.shipment

CREATE ALIAS emp FOR
usern.employee

Note that the following statement
returns an error (assuming you are using objects shown in
Figure
4-6) because the table named
SHIPMENT exists in schema
USER3:

CREATE PUBLIC SYNONYM SHIPMENT
FOR USER3.SHIPMENT;

Like Oracle, DB2 allows you to create
synonyms for objects that do not yet exist, though a warning will be
issued.

MS SQL Server 2000

MS SQL Server does not let you create
aliases or synonyms. This limitation is justified by its database structure
(Figure
4-7).