Manage synonyms

This topic describes how to create and drop synonyms.

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

Create a synonym

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

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

• To create a private synonym for the current user, you must have the CREATE SYNONYM privilege.

• To create a private synonym for another user, you must have the CREATE ANY SYNONYM privilege.

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

• You can create a synonym for a non-existent object. You do not need to be authorized to access the object for which you want to create a synonym.

For information about how to query user privileges, see Query user privileges. For information about how to grant user privileges, see Grant direct privileges.

To create a synonym, use the following syntax:

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

For more information about the CREATE SYNONYM statement, see SQL reference (Oracle mode).

Here are two examples:

• Create 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 rules when you drop a synonym:

• To drop a private synonym, make sure that this private synonym is under the corresponding database or schema, and that you have the DROP ANY SYNONYM privilege.

• To drop a public synonym, you must have the DROP PUBLIC SYNONYM privilege.

  In addition, you must specify the PUBLIC keyword, and cannot specify the database or schema in the synonym dropping statement.

For information about how to query user privileges, see Query user privileges. For information about how to grant user privileges, see Grant direct privileges.

The syntax for dropping a synonym is as follows:

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

For more information about the DROP SYNONYM statement, see OceanBase Database SQL Reference (Oracle Mode).

Here are two examples:

• 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)