OceanBase CREATE SYNONYM

描述

该语句用来创建一个同义词。

格式

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

参数说明

举例

• 创建一个同义词。

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)

• 创建一个 PUBLIC 同义词

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)

注意事项

创建同义词时，需要满足如下权限要求：

• 在当前的 DataBase 下创建私有的 synonym，需要 CREATE SYNONYM 权限
• 在非当前 DataBase 下创建私有的 synonym，需要 CREATE ANY SYNONYM 权限
• 创建 PUBLIC 的 synonym，需要 CREATE PUBLIC SYNONYM 权限
• 对于需要创建 synonym 的对象不需要存在，也不需要具有访问权限

连接sys用户:
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)


连接syn_user
//创建同义词失败
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

连接sys用户,给定CREATE SYNONYM权限
OceanBase(ADMIN@TEST)>grant CREATE SYNONYM on *.* to syn_user;
Query OK, 0 rows affected (0.03 sec)

连接syn_user
OceanBase(SYN_USER@(none))>create synonym syn_1 for t1;
Query OK, 0 rows affected (0.05 sec)