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:

SELECT * FROM USER3.SHIPMENT; 

Assuming SHIPMENT always means USER3.SHIPMENT for USER1, typing fully qualified name makes queries longer and less readable.

Note 

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

The syntax for the CREATE SYNONYM statement is

CREATE [PUBLIC] SYNONYM   [<schema>.]<synonym_name> FOR   [<schema>.]<object_name>[@<dblink>];

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 CREATE SYNONYM or the CREATE ALIAS 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).