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