DWS如何实现业务隔离

业务隔离

DWS中可以使用Database和Schema实现业务的隔离，区别在于：

• Database之间无法直接互访，通过连接隔离实现彻底的权限隔离。各个Database之间共享资源极少，可实现连接隔离、权限隔离等。
• Schema隔离的方式共用资源较多，可以通过GRANT与REVOKE语法便捷地控制不同用户对各Schema及其下属对象的权限，从而赋给业务更多的灵活性。

从便捷性和资源共享效率上考虑，推荐使用Schema进行业务隔离。建议系统管理员创建Schema和Database，再赋予相关用户对应的权限。

权限控制

DATABASE

数据库Database是数据库对象的物理集合，不同Database之间资源完全隔离(除部分共享对象之外)。即Database是对业务的物理隔离，不同Database的之间的对象不能相互访问。比如在Database A中无法访问Databse B中的对象。因此登录集群的时候必须显示指定要连接的Databse。

SCHEMA

数据库里面通过Schema把数据库对象进行逻辑划分，在Database中，通过Schema实现对数据库对象的逻辑隔离。

通过权限管理实现在同一个session下对不同Schema下对象的访问和操作权限。Schema下则是各种应用程序会接触到的对象，比如表，索引，数据类型，函数，操作符等。

同一个Schema下，不能存在同名的数据库对象；但是不同Schema下的对象名可以重复。

gaussdb=> CREATE SCHEMA myschema; 
CREATE SCHEMA 
gaussdb=> CREATE SCHEMA myschema_1; 
CREATE SCHEMA 
 
gaussdb=> CREATE TABLE myschema.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE 
gaussdb=> CREATE TABLE myschema.t1(a int, b int) DISTRIBUTE BY HASH(b); 
ERROR:  relation "t1" already exists 
gaussdb=> CREATE TABLE myschema_1.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE

Schema实现了对业务的逻辑划分，反过来这些业务对象也对Schema形成一种依赖关系，因此当Schema下存在对象时，删除Schema的时候会报错，并提示具体的依赖信息。

gaussdb=> DROP SCHEMA myschema_1; 
ERROR: cannot drop schema myschema_1 because other objects depend on it  
Detail: table myschema_1.t1 depends on schema myschema_1  
Hint: Use DROP ... CASCADE to drop the dependent objects too.

当删除Schema的时候加上CASCADE选项，把Schema以及依赖此Schema的选项连带删除。

gaussdb=> DROP SCHEMA myschema_1 CASCADE; 
NOTICE:  drop cascades to table myschema_1.t1 
gaussdb=> DROP SCHEMA

USER/ROLE

用户或角色是数据库服务器(集群)全局范围内的权限控制系统，是集群业务的所有者和执行者，用于各种集群范围内所有的对象权限管理。因此角色不特定于某个单独的数据库，但角色登录集群的时候必须要显式指定登录的用户名，以保证当前连接执行的操作者的透明性。同时数据库也会通过权限管理限定用户的访问和操作权限。

用户是权限的最终体现者，所有的权限管理最终都体现在用户对数据库对象的操作权限是否被允许。

权限管理

DWS中的权限管理分为三种场景：

• 系统权限

系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。

系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。

• 用户权限

将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。

当声明了WITH ADMIN OPTION，被授权的用户可以将该权限再次授予其他角色或用户，以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。

数据库系统管理员可以给任何角色或用户授予/撤销任何权限。拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。

• 数据对象权限

将数据库对象（表和视图、指定字段、数据库、函数、模式等）的相关权限授予特定角色或用户。GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。

SCHEMA隔离应用示例

示例一：

Schema的owner默认拥有该Schema下对象的所有权限，包括删除权限；Database的owner默认拥有该Database下对象的所有权限，包括删除权限。因此建议对Database和Schema的创建要做比较严格的控制，一般建议使用管理员创建Database和Schema，然后把相关的权限控制赋给业务用户。

1.dbadmin在数据库testdb下把创建Schema的权限赋给普通用户user_1。

testdb=> GRANT CREATE ON DATABASE testdb to user_1; 
GRANT

2.切换到普通用户user_1。

testdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

用户user_1在数据库testdb下创建名为myschema_2的Schema。

testdb=> CREATE SCHEMA myschema_2; 
CREATE SCHEMA

3.切换到管理员dbadmin。

testdb=> RESET SESSION AUTHORIZATION; 
RESET

