GaussDB(DWS) SQL进阶-database、schema、user和权限控制

1. 前言

• 适用版本：【8.1.3及以上】

  database是GaussDB(DWS)对象的物理集合，不同database之间资源完全隔离(除部分共享对象之外)。一个database里面通过schema把数据库对象进行逻辑划分，通过权限管理实现在同一个session下对不同schema下对象的访问和操作权限。

2. DATABASE

  在GaussDB(DWS)中，database是对业务的物理隔离，不同database的之间的对象不能相互访问。比如在databaseA中无法访问databse B中的对象。因此登录集群的时候必须显示指定要连接的databse。

  在GaussDB(DWS)中创建database时，需要重点关注字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)两个配置项。ENCODING指明了数据库存储的数据的编码格式，为了适应全球化，创建DATABASE的时候建议使用UTF-8编码。DBCOMPATIBILITY 指明了DATABASE的兼容性选项，GaussDB(DWS)支持Oracle、Teradata和MySQL三种兼容模式，分别兼容Oracle、Teradata和MySQL语法；若不指定DBCOMPATIBILITY，则默认为ORA。需要注意的是, 数据库一旦创建，这两个属性就不能修改，甚至语法层就没有提供修改这两个属性的接口。

3. SCHEMA

  在GaussDB(DWS)中，schema是DATABASE下一个特殊的对象，实现对数据库对象的逻辑隔离，从功能上类似于一些编程语言中namespace的概念。同一个schema下，不能存在同名的数据库对象；但是不同scheam下的对象名可以重复。

postgres=# CREATE SCHEMA nsp_1;
CREATE SCHEMA
postgres=# CREATE SCHEMA nsp_2;
CREATE SCHEMA
postgres=# CREATE TABLE nsp_1.t1(a int, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
postgres=# CREATE TABLE nsp_1.t1(a int, b int) DISTRIBUTE BY HASH(b);
ERROR:  relation "t1" already exists
postgres=# CREATE TABLE nsp_2.t1(a int, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
postgres=#

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

postgres=# -- 删除失败，提示依赖关系
postgres=# DROP SCHEMA nsp_1;
ERROR:  cannot drop schema nsp_1 because other objects depend on it
DETAIL:  table nsp_1.t1 depends on schema nsp_1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

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

postgres=# -- 删除成功，显示级联删除对象
postgres=# DROP SCHEMA nsp_1 CASCADE;
NOTICE:  drop cascades to table nsp_1.t1
DROP SCHEMA

4. search_path

  search_path又称之为模式搜索路径，本身是一个guc参数。对于未定义schema的对象，会根据search_path的配置赋予默认的schema或者默认的schema搜索范围。

1) 创建对象时，会在search_path指定的schema列表中的第一个schema下创建对象。

postgres=# DROP SCHEMA IF EXISTS nsp_1 CASCADE;
NOTICE:  drop cascades to table nsp_1.t1
DROP SCHEMA
postgres=# DROP SCHEMA IF EXISTS nsp_2 CASCADE;
NOTICE:  drop cascades to table t1
DROP SCHEMA
postgres=# CREATE SCHEMA nsp_1;
CREATE SCHEMA
postgres=# CREATE SCHEMA nsp_2;
CREATE SCHEMA
postgres=#
postgres=# SET search_path TO nsp_1, nsp_2;
SET
postgres=# CREATE TABLE t1(a text, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES('nsp_1', 1);
INSERT 0 1
postgres=#-- 此处创建的表t1在schema nsp_1下
postgres=# SELECT schemaname, tablename, tablecreator, created FROM pg_tables WHERE tablename = 't1';
 schemaname | tablename | tablecreator |            created
------------+-----------+--------------+-------------------------------
 nsp_1      | t1        | jyh          | 2021-03-30 16:50:12.930546+08
(1 row)

postgres=# SET search_path to nsp_2, nsp_1;
SET
postgres=# CREATE TABLE t1(a text, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES('nsp_2', 1);
INSERT 0 1
postgres=#-- 此处创建的表 t1 在schema nsp_2下
postgres=# SELECT schemaname, tablename, tablecreator, created FROM pg_tables WHERE tablename = 't1';
 schemaname | tablename | tablecreator |            created
------------+-----------+--------------+-------------------------------
 nsp_1      | t1        | jyh          | 2021-03-30 16:50:12.930546+08
 nsp_2      | t1        | jyh          | 2021-03-30 16:50:22.476881+08
(2 rows)

2) 查询对象时，如果对象没有显式指明schema，GaussDB(DWS)会按照search_path中指明的schema列表，顺序查找指定名称的对象。如果遍历完所有的schema都没有查找到同名对象，数据库会直接报错。

postgres=# SET search_path TO nsp_1, nsp_2;
SET
postgres=# -- 查询的表 t1 在schema nsp_1下
postgres=# SELECT * FROM t1;
   a   | b
-------+---
 nsp_1 | 1
(1 row)

postgres=# SET search_path TO nsp_2, nsp_1;
SET
postgres=# -- 查询的表 t1 在schema nsp_2下
postgres=# SELECT * FROM t1;
   a   | b
