CREATE SYNONYM · k8s原理与实践

[TOC] # CREATE SYNONYM ## 描述该语句用来创建一个同义词。 ## 格式 ~~~ CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ DATABASE. ]synonym FOR [ DATABASE. ]object; ~~~ ## 参数说明 <table data-tag="table" id="table-8fo-ddm-cws" class="table"><colgroup width="240" span="1" data-tag="col" id="col-g7x-6ej-x5m" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="466" span="1" data-tag="col" id="col-oo9-f7r-kan" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-nmp-c60-w3c" class="thead"><tr id="tr-ox8-g0b-bum"><th id="td-8se-fol-vrf"><p id="p-gwu-8eh-l1z"><b>参数</b></p></th><th id="td-nv8-int-mvh"><p id="p-im2-kjn-kef"><b>描述</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-anb-nly-kiq" class="tbody"><tr data-tag="tr" id="tr-wrh-2rj-jlg" class="tr"><td data-tag="td" id="td-4mh-27s-mp6" class="td"><p data-tag="p" id="p-8wx-s7l-lib" class="p">OR REPLACE</p></td><td data-tag="td" id="td-r5h-33b-2l6" class="td"><p data-tag="p" id="p-u9h-0xl-8lx" class="p">表示如果要创建的同义词名称已存在，则使用新的定义重新创建同义词。</p></td></tr><tr data-tag="tr" id="tr-a6a-56u-62i" class="tr"><td data-tag="td" id="td-hq2-e9p-1tt" class="td"><p data-tag="p" id="p-42c-dvg-hg9" class="p">PUBLIC</p></td><td data-tag="td" id="td-b6k-yjg-xyh" class="td"><p data-tag="p" id="p-lai-4zy-4ru" class="p">指定PUBLIC来创建公共同义词，所有用户都可以使用。用户必须对基础对象具有相应的权限才能使用该同义词。</p><p data-tag="p" id="p-p48-0jw-lq7" class="p">在解析对象的引用时，仅当对象没有指定DataBase时，才会使用公共同义词。</p><p data-tag="p" id="p-2qz-tkn-64h" class="p">如果不指定PUBLIC，则同义词是私有的，只能当前DataBase访问，并且同义词名称在当前DataBase中必须唯一。</p></td></tr><tr data-tag="tr" id="tr-bxi-ez7-3yu" class="tr"><td data-tag="td" id="td-6ny-ubj-l78" class="td"><p data-tag="p" id="p-a7p-zae-0r0" class="p">[ DataBase. ]synonym</p></td><td data-tag="td" id="td-xjl-oh1-qbz" class="td"><p data-tag="p" id="p-mvb-i66-i1g" class="p">DataBase指定当前同义词属于哪个DataBase，如果指定了PUBLIC，则对同义词不需要指定DataBase；synonym表示同义词的名称。</p></td></tr><tr data-tag="tr" id="tr-wrl-ytr-ibc" class="tr"><td data-tag="td" id="td-x0m-2ee-hqm" class="td"><p data-tag="p" id="p-emp-42d-9dp" class="p">[ DataBase. ]object</p></td><td data-tag="td" id="td-vi0-e6l-3my" class="td"><p data-tag="p" id="p-430-0kv-9x4" class="p">表示同义词对应对象的名称。</p></td></tr></tbody></table> ## 举例 * 创建一个同义词。 ~~~ 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) ~~~