管理员dbadmin在模式myschema_2下创建表t1。

testdb=> CREATE TABLE myschema_2.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE

4.切换到普通用户user_1。

testdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

普通用户user_1删除管理员dbadmin在模式myschema_2下创建的表t1。

testdb=> drop table myschema_2.t1; 
DROP TABLE

示例二：

因为Schema的逻辑隔离的功能，访问数据库对象实际上要通过Schema和具体对象的两层校验。

1.把表myschema.t1的权限赋给用户user_1。

gaussdb=> GRANT SELECT ON TABLE myschema.t1 TO user_1; 
GRANT

2.切换到用户user_1。

SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

查询表myschema.t1。

gaussdb=> SELECT * FROM myschema.t1; 
ERROR:  permission denied for schema myschema 
LINE 1: SELECT * FROM myschema.t1;

3.切换到管理员dbadmin。

gaussdb=> RESET SESSION AUTHORIZATION; 
RESET

把myschema.t1的权限赋给用户user_1。

gaussdb=> GRANT USAGE ON SCHEMA myschema TO user_1; 
GRANT

4.切换到普通用户user_1。

gaussdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

查询表myschema.t1。

gaussdb=> SELECT * FROM myschema.t1; 
 a | b 
---+--- 
(0 rows)

数据库密码到期了，如何修改？

数据库管理员dbadmin的密码，可登录管理控制台选择集群所在行右边的“更多 > 重置密码”进行修改。

出于安全机制考虑，DWS在集群参数中通过以下2个关键参数管理帐户密码，在管理控制台，单击集群名称，切换到“参数修改”可进行参数修改。

• failed_login_attempts：输入密码错误的次数，超出设置值，数据库帐户会被自动锁定，可通过dbadmin管理帐户执行以下语句解锁。

ALTER USER user_name ACCOUNT UNLOCK;

• password_effect_time：帐户密码的有效期，单位为天，默认为90。

如何给指定用户赋予某张表的权限？

给指定用户赋予某张表的权限主要通过以下语法实现，本章主要介绍常见的几种场景，包括只读（SELECT）、插入（INSERT）、改写（UPDATE）和拥有所有权限。

语法格式

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...]  
      | ALL [ PRIVILEGES ] } 
    ON { [ TABLE ] table_name [, ...] 
       | ALL TABLES IN SCHEMA schema_name [, ...] } 
    TO { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ WITH GRANT OPTION ];

场景介绍

假设当前有用户u1u5，在系统中有对应的同名Schema u1u5，各用户的权限管控如下：

• u2作为只读用户，需要表u1.t1的SELECT权限。
• u3作为插入用户，需要表u1.t1的INSERT权限。
• u4作为改写用户，需要表u1.t1的UPDATE权限。
• u5作为拥有所有权限的用户，需要表u1.t1的所有权限。

表u1.t1的表权限分类

用户名	用户类型	Grant授权语句	查询	插入	修改	删除
u1	所有者	-	√	√	√	√
u2	只读用户	GRANT SELECT ON u1.t1 TO u2;	√	x	x	x
u3	插入用户	GRANT INSERT ON u1.t1 TO u3;	x	√	x	x
u4	改写用户	GRANT SELECT,UPDATE ON u1.t1 TO u4; 须知 授予UPDATE权限必须同时授予SELECT权限，否则会出现信息泄露。	√	x	√	x
u5	拥有所有权限的用户	GRANT ALL PRIVILEGES ON u1.t1 TO u5;	√	√	√	√

操作步骤

以下将演示不同权限的授权方法和验证过程。

1.打开窗口1（即dbadmin连接会话窗口，后续不再提示），使用系统管理员dbadmin连接DWS数据库，创建用户u1u5（系统默认会创建u1u5的同名SCHEMA）。

CREATE USER u1 PASSWORD '{password}'; 
CREATE USER u2 PASSWORD '{password}'; 
CREATE USER u3 PASSWORD '{password}'; 
CREATE USER u4 PASSWORD '{password}'; 
CREATE USER u5 PASSWORD '{password}';

2.在SCHEMA u1下创建表u1.t1。

CREATE TABLE u1.t1 (c1 int, c2 int);

3.为表中插入两条数据。

INSERT INTO u1.t1 VALUES (1,2); 
INSERT INTO u1.t1 VALUES (1,2);

