摘要：我是管理員賬號，怎麼還沒有許可權？當小伙伴詢問的時候，我第一時間就會想到都是用戶同名Schema惹的禍 本文分享自華為雲社區《你應該知道的數倉安全——都是同名Schema惹的禍》，作者： zhangkunhn 。 典型場景 經常遇到小伙伴問到： 我是管理員賬號，怎麼還沒有許可權？ 管理員給我賦權了 ...
摘要：我是管理員賬號，怎麼還沒有許可權？當小伙伴詢問的時候，我第一時間就會想到都是用戶同名Schema惹的禍
本文分享自華為雲社區《你應該知道的數倉安全——都是同名Schema惹的禍》，作者： zhangkunhn 。
典型場景
經常遇到小伙伴問到：
- 我是管理員賬號，怎麼還沒有許可權？
- 管理員給我賦權了啊，怎麼還沒有許可權？
當小伙伴詢問的時候，我第一時間就會想到都是用戶同名Schema惹的禍。
同名Schema是私有Schema
我們知道，CREATE USER語法在創建用戶的同時會在當前資料庫中創建一個與用戶同名的SCHEMA。這個Schema很特殊，只有兩種用戶能在這個Schema下麵創建表、視圖、函數等對象：
- 用戶自己
- 管理員
然而，不管誰創建的，對象的所有者（Owner）都是用戶自己。基於這個事實，我們可以稱用戶同名Schema為私有Schema。私有表明瞭當前Schema的特殊性，在這個Schema下麵的所有對象都是這個用戶自己的，不管是誰創建的。
我們來看一個例子。資料庫中有三個用戶，如表所示。
使用管理員dbadmin執行以下SQL：
gaussdb=# create table ua.ta (c1 int); CREATE TABLE gaussdb =# select relname, relowner, rolname from pg_class c, pg_authid a where relname = 'ta' and c.relowner= a.oid; relname | relowner | rolname ---------+----------+--------- t1 | 16546 | ua(不是dbadmin) (1 row)
可以看到：系統管理員在普通用戶同名schema下創建的對象，所有者為schema的同名用戶
讓我們來總結第一點：同名Schema是私有Schema，這個Schema下麵的所有對象的所有者都是用戶自己，不管是誰創建的。管理員在私有Schema下創建的表等對象會發生Owner切換。
視圖規則：按照view的owner做許可權檢查
再來談視圖和視圖封裝的基表的許可權。視圖對基表的許可權檢查是按照視圖的Owner做許可權檢查。例如
create view v1 as select * from t1;
用戶執行select * from v1時做許可權檢查分為兩步：
- 首先檢查當前用戶對視圖v1的SELECT許可權；
- 然後檢查視圖v1的owner對基表t1的SELECT許可權。
而不是直接檢查當前用戶對基表t1的SELECT許可權。
總結第二點：視圖會按照視圖的Owner對基表做許可權檢查。
私有Schema與視圖規則導致莫名其妙的許可權報錯
由於私有Schema會造成Owner切換，而視圖規則要求對基表按照視圖Owner做許可權檢查。那麼在私有Schema下麵創建視圖就會導致莫名其妙的現象：
私有Schema + view規則 --> 管理員無許可權訪問自己創建的視圖。
gaussdb =# set role dbadmin password ‘*******’; -- 切換到管理員用戶 SET gaussdb => create table ua.ta (c1 int); -- 表ta的owner是？？？ CREATE TABLE gaussdb => create view ub.vb as select * from ua.ta; -- 視圖vb的owner是？？？ CREATE VIEW gaussdb => select * from ub.vb; -- 管理員創建的view，他竟然無許可權！！！ ERROR: SELECT permission denied to user “ub” for relation “ua.ta“
我們以管理員用戶在用戶ua的私有schema下創建表ta, 之後在用戶ub的schema下創建了視圖vb, 視圖vb的基表是ua.ta。管理員執行對視圖vb的查詢，報錯無許可權。
對於這個莫名其妙的現象，我們仔細捋一捋其中的來龍去脈。
- 根據私有Schema切換Owner的法則，儘管是管理員創建的，ta的owner切換到ua, 同樣vb的owner應該是u2.
- 結合view規則，對基表按照視圖Owner做許可權檢查，視圖vb的owner對基表ua.ta是否具有select許可權。視圖vb的owner是ub，而ub對ua.ta無select許可權，因此查詢報錯。
許可權報錯消除
如何解決這種許可權報錯呢？從上述梳理中，其實已經明白瞭如何賦權來消除這種報錯。那就是給視圖的owner用戶ub賦予基表ua.ta的SELCT許可權：管理員或者用戶ua執行下麵的賦權語句即可。
GRANT SELECT on ua.ta to ub;
有小伙伴 問了，我每次都這麼仔細捋一捋，感覺很浪費時間，有沒有簡單的方法。答案是有的，只需從查詢的許可權報錯著手，不需要每次都捋一捋。
我們當前的許可權報錯有著非常完備的提醒，會給用戶顯示如下提示：
SELECT permission denied to user “user_name” for relation “ schema_name.table_name“
可以看到，許可權報錯包括哪個許可權、哪個用戶、哪個schema的哪個對象。那麼看到這個之後，可以直接找管理員或者Owner來執行授予操作就可以了。
對於上述報錯，直接就對應到賦權語句：
grant SELECT on schema_name.table_name to user_name;
當然這個賦權跟我們前面的分析是殊途同歸的。
現在來回想下，是不是一切都清晰了。那麼我們再來看一遍示例。以管理員dbadmin執行以下SQL語句。
gaussdb => create table ua.ta (c1 int); -- ta的owner是ua CREATE TABLE gaussdb => create view ub.vb as select * from ua.ta; -- vb的owner是ub CREATE VIEW postgres=# select * from ub.vb; -- 按照報錯的指引來 ERROR: SELECT permission denied to user “ub” for relation “ua.ta“ gaussdb =# grant usage on schema ua to ub; -- 將schema ua和基表ta的許可權給ub GRANT gaussdb =# grant select on ua.ta to ub; GRANT postgres=# select * from ub.vb; -- 許可權檢查通過，可以正常查詢 c1 ---- (0 rows)
補充知識：
- CREATE USER語法在創建用戶的同時會在當前資料庫中，為該用戶創建一個同名的SCHEMA；其他資料庫中，則不會創建同名的SCHEMA；如果需要，可使用create schema authorization user_name語法，該語法會根據用戶名來創建同名schema。
- 為什麼用戶同名Schema這麼特殊，別的Schema沒有這些特點？因為：a) 在創建用戶時同時創建了與用戶同名的Schema，並將Schema的owner設置為同名用戶；b) 在創建對象時，如果創建對象的schema是用戶同名Schema，就會將對象的Owner切換為同名用戶，而不是執行SQL語句的當前用戶。
總結
遇到許可權報錯第一時間想到是否涉及同名schema。同名Schema是用戶的私有Schema。私有Schema中所有的對象Owner都是用戶自己，不管是誰創建的。在私有Schema中創建對象，對象Owner會切換到同名用戶。視圖規則是按照視圖Owner來檢查對基表的許可權。由於私有Schema的Owner切換機制和視圖規則導致了同名Schema的許可權報錯。根據報錯的提示，授予用戶相應的許可權就可以解決許可權報錯問題。
