前言
本文将介绍DWS基于RBAC（Role-Based Access Control，基于角色的访问控制）的数据库用户权限管理。 简单来说，一个用户有多个角色，每个角色有多个权限。 这样就构建了“用户-角色-权限”的授权模型。 在该模型中，用户和角色、角色和权限之间通常存在多对多的关系。
通过本节我们将学习DWS数据库权限管理的相关知识，进一步学习如何进行权限管理。
1 对象的基本概念
集群：集群是由一组服务器和其他可以实现高可用性的资源组成的单个系统。 在某些情况下，可以实现负载平衡和并行处理。
数据库：数据库是存储在一起的可以访问、管理和更新的相关数据的集合。 集群包含一个或多个命名数据库。
用户和角色：用户和角色在整个集群中共享，但其数据不共享。 即用户可以连接任意数据库，但连接成功后，任意用户只能访问连接请求中声明的数据库。
模式：一组数据库对象，包括表、视图、序列、存储过程、同义词、索引、簇和数据库链接等逻辑结构。
表：表由行和列组成。 每列都被视为一个字段。 每个字段中的值代表一种数据类型。
它们之间的关系如下：
集群内可以创建多个库，库之间物理隔离。 集群中的用户和角色是唯一且全局共享的。 对库的访问由用户控制。 同一个库中，schema是唯一的，不同schema下面可以创建同名的表。 表通过模式来区分。 通过权限控制隔离不同用户之间的数据访问。 不同用户之间表的访问权限由用户维护。 通过角色统一管理权限。 一个用户可以创建不同的schema来区分不同的业务模块，并可以通过不同的用户提供给不同的业务。
可以使用 CREATE USER 和 ALTER USER 创建和管理数据库用户。 数据库集群包含一个或多个命名数据库。 用户和角色在整个集群中共享，但其数据却不然。 即用户可以连接任意数据库，但连接成功后，任意用户只能访问连接请求中声明的数据库。
在没有权力分离的情况下，DWS用户帐户只能由具有CREATEROLE属性的系统管理员或安全管理员创建和删除。 当三权分离时，用户账户只能由初始用户和安全管理员创建。
用户登录DWS时进行身份验证。 用户可以拥有数据库和数据库对象（例如表），并且可以将这些对象的权限授予用户和角色，以控制谁可以访问哪些对象。 除了系统管理员之外，具有 CREATEDB 属性的用户也可以创建数据库并授予这些数据库的权限。
角色是用户的集合。 通过GRANT将角色授予用户后，该用户拥有该角色的所有权限。 建议使用角色来进行高效的权限分配。 例如，您可以为设计、开发和维护人员创建不同的角色。 向用户授予角色后设计包含用户,角色,权限和用户组数据库表结构，您可以向每个角色中的用户授予对其工作所需数据的不同权限。 在角色级别授予或撤销权限时，更改将应用于该角色下的所有成员。
模式也称为模式。 通过管理Schema，允许多个用户使用同一个数据库而不会互相干扰，数据库对象可以组织成易于管理的逻辑组，第三方应用程序可以方便地添加到相应的Schema中而不会造成冲突。
每个数据库包含一个或多个Schema。 数据库中的每个模式都包含表和其他类型的对象。 数据库最初创建时，默认有一个名为public的Schema，所有用户都拥有这个Schema的权限。 数据库对象可以按架构进行分组。 模式与操作系统目录类似，但模式不能嵌套。
相同的数据库对象名称可以应用在同一数据库的不同Schema中，不会发生冲突。 例如，a_schema 和 b_schema 都可以包含名为 mytable 的表。 具有所需权限的用户可以访问数据库的多个模式中的对象。
2 角色、用户和用户组
Ø 角色（ROLE）本质上是一组权限。 ROLE通常用来组织权限，用户用来管理权限和进行业务操作。
Ø 角色之间的权限可以继承，用户组中的所有用户都可以自动继承对应角色的权限。
Ø 数据库中USER和ROLE的关系是USER的权限来自ROLE。
Ø 用户组包含具有相同权限的用户的集合。
Ø 用户可以视为具有登录权限的角色。
Ø 角色可以视为没有登录权限的用户。
根据不同业务场景的需求，管理员通过“控制平面”创建和管理不同的用户组。 用户组通过角色绑定获取操作权限。 用户加入用户组后，可以获得该用户组的操作权限。 用户组可以同时对用户进行分类，对多个用户进行统一管理。 最大支持5000个用户组（包括系统内置用户组）。
DWS提供的权限包括“控制平面”以及各个组件的运维权限。 在实际应用中，需要根据业务场景为每个用户配置不同的权限。 为了提高权限管理的易用性音效，“控制平面”引入了角色的功能。 通过选择指定的权限并统一授予角色，以权限集的形式实现权限的集中查看和管理。
这样，一方面对普通用户屏蔽了内部的权限管理细节，另一方面也简化了管理员的权限管理操作方法，提高了权限管理的易用性和用户体验。
集中权限管理中权限、角色和用户之间的关系如图1所示。
图1 权限管理与用户关联示意图
DWS提供了多种权限。 您可以根据业务场景的实际需要，选择为不同的角色授予特定的权限。 一种或多种权限可以对应于一种角色。
l 角色A：授予操作权限A和B，用户A和用户B通过分配角色A获得相应的权限。
l 角色B：授予C操作权限，用户C通过分配角色B获得相应的权限。
l 角色C：授予操作权限D、F，用户C通过分配角色C获得相应的权限。
通过GRANT将角色授予用户后，该用户拥有该角色的所有权限。 建议使用角色来进行高效的权限分配。 它只对自己的表有所有权限，对其他用户在各自模式下放置的表没有权限。
3 基于角色的权限管理模型
Ø 系统权限：
系统权限：系统规定用户使用数据库的权限
对象权限：对数据库对象（例如表、序列、函数等）执行特殊操作的权限。
Ø 借助角色机制：
当对一组用户进行相同权限授权时，无需对这些用户进行一一授权。
通过为用户分配角色，该用户拥有该角色的所有权限。
一个用户可以属于不同的角色，并拥有不同的角色权限。
Ø 正常模式下：
数据库管理员和业务用户（只读用户、只写用户、读写用户）
p 在角色机制下，一个角色被视为一个数据库用户或一组数据库用户。
p 数据库用户的主要目的是连接数据库、访问数据库对象和执行SQL语句。
p ROLE 通常用于组织权限，users 用于实际的用户操作。
p 角色之间的权限可以继承，用户组中的所有用户自动继承角色的权限。
4 权限类型和对象列表
4.1 角色权限和对象权限
Ø 角色是权限的集合，它限制用户的行为
Ø 通过为用户分配角色来限制用户权限范围
Ø 使用角色管理权限更有效
Ø 使用角色来管理所有用户权限，使其更加统一
Ø 角色可派生（实现资源管理和控制，两级层次结构）
Ø 角色的对象权限集可以继承，但系统权限不能继承。
4.2 涉及权限的数据库对象
数据库（DATABASE）、用户（USER）、模式（SCHEMA）、表（TABLE）、函数（FUNCTION）、表空间（TABLESPACE）、类型（TYPE）、角色（ROLE）
4.3 查看对象权限
通过系统表字段查看对象权限变化：
1、用户mytbl1是用户along1拥有的表，表along2对mytbl1有select(r)查询权限。
#select relname,relacl from pg_class where relname = 'mytbl1';
别名 | 重新
------+---------------------------------------- -
我的tbl1 | {along1=arwdDxt/along1,along2=r/along1}
2. 将mytbl1的插入权限授予ong2。
# 授予沿1.mytbl1 上的插入到沿2 的权限；
3. 检查用户long2是否已经拥有mytbl1的insert(a)权限。
# 从 pg_class 中选择 relname,relacl 其中 relname = 'mytbl1';
别名 | 重新
------+---------------------------------------- --
我的tbl1 | {along1=arwdDxt/along1,along2=ar/along1
5 权限管理
5.1 权限机制
数据库对象创建后，创建该对象的用户就是该对象的所有者。 集群安装后默认不启用分权，数据库系统管理员与对象所有者拥有相同的权限。 也就是说，对象创建后，默认只有对象所有者或系统管理员才能查询、修改、销毁该对象地图场景，并通过GRANT将该对象的权限授予其他用户。
要使其他用户能够使用某个对象，必须向该用户或包含该用户的角色授予必要的权限。
DWS 支持以下权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE 和 USAGE。 不同的权限与不同的对象类型相关联。 可以使用GRANT。
要撤销已授予的权限，请使用 REVOKE。 对象所有者的权限（例如 ALTER、DROP、GRANT 和 REVOKE）是隐式的，无法授予或撤销。 也就是说，只要你拥有该对象，就可以执行对象所有者的这些隐式权限。 对象所有者可以撤销他或她的正常权限，例如，使该表对她自己和其他人只读。
系统表和系统视图仅对系统管理员或所有用户可见。 识别需要系统管理员权限的系统表和视图，只有系统管理员才能查询它们。
5.2 系统管理员
系统管理员是指具有SYSADMIN属性的帐户。 集群安装完成后，系统管理员默认拥有与对象所有者相同的权限。
集群安装时自动生成的账户称为初始用户。 初始用户也是系统管理员，拥有系统的最高权限，可以执行所有操作。 该帐户与执行集群安装的操作系统用户 omm 同名。
初始用户绕过所有权限检查。 建议该初始用户仅用于 DBA 管理目的，而不用于业务应用程序。
5.3 系统权限和对象权限
权限表示是否允许用户访问某个数据库对象。 数据库对象包括表、函数、模式、序列等。操作包括：创建、添加、删除、修改、查询等。权限是用户执行某个功能的权限。 在DWS中，根据系统管理方式的不同，权限可以分为两类：系统权限和对象权限。
5.3.1 系统权限
对数据库系统执行特定操作的能力。 系统权限也称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。
系统权限一般通过CREATE/ALTER ROLE语法指定。 其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE来授予或撤销。 但系统权限不能通过ROLE和USER的权限继承，也不能授予PUBLIC。
5.3.2 对象权限
操作数据库对象的能力，如SELECT、INSERT、UPDATE、DELETE等。
对象权限可以由对象所有者或管理员通过 GRANT/REVOKE 向其他角色分配或撤销。
5.4 授权操作
对象权限管理主要通过GRANT/REVOKE授予或撤销用户/角色对对象的权限。 公共特征赋予所有角色权力。
权限操作示例：
示例1：向用户或角色授予系统权限。
创建一个名为 joe 的用户并授予他 sysadmin 权限。
创建用户 joe 密码 'Bigdata123@';
授予乔所有特权；
授权成功后，用户joe将拥有sysadmin的所有权限。
示例2：向用户或角色授予对象权限。
1. 撤销joe用户的sysadmin权限，然后将模式tpcds的使用权限以及tpcds.reason表的所有权限授予joe用户。
注意：将Schema中的表或视图对象授权给其他用户或角色时，需要同时授予该用户或角色所属的Schema的USAGE权限。 否则，用户或角色将只能看到这些对象的名称，而无法实际访问这些对象。
撤销乔的所有特权；
将 SCHEMA tpcd 的使用权授予 joe；
将 tpcds.reason 上的所有权限授予 joe；
授权成功后，用户joe拥有tpcds.reason表的所有权限，包括添加、删除、修改、查询权限。
2. 授予joe对tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限和r_reason_desc的更新权限。
将 tpcds.reason 上的选择 (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) 授予 joe；
授权成功后，用户joe对tpcds.reason表中r_reason_sk和r_reason_id的查询权限立即生效。 3. 如果用户joe需要有权限将这些权限授权给其他用户，可以通过以下语法对用户joe进行授权。
使用 GRANT OPTION 将 select (r_reason_sk, r_reason_id) ON tpcds.reason 授予 joe；
4、将数据库postgres的连接权限授予用户joe，并赋予他在postgres中创建schema的权限，并允许joe将该权限授予其他用户。
使用 GRANT OPTION 授予创建、连接数据库 postgres 到 joe 的权限；
5. 创建角色tpcds_manager，将mode tpcds的访问权限授予角色tpcds_manager，并授予角色创建tpcds下对象的权限。 担任此角色的用户不得向其他人授予权限。
创建角色 tpcds_manager 密码 'Bigdata123@';
授予使用权，在架构 tpcds 上创建到 tpcds_manager；
6、将表空间tpcds_tbspc的所有权限授予用户joe，但用户joe不能继续向其他用户授予权限。
创建表空间 tpcds_tbspc 相对位置 'tablespace/tablespace_1';
将表空间 tpcds_tbspc 上的所有内容授予 joe；
示例3：将一个用户或角色的权限授予其他用户或角色。
1、创建角色管理者，将joe的权限授予管理者，并允许该角色向其他角色授予权限。
创建角色管理器密码“Bigdata123@”；
将 joe 授予具有管理选项的经理；
2. 创建用户senior_manager设计包含用户,角色,权限和用户组数据库表结构，并授予用户管理员权限。
创建角色 Senior_manager 密码 'Bigdata123@';
将经理授予高级经理；
3.撤销权限并清除用户。
撤销乔的经理；
从经理中撤销高级经理；
删除用户管理器；
5.5 权限规划
5.5.1 系统最小授权规划原则
5.5.2 授予许可的建议
1) 尽量减少授予权限。 只需要SELECT权限的，不需要授予其他权限。
2) 不要仅仅为了方便而授予 ALL PRIVILEGES 权限。
3) 在授予可能更改表内容的操作（更新、插入）权限时要小心。
4）管理好权限周期，时间到期时使用REVOKE回收权限。
6 业务用户权限示例
6.1 业务运营权限
第一步：先创建一个角色
创建角色data_mgr密码'Gauss_234';
步骤2：登录schema所属用户u2，授予角色data_mgr对schema s2的使用权限和所有表的数据操作权限。
Ø 授予角色data_mgr使用s2的权限
将架构 s2 上的 USAGE、CREATE 授予 data_mgr；
Ø 角色data_mgr授予对u2创建的表的查询权限
将模式 s2 中所有表的 SELECT、插入、删除、更新授予 data_mgr；
Ø 角色data_mgr对u2之后创建的新表授予查询权限
更改模式 s2 中的默认权限，将表上的 SELECT、插入、删除、更新授予 data_mgr；
6.2 只读操作权限
步骤一：创建只读角色
创建角色只读密码'Gauss_234';
步骤2：登录schema所属用户u2，授予角色read_only使用schema s1和s2的权限以及对所有表的查询权限。
Ø 授予角色read_only使用s1和s2的权限
将模式 s1,s2 上的 USAGE 授予 read_only；
Ø --角色read_only对s1和s2创建的表有查询权限
将模式 s1,s2 中所有表的 SELECT 权限授予 read_only；
Ø 角色read_only对s1和s2之后创建的新表有查询权限。
更改模式 s1、s2 中的默认权限，将表上的 SELECT 授予只读权限；
6.3 授权操作
Ø 授予用户u1对用户u2的schema s2的数据操作权限。
将 data_mgr 授予 u1；
Ø 授予用户u3对用户u2的schema s1和s2的只读权限。
授予 u3 只读权限；
6.4 权限恢复操作
6.4.1 角色恢复
Ø 将角色data_mgr的使用权限恢复到s2
从 data_mgr 撤销模式 s2 上的 USAGE、CREATE；
Ø 恢复角色data_mgr对u2创建的表的查询权限
从 data_mgr 撤销对模式 s2 中所有表的 SELECT、插入、删除、更新；
Ø 回收角色data_mgr，并对u2后续创建的新表授予查询权限。
更改模式 s2 中的默认权限，撤销 data_mgr 表上的 SELECT、插入、删除、更新；
6.4.2 用户权限恢复
Ø 恢复用户u1对u2的schema s2的数据操作权限
撤销u1的data_mgr；
Ø 恢复用户u3对u2的schema s1和s2的只读权限
从 u3 撤销 read_only；
6.5 赋能与回收
要将当前用户下某个schema下的表的查询权限授予其他用户，不仅需要将该schema的使用权限授予其他用户，还需要授予其他用户对该表的查询权限（可以指定具体表）授权查询操作，也可以一次性授予所有表的查询权限）。
如果以后想授予对新表的查询权限，需要通过alter default权限授予默认权限。
恢复权限需要恢复schema的使用权限、表的查询权限以及默认权限。
您可以通过\ddp查看默认的授权信息。
7 总结
通过本文，您可以系统地了解DWS数据库权限管理相关知识，从而为数据库管理和业务开发提供技术支持。 通过更有效、更细致的权限管理，我们将制定完善的数据安全机制，确保数据安全。
点击关注，第一时间了解华为云新技术~
