CREATE SYNONYM

CREATE SYNONYM

Syntax

create_synonym ::=

Prerequisites

At least one of the following conditions must be met:

• The SYS user
• Users having the CREATE SYNONYM or CREATE ANY SYNONYM system privilege in order to create a synonym in their own schema can execute this statement.
• Additionally, it is necessary to be the SYS user or have the CREATE ANY SYNONYM system privilege in order to create a synonym in another user's schema, and it is necessary to be the SYS user or have the CREATE PUBLIC SYNONYM system privilege in order to create a public synonym

Description

This statement is a SQL statement that generates synonyms. Synonyms are alternative names for the following objects:

• Table
• View
• Sequence
• Stored procedure and function
• Another synonym

Synonyms can be referred to using the following types of SQL statements:

DML Statement	DDL statement
SELECT INSERT UPDATE DELETE MOVE LOCK TABLE MERGE ENQUEUE DEQUEUE	GRANT REVOKE

OR REPLACE

This clause specifies the re-creation of a synonym, if it already exists. Using this clause, you can alter the definition of an existing synonym, without deleting it first.

PUBLIC Synonyms vs. PRIVATE Synonyms

A PUBLIC synonym is accessible by all users, whereas a PRIVATE synonym can only be accessed by its owner.

To create a public synonym, specify PUBLIC in the statement. If this keyword is not provided, a PRIVATE synonym will be created by default.

user_name

If a user name is provided in front of the synonym name, that user will be the owner of the synonym.

When creating a PUBLIC synonym, do not specify the name of the owner.

When creating a PRIVATE synonym, it is possible to specify the name of the owner. If no user name is provided, the synonym will be created in the schema of the user connected via the current session.

synonym_name

If there is a table, view, sequence, stored procedure, stored function,or another synonym that has the same name as the synonym to be created, an error will be raised. Because synonyms occupy the same namespace as these object types, the name for the synonym must be unique within the schema in which it is created. Refer to "Rules for Object Names" for more information of specifying names.

FOR clause

This clause is used to specify the object for which the synonym will serve as an alias.

user_name

This is used to specify the owner of the object for which the synonym will function as an alias. If no user name is specified, Altibase will assume that the object belongs to the schema of the user connected via the current session.

object_name

This is used to specify the name of the object for which the synonym will serve as an alias.

If this object does not exist in the database, no error will be raised, and the synonym will be created successfully regardless. In other words, the schema object need not currently exist, and it is not necessary to have privileges for the object for which the synonym will function as an alias.

Privileges and Synonyms

To execute DML statements on synonyms, it is necessary to have DML execution privileges for the underlying object.

When DML execution privileges on a synonym are granted or revoked, the privileges are actually granted or revoked on the underlying objects.

Therefore, when the execution of a DML statement on a synonym results in an error, check the SYS_GRANT_SYSTEM_ or SYS_GRANT_OBJECT_ meta table to determine whether the user has been granted suitable privileges for the underlying object.

If the user has not been granted suitable privileges, grant the privileges to the user. When granting privileges to the user, they can be granted either by referencing the object itself, or by referencing the synonym. If the user already has appropriate privileges for the underlying object itself, it is necessary only to create the synonym. No additional privileges need to be granted for the synonym.

Privileges granted for an object by referencing a synonym for the object are not revoked even if the synonym is subsequently dropped. This is because the privileges are actually granted for the object underlying the synonym, not the synonym itself, even though the synonym was referenced when the privileges were granted.

Object Name Search Order

In order to determine which objects match the objects referenced in a SQL statement, any tables, views, sequences, stored procedures or stored functions having corresponding names are first searched for. If they don't exist, other synonym objects having the names are searched for.

PRIVATE synonyms are examined before PUBLIC synonyms.

For example, whether objects having the names referenced in a SQL statement exist in the database would be determined in the order shown below.

SELECT * FROM NAME;

1. Any tables or views having the name "NAME" are searched for.
2. If no tables or views having the name "NAME" exist, any PRIVATE synonym objects having the name are searched for in the schema of the user connected via the current session.
3. If no PRIVATE synonyms having the name exist, any PUBLIC synonym objects are searched for.

SELECT * FROM USER.NAME

1. Any tables or views having the name "NAME" are searched for in the "USER" schema.
2. If no tables or views having the name "NAME" exist, any PRIVATE synonym objects having the name are searched for in the "USER" schema.
3. If no PRIVATE synonyms having the name exist, no PUBLIC synonym objects are searched for. Instead, an error is returned.

Examples

<Query> >Create a synonym called my_dept for the table dept, which is owned by the user altibase, in the current user's schema and execute some DML statements using the synonym.

iSQL> CONNECT altibase/altibase;
Connect success.
iSQL> CREATE TABLE dept 
     (
     id integer,
     name char(10), 
     location varchar(40), 
     member integer
     );
Create success.
iSQL> GRANT INSERT ON dept TO mylee;
Grant success.
iSQL> GRANT SELECT ON dept TO mylee;
Grant success.
iSQL> CONNECT mylee/mylee;
Connect success.
iSQL> CREATE SYNONYM mylee.my_dept FOR altibase.dept;
Create success.
iSQL> INSERT INTO my_dept VALUES (1,'rndn1',NULL,4);
1 row inserted.
iSQL> SELECT * FROM my_dept;
MY_DEPT.ID  MY_DEPT.NAME  MY_DEPT.LOCATION 
-------------------------------------------------------
MY_DEPT.MEMBER 
-----------------
1           rndn1 
4           
1 row selected.