4. DWS中引入了SCHEMA层概念，如果有SCHEMA，需要先给用户赋予SCHEMA的使用权限。

GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;

5.给只读用户u2赋予表u1.t1的查询权限。

GRANT SELECT ON u1.t1 TO u2;

6.打开窗口2（即用户u2连接会话窗口，后续不再提示），使用用户u2连接DWS数据库，验证u2可以查询u1.t1表，但是不能写入和修改，此时u2为只读用户。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

7.切回窗口1，分别给u3、u4、u5赋予对应的权限。

GRANT INSERT ON u1.t1 TO u3;    --插入用户u3，可以插入数据 
GRANT SELECT,UPDATE ON u1.t1 TO u4;   --改写用户u4，可以修改表 
GRANT ALL PRIVILEGES ON u1.t1 TO u5;   --拥有所有权限的用户u5，可以对表进行查询、插入、改写和删除

8.打开窗口3，使用用户u3连接DWS数据库，验证u3可以插入u1.t1，但是不能查询和修改，此时u3为插入用户。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

9.打开窗口4，使用用户u4连接DWS数据库，验证u4可以修改u1.t1，同时还可以查询，但是不能插入，此时u4为改写用户。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

10.打开窗口5，使用用户u5连接DWS数据库，验证u5可以查询、插入、修改和删除u1.t1，此时u5为拥有所有权限的用户。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; 
DELETE FROM u1.t1;

11.最后切回窗口1，通过函数has_table_privilege分别查询每个用户的权限。

SELECT * FROM pg_class WHERE relname = 't1';

返回结果，查看relacl字段，该字段回显结果如下。" rolename =xxxx/yyyy" --表示rolename对该表有xxxx权限，且权限来自yyyy；

例如下图，与以上验证结果完全一致。

• u1=arwdDxtA/u1, 表示u1为owner，拥有所有权限。
• u2=r/u1,表示u2拥有读权限。
• u3=a/u1,表示u3拥有插入权限。
• u4=rw/u1，表示u4拥有读和修改权限。
• u5=arwdDxtA/u1,表示u5拥有所有权限。

如何给指定用户赋予某个SCHEMA的权限？

给某个用户授权某个SCHEMA的权限，包括三个场景（本章节针对SCHEMA层级权限，仅演示查询权限，如果需要其他权限，可以参考《常见问题-操作类-帐户、密码、权限》中“如何给指定用户赋予某张表的权限？”章节：

• SCHEMA下当前某张表权限。
• SCHEMA下当前所有表的权限。
• SCHEMA下未来创建的表的权限。

假设有两个用户u1和u2，对应的同名SCHEMA是u1和u2，用户u2需要访问SCHEMA u1的表（包括当前的某张表、所有表、未来创建的表）。

1.打开窗口1（即dbadmin连接会话窗口，后续不再提示），使用系统管理员dbadmin连接DWS数据库，创建用户u1和u2（系统默认会创建u1和u2的同名SCHEMA）。

CREATE USER u1 PASSWORD '{password}'; 
CREATE USER u2 PASSWORD '{password}';

2.在SCHEMA u1下创建表u1.t1和u1.t2。

CREATE TABLE u1.t1 (c1 int, c2 int); 
CREATE TABLE u1.t2 (c1 int, c2 int);

3.给用户u2赋予SCHEMA u1的访问权限。

GRANT USAGE ON SCHEMA u1 TO u2;

4.（ 某张表权限 ）给用户u2赋予SCHEMA u1下某张表u1.t1的查询权限。

GRANT SELECT ON u1.t1 TO u2;

5.打开窗口2（即用户u2连接会话窗口，后续不再提示），使用用户u2连接DWS数据库，验证u2可以查询u1.t1表，但是不能查询u1.t2表。

SELECT * FROM u1.t1; 
SELECT * FROM u1.t2;

6.（ 所有表权限 ）切回窗口1，使用dbadmin给用户u2赋予SCHEMA u1下所有表的查询权限。

GRANT SELECT ON ALL TABLES IN SCHEMA u1 TO u2;

7.切回窗口2，再次验证u2可以查询所有表。

SELECT * FROM u1.t1; 
SELECT * FROM u1.t2;

8.切回窗口1，创建一张新的表u1.t3。

CREATE TABLE u1.t3 (c1 int, c2 int);

