Mar 13, 2023

RedShift 使用者權限查詢

整理一下查詢使用者權限的方法。

預設權限

RedShift 提供了 SVV_DEFAULT_PRIVILEGES 這張表來查詢預設權限：

SELECT
    owner_type,
    owner_name,
    schema_name,
    object_type,
    privilege_type,
    grantee_name,
    admin_option
FROM svv_default_privileges
WHERE
    (
        grantee_type = 'user'
        AND grantee_name = 'user1'
    )
    OR grantee_type = 'public'

考慮到有些預設權限是全域賦予的，這裡有補了一個 grantee_type = 'public' 的語句。

其輸出為：

owner type	owner name	schema name	object type	privilege type	grantee name	admin option
user	admin	foo	RELATION	SELECT	user1	True
user	user2		RELATION	SELECT	public	False

其解讀方式為：

當使用者 admin 在 schema foo 建立新的 relation 時，user1 會被自動賦予 SELECT 權限，附帶再賦予此權限給其他使用者的權限。
當使用者 user2 在任意 schema 建立新的 relation 時，public 會被自動賦予 SELECT 權限，不附帶再賦予的權限。故 user1 也可以從此詞條得到對應的權限。

輸出的值的部分：

Object type 可為 RELATION, FUNCTION 或 PROCEDURE，而 table 及 view 共掛為 relation 這個類別。
Privilege type 的值有 INSERT, SELECT, UPDATE, DELETE, REFERENCES 或 DROP。

另外一種方法：使用 PostgreSQL 的 `pg_default_acl` 查詢

RedShift 的底層為 PostgreSQL 故我們也能用 PSQL 的方法來做查詢。使用者預設權限被列在 PG_DEFAULT_ACL 裡面，可利用以下的指令查詢：

SELECT
    pg_get_userbyid(pg_default_acl.defacluser) AS grantor,
    pg_namespace.nspname as schema,

    CASE pg_default_acl.defaclobjtype
        WHEN 'r' THEN 'relation'
        WHEN 'f' THEN 'function'
        WHEN 'p' THEN 'stored procedure'
    END AS object_type,

    array_to_string(pg_default_acl.defaclacl, ', ')  as default_privileges

FROM pg_catalog.pg_default_acl
LEFT JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_default_acl.defaclnamespace

其輸出資訊如：

grantor	schema	object_type	default_privileges
admin		relation	user1=r/admin
admin	foo	relation	user2=w*/admin

其解讀方式為：當 <grantor> 在 <schema> 下建立起新的 <object_type> 時，<default_privileges> 清單中的權限會被賦予。

其中 default_privileges 是被攤平的 aclitem[] 陣列，內文格式會以 使用者 = 權限字串 / 賦予者 呈現，又權限字串是以下列字元湊合而成：

privilege string	represent
`r`	查詢 (read; `SELECT`)
`a`	新增 (append; `INSERT`)
`w`	修改 (write; `UPDATE`)
`d`	刪除 (`DELETE`)
`x`	允許建立 foreign key
`X`	執行函數或呼叫程序
`*`	用於其餘權限的後綴，代表此使用者得以將此權限再賦予給其他使用者

以上面預設權限的表格為例：

當 admin 在任意 schema 建立 table 或 view 時，user1 會被賦予 SELECT 權限
當 admin 在 schema foo 建立 table 或 view 時，user2 會被賦予附帶 GRANT 能力的 UPDATE 權限

命名空間權限

對於使用者在 schema 的既有權限可利用 SVV_SCHEMA_PRIVILEGES 查詢。由於我只想要查詢特定使用者的權限，故使用這樣的指令：

SELECT
    identity_name,
    namespace_name AS schema,
    privilege_type,
    admin_option
FROM SVV_SCHEMA_PRIVILEGES
WHERE
    (
        identity_type = 'user'
        AND identity_name = 'user1'
    )
    OR identity_type = 'public'

輸出資訊如：

identity name	schema	privilege type	admin option
user1	foo	CREATE	False
public	foo	USAGE	False

其中 privilege type 可為 USAGE 或 CREATE。

表格權限

我們可以從 SVV_RELATION_PRIVILEGES 查詢使用者在表格的權限。一樣的情境，由於我想要查詢特定使用者的權限，故：

SELECT
    identity_name,
    namespace_name AS schema,
    relation_name,
    privilege_type,
    admin_option
FROM SVV_RELATION_PRIVILEGES
WHERE
    (
        identity_type = 'user'
        AND identity_name = 'user1'
    )
    OR identity_type = 'public'

其中 privilege type 的值有 INSERT, SELECT, UPDATE, DELETE, REFERENCES 或 DROP。