All Products
Search
Document Center

CREATE SYNONYM

Last Updated: Jun 18, 2021

Description

The CREATE SYNONYM statement creates a synonym.

Syntax

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

Parameter description

Parameter

Description

OR REPLACE

Specifies that if the name of the synonym to be created already exists, the synonym is recreated based on the new definition.

PUBLIC

Specify PUBLIC to create a public synonym that can be used by all users. To use the synonym, users must have the corresponding permission on the basic object.

When the reference to an object is resolved, the public synonym is used only if no schema is specified for the object.

If you do not specify PUBLIC, the synonym is private and can be accessed by only the current schema. In addition, the synonym name must be unique in the current schema.

[ schema. ]synonym

schema specifies the schema of the current synonym. If you specify PUBLIC, you do not need to specify a schema for the synonym. synonym specifies the name of the synonym.

[ schema. ]object

The name of the object that corresponds to the synonym.

Examples

  • Create a synonym.

OceanBase(TEST@TEST)>create table t1(c1 int);
Query OK, 0 rows affected (0.11 sec)

OceanBase(TEST@TEST)>create synonym s1 for t1;
Query OK, 0 rows affected (0.04 sec)

OceanBase(TEST@TEST)>insert into s1 values(1);
Query OK, 1 row affected (0.04 sec)

OceanBase(TEST@TEST)>select * from s1;
+------+
| C1   |
+------+
|    1 |
+------+
1 row in set (0.02 sec)
  • Create a public synonym.

OceanBase(TEST@TEST)>create public synonym syn_pub for t1;
Query OK, 0 rows affected (0.04 sec)

OceanBase(TEST@TEST)>select * from syn_pub;
+------+
| C1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

Considerations

To create synonyms, the following permission requirements must be met:

  • To create a private synonym in the current schema, you must have the CREATE SYNONYM permission.

  • To create a private synonym in a schema that is not the current schema, you must have the CREATE ANY SYNONYM permission.

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

  • The object for which you need to create a synonym does not need to exist. Access permissions are not required.

// Create synonym_user.
OceanBase(TEST@TEST)>CREATE USER synonym_user IDENTIFIED BY synonym_user;
Query OK, 0 rows affected (0.04 sec)

OceanBase(TEST@TEST)>grant CREATE on test.* to synonym_user;
Query OK, 0 rows affected (0.05 sec)

OceanBase(TEST@TEST)>grant SELECT on test.* to synonym_user;
Query OK, 0 rows affected (0.02 sec)

// Connect to synonym_user.
OceanBase(SYNONYM_USER@TEST)>create or replace synonym s1 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

// Grant the CREATE SYNONYM permission again.
OceanBase(TEST@TEST)>grant CREATE SYNONYM on *.* to synonym_user;
Query OK, 0 rows affected (0.03 sec)

OceanBase(SYNONYM_USER@TEST)>create synonym s1 for t1;
Query OK, 0 rows affected (0.07 sec)