CREATE SYNONYM

Purpose

You can use this statement to create a synonym.

Syntax

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ synonym_schema. ]synonym_name
FOR [ object_schema. ]object_name;

Parameters

Examples

• Create a synonym for a table.

  obclient> CREATE TABLE tbl1(col1 INT,col2 INT);
  Query OK, 0 rows affected
  
  obclient> CREATE SYNONYM syn1 FOR tbl1;
  Query OK, 0 rows affected
  
  obclient> INSERT INTO syn1 values(1,1);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM syn1;
  +------+------+
   COL1  COL2 
  +------+------+
      1     1 
  +------+------+
  1 row in set
  

• Create a public synonym.

  obclient> CREATE PUBLIC SYNONYM syn2 FOR tbl1;
  Query OK, 0 rows affected
  

Usage notes

The following describes the privileges required for creating different types of synonyms:

• To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege.

• To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM privilege.

• To create a public synonym, you must have the CREATE PUBLIC SYNONYM privilege.

• The object for which the synonym is created need not currently exist and you need not have privileges to access the object.

/*Create user synonym_user.*/
obclient> CREATE USER synonym_user IDENTIFIED BY synonym_user;
Query OK, 0 rows affected

obclient> GRANT CREATE ON *.* TO synonym_user;
Query OK, 0 rows affected

obclient> GRANT SELECT ON *.* TO synonym_user;
Query OK, 0 rows affected

/*Connect to user synonym_user.*/
obclient> CREATE OR REPLACE SYNONYM syn1 FOR tbl1;
ERROR-00600: internal error code, arguments: -5036, Access denied; you need (at least one of) the CREATE SYNONYM privilege(s) for this operation

/*Grant the CREATE SYNONYM privilege to user synonym_user.*/
obclient> GRANT CREATE SYNONYM ON *.* TO synonym_user;
Query OK, 0 rows affected

obclient> CREATE OR REPLACE SYNONYM syn1 FOR tbl1;
Query OK, 0 rows affected