-------+---
 nsp_2 | 1
(1 row)
postgres=# -- 在schema public下创建表t2
postgres=# CREATE TABLE public.t2(a text, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
postgres=# -- public在search_path指明的schema之外，查询t2报错
postgres=# SELECT * FROM t2;
ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^
postgres=#

5. USER

  用户是集群业务的所有者和执行者，角色不特定于某个单独的数据库，但是登录集群的时候必须要显式指定登录的用户名，以保证当前连接执行的操作者的透明性。同时数据库也会通过权限管理限定用户的访问和操作权限。

6. 权限管理

  GaussDB(DWS)中的权限管理分成三种场景：

1) 系统权限

  系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。

2) 用户权限

  将一个用户的权限授予一个或多个其他用户。在这种情况下，每个用户都可视为拥有一个或多个数据库权限的集合。当声明了WITH ADMIN OPTION，被授权的用户可以将该权限再次授予其他角色或用户，以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。

3) 数据对象权限

  将数据库对象（表和视图、指定字段、数据库、函数、模式、表空间等）的相关权限授予特定角色或用户。
  a.  GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。
  b.  关键字PUBLIC表示该权限要赋予所有角色，包括以后创建的用户。PUBLIC可以看做是一个隐含定义好的组，它总是包括所有角色。任何角色或用户都将拥有通过GRANT直接赋予的权限和所属的权限，再加上PUBLIC的权限。
  c.  如果声明了WITH GRANT OPTION，则被授权的用户也可以将此权限赋予他人，否则就不能授权给他人。但是WITH GRANT OPTION这个选项不能赋予PUBLIC。

  用户是权限的最终体现者，所有的权限管理最终都体现在用户对数据库对象的操作权限是否被允许。
  需要特别指出的是：
  1. schema的owner默认拥有该schema下对象的所有权限，包括删除权限；database的owner默认拥有该database下对象的所有权限，包括删除权限。 因此建议对database和schema的创建要做比较严格的控制，一般建议使用管理员创建database和schema，然后把相关的权限控制赋给业务用户。

test=# -- 1. 超级用户把在database test下把创建schema的权限赋给普通用户dfm
test=# GRANT CREATE ON DATABASE test TO dfm;
GRANT
test=# -- 切换到用户dfm
test=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123';
SET
test=> -- 2. 普通用户dfm在database test下创建schema nsp_3
test=> CREATE SCHEMA nsp_3;
CREATE SCHEMA
test=> -- 切换回原来的用户
test=> RESET SESSION AUTHORIZATION;
RESET
test=# -- 3. 超级用户在schema nsp_3下创建表t1
test=# CREATE TABLE nsp_3.t1(a int, b int) DISTRIBUTE BY HASH(b);
CREATE TABLE
test=# -- 切换到用户dfm
test=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123';
SET
test=> -- 4. 普通用户dfm删除超级用户在schema nsp_3下创建的表t1;
test=> DROP TABLE nsp_3.t1;
DROP TABLE
test=>

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

postgres=# -- 把表nsp_1.t1的权限赋给用户dfm
postgres=# GRANT SELECT ON TABLE nsp_1.t1 TO dfm;
GRANT
postgres=# -- 切换到用户dfm
postgres=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123';
SET
postgres=> -- 查询表nsp_1.t1
postgres=> SELECT * FROM nsp_1.t1;
ERROR:  permission denied for schema nsp_1
LINE 1: SELECT * FROM nsp_1.t1;
                      ^
postgres=> -- 切换回原来的用户
postgres=> RESET SESSION AUTHORIZATION;
RESET
postgres=# -- 把schema nsp_1的权限赋给用户dfm
postgres=# GRANT USAGE ON SCHEMA nsp_1 TO dfm;
GRANT
postgres=# -- 切换到用户dfm
postgres=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123';
SET
postgres=> -- 查询表nsp_1.t1
postgres=> SELECT * FROM nsp_1.t1;
   a   | b
-------+---
 nsp_1 | 1
(1 row)

7. 业务隔离

  GaussDB(DWS)中可以使用database和Schema实现业务的隔离，区别在于database之间无法直接互访，通过连接隔离实现彻底的权限隔离。schema隔离的方式共用资源较多，可以通过grant与revoke语法便捷地控制不同用户对各schema及其下属对象的权限，从而赋给业务更多的灵活性。
  通常情况下我们建议使用schema进行业务隔离，除非我们新业务具有以下特征相对以前的业务：

• 新业务的数据源需要特殊的字符集编码或者排序规则，导致旧的database无法承载业务
• 相对以前的业务，新业务需要满足特殊的兼容性来降低业务迁移成本
• 安全上需要绝对的严格的隔离，这种场景一般需要结合pg_hba.conf配置文件进行会话级别的认证控制，只允许特定IP+特定用户访问这些数据库

8. 总结

  GaussDB(DWS)中通过DATABASE实现对业务的物理隔离、通过schema实现对数据库对象的逻辑隔离，通过权限管理最终实现对数据库对象的操作权限