DWS用户权限总结

1、用户权限介绍

授权命令：

https://support.huaweicloud.com/sqlreference-dws/dws_06_0250.html

给某个用户授权某个SCHEMA的权限，包括三个场景：

https://support.huaweicloud.com/dws_faq/dws_03_0196.html

• 授予SCHEMA下当前某张表权限。

  GRANT USAGE ON SCHEMA u1 TO u2;
  GRANT SELECT ON u1.t1 TO u2;

• 授予SCHEMA下当前所有表的权限。

  GRANT USAGE ON SCHEMA u1 TO u2;
  GRANT SELECT ON ALL TABLES IN SCHEMA u1 TO u2;

• 授予SCHEMA下未来创建的表的权限。

  GRANT USAGE ON SCHEMA u1 TO u2;
  ALTER DEFAULT PRIVILEGES FOR ROLE u1 IN SCHEMA u1 GRANT SELECT ON TABLES TO u2;

查询权限命令：

https://support.huaweicloud.com/dws_faq/dws_03_0201.html

-- 查看用户
\du
​
-- 查询库下的schema
\dn
​
-- 查看schema属主
\dn dw_pwork
​
-- 查看schema默认权限
\ddp dw_pwork
​
-- 查看schema属主
\dn dw_pwork
​
-- 查询表权限
SELECT relacl FROM pg_class WHERE relname = 'test_zbb_1213';
​
-- 查询用户拥有的表权限
SELECT * FROM information_schema.table_privileges WHERE GRANTEE='test_zbb_1213';
​
-- 查询用户拥有的库权限
select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='liusiyi' group by a.datname,b.rolname;
​
-- 查询用户拥有的schema权限
select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='test_zbb_1213' group by a.nspname,b.rolname;
​
-- 查看所有用户对所有schema的create权限
SELECT a.rolname, n.nspname FROM pg_authid AS a, pg_namespace AS n WHERE has_schema_privilege(a.oid, n.oid, 'CREATE');
​
-- 查看所有用户对某张表的权限
SELECT * FROM INFORMATION_SCHEMA.role_table_grants WHERE table_name = 't1';
​
-- 查看schema是否存在
SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ('s1','s2');
​
-- 查看用户是否存在
SELECT usename FROM pg_catalog.pg_user WHERE upper(usename) IN ('U1','U2','U3','U4');

2、案例（未来表无权限访问问题）

2.1 背景

-- 创建用户test_zbb_1212，拥有schema dw_pwork的当前所有表和未来表的读权限
create user test_zbb_1212 password 'xxx';
grant usage on schema dw_pwork to test_zbb_1212;
grant select on all tables in schema dw_pwork to test_zbb_1212;
alter default privileges for role dw_pwork in schema dw_pwork grant select on tables to test_zbb_1212;
​
-- 创建用户test_zbb_1213，拥有schema dw_pwork的建表权限
create user test_zbb_1213 password 'xxx';
grant create on schema dw_pwork to test_zbb_1213;
grant dw_pwork to test_zbb_1213; 
​
-- 用户test_zbb_1213在schema dw_pwork下创建表test_zbb
gsql -d wc_test -p 26308 -U test_zbb_1213 -W xxx -ar
create table dw_pwork.test_zbb(id int);
​
-- 用户test_zbb_1212查询表dw_pwork.test_zbb报错 ERROR: SELECT permission denied to user "test_zbb_1212" for relation "dw_pwork.test_zbb"
gsql -d wc_test -p 26308 -U test_zbb_1212 -W xxx -ar
select * from dw_pwork.test_zbb;

2.2 分析

• 案例中schema dw_pwork的属主是spdbdba，没有名为dw_pwork的role

• ALTER DEFAULT PRIVILEGES FOR ROLE spdbdba IN SCHEMA dw_pwork GRANT SELECT ON TABLES TO test_zbb_1212;        未来表授权语句改成FOR ROLE spdbdba后，还是查询失败

• ALTER DEFAULT PRIVILEGES FOR ROLE test_zbb_1213 IN SCHEMA dw_pwork GRANT SELECT ON TABLES TO test_zbb_1212;        未来表授权语句改成FOR ROLE test_zbb_1213后，可以查询成功

2.3 结论

schema与对应owner同名（都是dw_pwork）：

• ALTER DEFAULT PRIVILEGES [FOR USER dw_pwork] IN SCHEMA dw_pwork GRANT SELECT ON TABLES TO test_zbb_1212;        FOR USER dw_pwork可已省略不写，如果省略FOR ROLE/USER，则缺省值为当前角色/用户。参考 https://support.huaweicloud.com/dws_faq/dws_03_0196.html

• 通过上述语句授权未来表权限，其他用户在schema dw_pwork下创建的表，test_zbb_1212都有权限查询；

schema与对应owner不同名：

• ALTER DEFAULT PRIVILEGES FOR USER test_zbb_1213 IN SCHEMA dw_pwork GRANT SELECT ON TABLES TO test_zbb_1212;        FOR USER使用创建表的owner，即test_zbb_1213，test_zbb_1212只能查询test_zbb_1213新建的表