Manage synonyms

This topic describes how to create and delete synonyms.

In OceanBase Database, a synonym is an alias for a database object in a tenant. Synonyms make privilege management easier. You can use synonyms to mask the owner privilege of other data objects.

Create a synonym

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

Take note of the following items when you create a synonym:

• To create a private synonym in the current database (MySQL tenant) or schema (Oracle tenant), you must have the CREATE SYNONYM privilege.

• To create a private synonym in another database or schema, you must have the following privilege.

  • MySQL mode: CREATE SYNONYM

  • Oracle mode: CREATE ANY SYNONYM

• To create a public synonym, you must have the following privilege.

  • MySQL mode: CREATE SYNONYM

  • Oracle mode: CREATE PUBLIC SYNONYM

• You can create a synonym for a non-existent object. You do not need to have the permission to access the object that you create a synonym for.

To create a synonym, use the following syntax:

• MySQL mode

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

  For more information about the CREATE SYNONYM statement in MySQL mode, see the "CREATE SYNONYM" topic in SQL Reference (MySQL Mode).

• Oracle mode

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

  For more information about the CREATE SYNONYM statement in Oracle mode, see the "CREATE SYNONYM" topic in SQL Reference (Oracle Mode).

Sample commands:

• Creating a private synonym

  obclient> CREATE TABLE test(c1 int);
  Query OK, 0 rows affected (0.18 sec)
  
  obclient> CREATE SYNONYM s1 for test;
  Query OK, 0 rows affected (0.05 sec)
  
  obclient> INSERT INTO s1 VALUES(1);
  Query OK, 1 row affected (0.02 sec)
  
  obclient> SELECT * FROM s1;
  +------+
  | c1   |
  +------+
  |    1 |
  +------+
  1 row in set (0.01 sec)
  

• Create a public synonym

  obclient> CREATE PUBLIC SYNONYM syn_pub FOR test;
  Query OK, 0 rows affected (0.03 sec)
  
  obclient> SELECT * FROM syn_pub;
  +------+
  | c1   |
  +------+
  |    1 |
  +------+
  1 row in set (0.01 sec)
  

Drop a synonym

You can use the DROP SYNONYM statement to drop a synonym that you no longer need.

Take note of the following items when you drop a synonym:

• To drop a private synonym, the synonym must be in the corresponding database or schema, and you must have the following privilege:

  • MySQL mode: CREATE SYNONYM

  • Oracle mode: DROP ANY SYNONYM

• To drop a public synonym, you must have the following privilege:

  • MySQL mode: CREATE SYNONYM

  • Oracle mode: DROP PUBLIC SYNONYM

  At the same time, you must specify the PUBLIC keyword, and must not specify the database or schema in the synonym dropping statement.

To drop a synonym, use the following syntax:

• MySQL mode

  DROP [PUBLIC] SYNONYM [ DATABASE. ]synonym;
  

  For more information about the DROP SYNONYM statement in MySQL mode, see the "DROP SYNONYM" topic in SQL Reference (MySQL Mode).

• Oracle mode

  DROP [PUBLIC] SYNONYM [ schema. ]synonym;
  

  For more information about the DROP SYNONYM statement in Oracle mode, see the "DROP SYNONYM" topic in SQL Reference (Oracle Mode).

Sample commands:

• Drop a private synonym

  obclient> DROP SYNONYM test.s1;
  Query OK, 0 rows affected (0.03 sec)
  

• Drop a public synonym

  obclient> DROP PUBLIC SYNONYM syn_pub;
  Query OK, 0 rows affected (0.02 sec)