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 under the current user, you must have the CREATE SYNONYM privilege.

• To create a private synonym under other users, 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 Modify user privileges.

Syntax for creating a synonym:

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

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

Examples:

• Create a private synonym.

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

• Create a public synonym.

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

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 user, 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 Modify user privileges.

Syntax for dropping a synonym:

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

For more information about the DROP SYNONYM statement, see DROP SYNONYM.

Examples:

• Drop a private synonym.

  obclient> DROP SYNONYM test.s1;
  Query OK, 0 rows affected
  

• Drop a public synonym.

  obclient> DROP PUBLIC SYNONYM syn_pub;
  Query OK, 0 rows affected