你應該知道的數倉安全：都是同名Schema惹的禍-有解無憂

摘要：我是管理員賬號，怎么還沒有權限？當小伙伴詢問的時候，我第一時間就會想到都是用戶同名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的權限報錯，根據報錯的提示，授予用戶相應的權限就可以解決權限報錯問題，

點擊關注，第一時間了解華為云新鮮技術~

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/519191.html

標籤：其它

上一篇：如何搭建一套學習、開發PostgreSQL的環境

下一篇：全球名校AI課程庫（12）| CMU卡內基梅隆 · 資料庫系統進階課程『Advanced Database Systems』