All Products
Search
Document Center

CREATE SYNONYM

Last Updated: Jun 18, 2021

Description

You can execute the CREATE SYNONYM statement to create a synonym.

Syntax

CREATE [ OR REPLACE ] [ PUBLIC ]
SYNONYM [ DATABASE. ]synonym
FOR [ DATABASE. ]object;

Parameters

Parameter

Description

OR REPLACE

Create the synonym again if it already exists. You can use this clause to change the definition of an existing synonym.

PUBLIC

Add PUBLIC to the statement to create a public synonym. Public synonyms are accessible to all users. To use a public synonym, ensure you have appropriate permissions on the underlying object.

The system uses a public synonym only if you do not specify the DataBase parameter for the object.

If you do not use the PUBLIC keyword, the synonym is private and is accessible only in the current database. The name of a private synonym must be unique in the current database.

[ DataBase. ]synonym

DataBase specifies the database to which the current synonym belongs. If you add PUBLIC to the statement, you do not need to specify the database for the synonym. The synonym parameter specifies the name of the synonym.

[ DataBase. ]object

The name of the object for which the synonym is created.

Examples

  • Create a synonym.
OceanBase(admin@test)>create table t1(c1 int);
Query OK, 0 rows affected (0.18 sec)

OceanBase(admin@test)>create synonym s1 for t1;
Query OK, 0 rows affected (0.05 sec)

OceanBase(admin@test)>insert into s1 values(1);
Query OK, 1 row affected (0.02 sec)

OceanBase(admin@test)>select * from s1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
  • Create a public synonym.
OceanBase(admin@test)>create public synonym syn_pub for t1;
Query OK, 0 rows affected (0.03 sec)

OceanBase(admin@test)>select * from syn_pub;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

Notes

To create a synonym, ensure that you have the following permissions:

  • To create a private synonym for the current database, ensure that you have the CREATE SYNONYM permission.
  • To create a private synonym for another database, ensure that you have the CREATE ANY SYNONYM permission.
  • To create a public synonym, ensure that you have the CREATE PUBLIC SYNONYM permission.
  • The object for which you want to create a synonym does not need to exist. Therefore, you do not need to have the permission to access the object.
Connect to the database as the syn_user user.
OceanBase(ADMIN@TEST)>CREATE USER syn_user IDENTIFIED BY syn_user;
Query OK, 0 rows affected (0.06 sec)

OceanBase(ADMIN@TEST)>grant CREATE on syn_user.* to syn_user;
Query OK, 0 rows affected (0.02 sec)

OceanBase(ADMIN@TEST)>grant SELECT on syn_user.* to syn_user;
Query OK, 0 rows affected (0.03 sec)


Connect to the database as the syn_user user.
//Failed to create a synonym.
OceanBase(SYN_USER@(none))>create synonym syn_1 for t1;
ERROR-00600: internal error code, arguments: -5036, Access denied; you need (at least one of) the CREATE SYNONYM privilege(s) for this operation

Connect to the database as the syn_user user and grant the CREATE SYNONYM permission to the user.
OceanBase(ADMIN@TEST)>grant CREATE SYNONYM on *.* to syn_user;
Query OK, 0 rows affected (0.03 sec)

Connect to the database as the syn_user user.
OceanBase(SYN_USER@(none))>create synonym syn_1 for t1;
Query OK, 0 rows affected (0.05 sec)