9.切回窗口2，验证发现用户u2没有u1.t3的查询权限，说明u2虽然有SCHEMA u1下所有表的访问权限，但是对于赋权后新创建的表还是没有访问权限（即u2对SCHEMA u1未来的表权限是没有的）。

SELECT * FROM u1.t3;

10.（未来表权限） 切回窗口1，给用户u2赋予SCHAMA u1未来创建的表的访问权限，并创建一张新的表u1.t4。

ALTER DEFAULT PRIVILEGES FOR ROLE u1 IN SCHEMA u1 GRANT SELECT ON TABLES TO u2; 
CREATE TABLE u1.t4 (c1 int, c2 int);

11.切回窗口2，验证发现用户u2可以访问u1.t4。但是对于之前的u1.t3还是没有访问权限（以上ALTER DEFAULT PRIVILEGES语句的授权范围只是未来创建的表，对于已经创建的表，还是要使用GRANT 单张表重新授权），此时需要参考步骤4重新授权u1.t3即可。

SELECT * FROM u1.t4;

如何创建数据库只读用户？

场景介绍

在业务开发场景中，数据库管理员通过SCHEMA来划分不同的业务，例如在金融行业中，负债业务属于SCHEMA s1，资产业务属于SCHEMA s2。

当前需要在数据库中创建一个只读用户user1，允许这个用户访问负债业务SCHEMA s1下所有的表（包括未来创建的新表），供日常读取，但是不允许做数据插入、修改或删除。

实现原理

DWS有基于角色的用户管理，需要先创建一个的只读角色role1，再将对应的角色授权到实际的用户user1即可。

操作步骤

1.使用系统管理员dbadmin连接DWS数据库。

2.执行以下SQL语句创建角色role1。

CREATE ROLE role1 PASSWORD disable;

3.执行以下SQL语句，为角色role1进行授权。

GRANT usage ON SCHEMA s1 TO role1;     --赋予SCHEMA s1的访问权限; 
GRANT select ON ALL TABLES IN SCHEMA s1 TO role1;    --赋予SHCEMA s1下所有表的查询权限; 
ALTER DEFAULT PRIVILEGES FOR USER tom IN SCHEMA s1 GRANT select ON TABLES TO role1;     --赋予SCHEMA s1未来创建的表的权限，其中tom为SCHEMA s1的owner

4.执行以下SQL语句，将角色role1授权到实际用户user1。

GRANT role1 TO user1;

5.使用只读用户user1进行SCHAMA s1下所有表数据的日常读取。

如何创建数据库私有用户和私有表？

场景介绍

在业务开发场景中，普通用户默认创建的表，系统管理员dbadmin还是有权访问的，并没有做到完全私有。而在三权分立开启下，管理员dbadmin虽然对普通用户的表没有访问权限，但同时也没有控制权限（DROP、ALTER、TRUNCATE）。

当前业务开发场景中，需要创建一个私有用户和私有表（即私有用户创建的表），这个私有表只有私有用户本身可以访问，系统管理员dbadmin和其他普通用户均无权访问（INSERT、DELETE、UPDATE、SELECT、COPY），但也希望在未经私有用户授权的情况下，可允许系统管理员dbadmin做DROP/ALTER/TRUNCATE操作。因此，私有用户应运而生，私有用户即拥有INDEPENDENT属性的用户。

实现原理

通过创建INDEPENDENT属性的用户来实现。

操作步骤

1.使用系统管理员dbadmin连接DWS数据库。

2.执行以下SQL语句创建私有用户u1。

CREATE USER u1 WITH INDEPENDENT IDENTIFIED BY "password";

3.使用u1重新连接DWS数据库，创建测试表，并插入数据。

CREATE TABLE test (id INT, name VARCHAR(20)); 
INSERT INTO test VALUES (1, 'joe'); 
INSERT INTO test VALUES (2, 'jim');

4.切换成dbadmin用户重新连接DWS数据库，并执行以下SQL语句验证是否可以访问，结果提示无权访问。

SELECT * FROM u1.test;

5.执行控制语句DROP，仍然可以删除成功。

DROP TABLE u1.test;

如何REVOKE某用户的connect on database权限？

DWS提供了一个隐式定义的拥有所有角色的组PUBLIC，所有创建的用户和角色默认拥有PUBLIC所拥有的权限。要撤销或重新授予用户和角色对PUBLIC的权限， 可通过在GRANT和REVOKE指定关键字PUBLIC实现。

DWS会将某些类型的对象上的权限授予PUBLIC。默认情况下，对表、表字段、序列、外部数据源、外部服务器、模式或表空间对象的权限不会授予PUBLIC，而以下这些对象的权限会授予PUBLIC：数据库的CONNECT权限和CREATE TEMP TABLE权限、函数的EXECUTE特权、语言和数据类型（包括域）的USAGE特权。当然，对象拥有者可以撤销默认授予PUBLIC的权限并专门授予权限给其他用户。为了更安全，建议在同一个事务中创建对象并设置权限，这样其他用户就没有时间窗口使用该对象。另外，这些初始的默认权限可以使用ALTER DEFAULT PRIVILEGES命令修改。

可参考以下示例，REVOKE某用户的connect on database权限：

1.执行以下命令连接DWS 集群的默认数据库postgres：

gsql -d postgres -h 192.168.0.89 -U dbadmin -p 8000 -r

根据界面提示输入密码后，显示如下信息表示gsql工具已经连接成功：

postgres=>

2.创建用户u1。

CREATE USER u1 IDENTIFIED BY 'password'; 
CREATE USER

3.确认u1正常访问。

gsql -d postgres -h 192.168.0.89 -U u1 -p 8000 -W password -r 
gsql ((GaussDB 8.1.0 build be03b9a0) compiled at 2021-03-12 14:18:02 commit 1237 last mr 2001 release) 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, bits: 128) 
Type "help" for help.

4.撤销public的connect on database权限。

gsql -d postgres -h 192.168.0.89 -U dbadmin -p 8000 -r 
postgres=> 
 
REVOKE CONNECT ON database gaussdb FROM public; 
REVOKE

                    
说明
                    
若直接使用revoke connect on database postgres from u1命令撤销u1用户的权限不会生效，因为数据库的CONNECT权限授予了PUBLIC，需指定关键字PUBLIC实现。
                    
                  
                  

5.验证结果，显示如下内容表示用户u1的connect on database权限已成功撤销。

gsql -d postgres -h 192.168.0.89 -U u1 -p 8000 
gsql: FATAL:  permission denied for database "gaussdb" 
DETAIL:  User does not have CONNECT privilege.

如何查看某个用户有哪些表的权限？

场景一：查看用户有哪些表的权限 ，可以通过information_schema.table_privileges查看。例如

SELECT * FROM information_schema.table_privileges WHERE GRANTEE='user_name'; 

• grantor :赋权用户
• grantee：被赋权用户
• table_catalog：数据库名
• table_schema：模式名
• table_name：对象名
• privilege_type：被赋予的权限

如上图所示，表示用户u2拥有Schema u2下的t2的所有权限和Schema u1下的t1的SELECT 权限 。

场景二：查看用户是否有某张表的权限 ，可以通过以下方法。

1.执行以下语句查询pg_class系统表。

select * from pg_class where relname = 'tablename';

查看relacl字段，该字段回显结果如下，权限参数参见下表“权限的参数说明”。

• " rolename =xxxx/yyyy" --表示rolename对该表有xxxx权限，且权限来自yyyy；
• "=xxxx/yyyy" -- 表示public对该表有xxxx权限，且权限来自yyyy。

例如下图：

joe=arwdDxtA，表示joe用户有所有权限（ALL PRIVILEGES）。

leo=arw/joe，表示leo用户拥有读、写、改权限，该权限来自joe授权。

权限的参数说明

参数	参数说明
r	SELECT（读）
w	UPDATE（写）
a	INSERT（插入）
d	DELETE
D	TRUNCATE
x	REFERENCES
t	TRIGGER
X	EXECUTE
U	USAGE
C	CREATE
c	CONNECT
T	TEMPORARY
A	ANALYZE
arwdDxtA	ALL PRIVILEGES（用于表）
*	给前面权限的授权选项

2.如果要查某用户对某张表是否有某种权限，也可以通过访问权限查询函数has_table_privilege进行查询。

select * from has_table_privilege('用户名','表名','select');

例如，查询joe对表t1是否有查询权限。

select * from has_table_privilege('joe','t1','select');

Ruby是什么用户？

在执行SELECT * FROM pg_user语句查看当前系统的用户时，看到Ruby用户且拥有很多权限。

Ruby用户为官方运维使用帐户，DWS数据库创建后，默认生成Ruby帐户，不涉及安全风险，请放心使用。