华为云用户手册

数据加载和卸载 【建议】在insert语句中显式给出插入的字段列表。例如： 1 INSERT INTO task(name,id,comment) VALUES ('task1','100','第100个任务'); 【建议】在批量数据入库之后，或者数据增量达到一定阈值后，建议对表进行analyze操作，防止统计信息不准确而导致的执行计划劣化。 【建议】如果要清理表中的所有数据，建议使用truncate table方式，不要使用delete table方式。delete table方式删除性能差，且不会释放那些已经删除了的数据占用的磁盘空间。

DDL 【建议】在 GaussDB (DWS)中，建议DDL（建表、comments等）操作统一执行，在批处理作业中尽量避免DDL操作。避免大量并发事务对性能的影响。 【建议】在非日志表（unlogged table）使用完后，立即执行数据清理（truncate）操作。因为在异常场景下，GaussDB(DWS)不保证非日志表(unlogged table)数据的安全性。 【建议】临时表和非日志表的存储方式建议和基表相同。当基表为行存（列存）表时，临时表和非日志表也推荐创建为行存（列存）表，可以避免行列混合关联带来的高计算代价。 【建议】索引字段的总长度不超过50字节。否则，索引大小会膨胀比较严重，带来较大的存储开销，同时索引性能也会下降。 【建议】不要使用DROP…CASCADE方式删除对象，除非已经明确对象间的依赖关系，以免误删。

连接参数 【关注】第三方工具通过JDBC连接GaussDB(DWS)时，JDBC向GaussDB(DWS)发起连接请求，会默认添加以下配置参数，详见JDBC代码ConnectionFactoryImpl类的实现。 params = { { "user", user }, { "database", database }, { "client_encoding", "UTF8" }, { "DateStyle", "ISO" }, { "extra_float_digits", "2" }, { "TimeZone", createPostgresTimeZone() }, }; 这些参数可能会导致JDBC客户端的行为与gsql客户端的行为不一致，例如，Date数据显示方式、浮点数精度表示、timezone显示。 如果实际期望和这些配置不符，建议在java连接设置代码中显式设定这些参数。 【建议】通过JDBC连接数据库时，应该保证下面两个时区设置一致： JDBC客户端所在主机的时区。 GaussDB(DWS)集群所在主机的时区。

释放连接 【建议】推荐使用连接池限制应用程序的连接数。每执行一条SQL就连接一次数据库，是一种不好SQL的编写习惯。 【建议】在应用程序完成作业任务之后，应当及时断开和GaussDB(DWS)的连接，释放资源。建议在任务中设置session超时时间参数。 【建议】使用JDBC连接池，在将连接释放给连接池前，需要执行以下操作重置会话环境。否则，可能会因为历史会话信息导致的对象冲突。 如果在连接中设置了GUC参数，那么在将连接归还连接池之前，必须执行“SET SESSION AUTHORIZATION DEFAULT;RESET ALL;”将连接的状态清空。 如果使用了临时表，那么在将连接归还连接池之前，必须将临时表删除。

选择数据类型 在字段设计时，基于查询效率的考虑，一般遵循以下原则： 【建议】尽量使用高效数据类型。 选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数、NUMERIC。 【建议】当多个表存在逻辑关系时，表示同一含义的字段应该使用相同的数据类型。 【建议】对于字符串数据，建议使用变长字符串数据类型，并指定最大长度。请务必确保指定的最大长度大于需要存储的最大字符数，避免超出最大长度时出现字符截断现象。除非明确知道数据类型为固定长度字符串，否则，不建议使用CHAR(n)、BPCHAR(n)、NCHAR(n)、CHARACTER(n)。 关于字符串类型的详细说明，请参见常用字符串类型介绍。

选择分布键 Hash表的分布键选取至关重要，如果分布键选择不当，可能会导致数据倾斜，从而导致查询时，I/O负载集中在部分DN上，影响整体查询性能。因此，在确定Hash表的分布策略之后，需要对表数据进行倾斜性检查，以确保数据的均匀分布。分布键的选择一般需要遵循以下原则： 【建议】选作分布键的字段取值应该比较离散，以便数据能在各个DN上均匀分布。当单个字段无法满足离散条件时，可以考虑使用多个字段一起作为分布键。一般情况下，可以考虑选择表的主键作为分布键。例如，在人员信息表中选择证件号码作为分布键。 【建议】在满足第一条原则的情况下，尽量不要选取在查询中存在常量过滤条件的字段作为分布键。例如，在表dwcjk相关的查询中，字段zqdh存在常量过滤条件“zqdh='000001'”，那么就应当尽量不选择zqdh字段作为分布键。 【建议】在满足前两条原则的情况，尽量选择查询中的关联条件为分布键。当关联条件作为分布键时，Join任务的相关数据都分布在DN本地，将极大减少DN之间的数据流动代价。

选择分区方案 当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则： 【建议】使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。 【建议】分区名称应当体现分区的数据特征。例如，关键字+区间特征。 【建议】将分区上边界的分区值定义为MAXVALUE，以防止可能出现的数据溢出。 典型的分区表定义如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 CREATE TABLE staffS_p1 ( staff_ID NUMBER(6) not null, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(4,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) PARTITION BY RANGE (HIRE_DATE) ( PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'), PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'), PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE) );

选择分布方案 【建议】表的分布方式的选择一般遵循以下原则： 表2 表的分布方式及使用场景 分布方式 描述 适用场景 Hash 表数据通过Hash方式散列到集群中的所有DN上。 数据量较大的事实表。 Replication 集群中每一个DN都有一份全量表数据。 维度表、数据量较小的事实表。 Roundrobin 表的每一行被轮番地发送给各个DN，因此数据会被均匀地分布在各个DN中。 数据量较大的事实表，且使用Hash分布时找不到合适的分布列。

Database设计建议 【建议】在实际业务中，根据需要创建新的Database，不建议直接使用集群默认的gaussdb数据库。 【建议】一个集群内，用户自定义的Database数量建议不超过3个。 【建议】为了适应全球化的需求，使数据库编码能够存储与表示绝大多数的字符，建议创建Database的时候使用UTF-8编码。 【关注】创建Database时，需要重点关注字符集编码(ENCODING)和兼容性(DBCOMPATIBILITY)两个配置项。GaussDB(DWS)支持Oracle、Teradata和MySQL三种兼容模式，分别兼容Oracle、Teradata和MySQL语法，不同兼容模式下的语法行为可能有一些差异。详细内容可参考Oracle、Teradata和MySQL语法兼容性差异。 【关注】Database的owner默认拥有该Database下所有对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。

Schema设计建议 【关注】如果该用户不具有sysadmin权限或者不是该Schema的owner，要访问Schema下的对象，需要同时给用户赋予Schema的usage权限和对象的相应权限。 【关注】如果要在Schema下创建对象，需要授予操作用户该Schema的CREATE权限。 【关注】Schema的owner默认拥有该Schema下对象的所有权限，包括删除权限。删除权限影响较大，请谨慎使用。

数据库对象命名 数据库对象命名需要满足约束：长度不超过63个字符，以字母或下划线开头，中间字符可以是字母、数字、下划线、$、#。 【建议】避免使用保留或者非保留关键字命名数据库对象。 可以使用SELECT * FROM pg_get_keywords()查询GaussDB(DWS)的关键字，或者在《SQL语法参考》中“关键字”章节中查看。 【建议】避免使用双引号括起来的字符串来定义数据库对象名称，GaussDB(DWS)中使用双引号将数据库对象名称括起来时表示对大小写敏感。数据库对象名称大小写敏感会使定位问题难度增加。 【建议】数据库对象命名风格务必保持统一。 增量开发的业务系统或进行业务迁移的系统，建议遵守历史的命名风格。 数据库对象名称由字母、数字和下划线组成，并且不能由数字开头。建议使用多个单词组成，以下划线分割。 数据库对象名称最好能够望文知意，尽量避免使用自定义缩写（可以使用通用的术语缩写进行命名）。例如，在命名中可以使用具有实际业务含义的英文词汇或汉语拼音，但规则应该在集群范围内保持一致。 变量名的关键是要具有描述性，即变量名称要有一定的意义，变量名要有前缀标明该变量的类型。 【建议】表对象的命名应该可以表征该表的重要特征。例如，在表对象命名时区分该表是普通表、临时表还是非日志表： 普通表名按照数据集的业务含义命名。 临时表以“tmp_+后缀”命名。 非日志表以“ul_+后缀”命名。 外表以“f_+后缀”命名。 父主题： 开发设计建议

开发设计建议概述 本开发设计建议约定数据库建模和数据库应用程序开发过程中，应当遵守的设计规范。依据这些规范进行建模，能够更好的契合GaussDB(DWS)的分布式处理架构，输出更高效的业务SQL代码。 本开发设计建议中所陈述的“建议”和“关注”含义如下： 建议：用户应当遵守的设计规则。遵守这些规则，能够保证业务的高效运行；违反这些规则，将导致业务性能的大幅下降或某些业务逻辑错误。 关注：在业务开发过程中客户需要注意的细则。用于标识容易导致客户理解错误的知识点（实际上遵守SQL标准的SQL行为），或者程序中潜在的客户不易感知的默认行为。 父主题： 开发设计建议

自定义密码策略示例 示例一：配置密码复杂度参数password_policy。 登录GaussDB(DWS) 管理控制台。 在左侧导航栏中，单击“集群管理”。 在集群列表中找到所需要的集群，单击集群名称，进入“集群详情”页面。 单击“参数修改”页签，并在“参数列表”模块修改password_policy参数值，然后单击“保存”。password_policy参数无需进行重启集群操作，参数修改后立即生效。 图1 password_policy

密码设置和修改 建议系统管理员和普通用户都要定期修改自己的账户密码，避免账户密码被非法窃取。 以修改用户user1密码为例，使用管理员用户连接数据库并执行如下命令： 1 ALTER USER user1 IDENTIFIED BY 'newpassword' REPLACE 'oldpassword'; 密码要符合规则，否则会执行失败。 管理员可以修改自己的或者其他账户的密码。通过修改其他账户的密码，解决用户密码遗失所造成无法登录的问题。 以修改用户joe账户密码为例，命令格式如下： 1 ALTER USER joe IDENTIFIED BY 'password'; 系统管理员之间不允许互相修改对方密码。 系统管理员可以修改普通用户密码且不需要用户原密码。 系统管理员可以修改自己的密码但需要管理员原密码。 密码验证 设置当前会话的用户和角色时，需要验证密码。如果输入密码与用户的存储密码不一致，则会报错。 以设置用户joe为例，命令格式如下： 1 SET ROLE joe PASSWORD 'password'; 显示如下命令表示设置成功： SET ROLE

示例 以员工表emp，表的属主alice及角色matu、july为例，简要介绍数据脱敏过程。其中，表emp包含员工的姓名、手机号、邮箱、银行卡号、薪资等隐私数据 使用管理员用户连接数据库后，创建角色alice、matu和july。 1 2 3 CREATE ROLE alice PASSWORD 'password'; CREATE ROLE matu PASSWORD 'password'; CREATE ROLE july PASSWORD 'password'; 赋予alice、matu和july当前数据库的模式权限。 1 GRANT ALL PRIVILEGES on schema public to alice,matu,july; 切换至角色alice，创建表emp并插入三条员工信息。 1 2 3 4 5 6 7 SET ROLE alice PASSWORD 'password'; CREATE TABLE emp(id int, name varchar(20), phone_no varchar(11), card_no number, card_string varchar(19), email text, salary numeric(100, 4), birthday date); INSERT INTO emp VALUES(1, 'anny', '13420002340', 1234123412341234, '1234-1234-1234-1234', 'smithWu@163.com', 10000.00, '1999-10-02'); INSERT INTO emp VALUES(2, 'bob', '18299023211', 3456345634563456, '3456-3456-3456-3456', '66allen_mm@qq.com', 9999.99, '1989-12-12'); INSERT INTO emp VALUES(3, 'cici', '15512231233', NULL, NULL, 'jonesishere@sina.com', NULL, '1992-11-06'); alice将表emp的读取权限授予matu、july。 1 GRANT SELECT ON emp TO matu, july; 创建脱敏策略mask_emp，仅alice可查看员工所有信息，matu和july对员工银行卡号和薪资数据不可见。字段card_no是数值类型，采用MASK_FULL全脱敏成固定值0；字段card_string是字符类型，采用MASK_PARTIAL按指定的输入输出格式对原始数据进行部分脱敏；字段salary是数值类型，采用MASK_PARTIAL指定数字9部分脱敏倒数第二位前的所有数位值。 1 2 3 4 CREATE REDACTION POLICY mask_emp ON emp WHEN (current_user IN ('matu', 'july')) ADD COLUMN card_no WITH mask_full(card_no), ADD COLUMN card_string WITH mask_partial(card_string, 'VVVVFVVVVFVVVVFVVVV','VVVV-VVVV-VVVV-VVVV','#',1,12), ADD COLUMN salary WITH mask_partial(salary, '9', 1, length(salary) - 2); 切换到matu和july，查看员工表emp。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SET ROLE matu PASSWORD 'password'; SELECT * FROM emp; id | name | phone_no | card_no | card_string | email | salary | birthday ----+------+-------------+---------+---------------------+----------------------+------------+--------------------- 1 | anny | 13420002340 | 0 | ####-####-####-1234 | smithWu@163.com | 99999.9990 | 1999-10-02 00:00:00 2 | bob | 18299023211 | 0 | ####-####-####-3456 | 66allen_mm@qq.com | 9999.9990 | 1989-12-12 00:00:00 3 | cici | 15512231233 | | | jonesishere@sina.com | | 1992-11-06 00:00:00 (3 rows) SET ROLE july PASSWORD 'password'; SELECT * FROM emp; id | name | phone_no | card_no | card_string | email | salary | birthday ----+------+-------------+---------+---------------------+----------------------+------------+--------------------- 1 | anny | 13420002340 | 0 | ####-####-####-1234 | smithWu@163.com | 99999.9990 | 1999-10-02 00:00:00 2 | bob | 18299023211 | 0 | ####-####-####-3456 | 66allen_mm@qq.com | 9999.9990 | 1989-12-12 00:00:00 3 | cici | 15512231233 | | | jonesishere@sina.com | | 1992-11-06 00:00:00 (3 rows) 若需要matu也有员工所有信息的查看权限，只有july不可见，修改策略生效范围即可。 1 2 SET ROLE alice PASSWORD 'password'; ALTER REDACTION POLICY mask_emp ON emp WHEN(current_user = 'july'); 切换到matu和july，重新查看员工表emp。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SET ROLE matu PASSWORD 'password'; SELECT * FROM emp; id | name | phone_no | card_no | card_string | email | salary | birthday ----+------+-------------+------------------+---------------------+----------------------+------------+--------------------- 1 | anny | 13420002340 | 1234123412341234 | 1234-1234-1234-1234 | smithWu@163.com | 10000.0000 | 1999-10-02 00:00:00 2 | bob | 18299023211 | 3456345634563456 | 3456-3456-3456-3456 | 66allen_mm@qq.com | 9999.9900 | 1989-12-12 00:00:00 3 | cici | 15512231233 | | | jonesishere@sina.com | | 1992-11-06 00:00:00 (3 rows) SET ROLE july PASSWORD 'password'; SELECT * FROM emp; id | name | phone_no | card_no | card_string | email | salary | birthday ----+------+-------------+---------+---------------------+----------------------+------------+--------------------- 1 | anny | 13420002340 | 0 | ####-####-####-1234 | smithWu@163.com | 99999.9990 | 1999-10-02 00:00:00 2 | bob | 18299023211 | 0 | ####-####-####-3456 | 66allen_mm@qq.com | 9999.9990 | 1989-12-12 00:00:00 3 | cici | 15512231233 | | | jonesishere@sina.com | | 1992-11-06 00:00:00 (3 rows) 员工信息phone_no、email和birthday也是隐私数据，更新脱敏策略mask_emp，新增三个脱敏列。 1 2 3 4 SET ROLE alice PASSWORD 'password'; ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN phone_no WITH mask_partial(phone_no, '*', 4); ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN email WITH mask_partial(email, '*', 1, position('@' in email)); ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN birthday WITH mask_full(birthday); 切换到july，查看表emp数据。 1 2 3 4 5 6 7 8 SET ROLE july PASSWORD 'password'; SELECT * FROM emp; id | name | phone_no | card_no | card_string | email | salary | birthday ----+------+-------------+---------+---------------------+----------------------+------------+--------------------- 1 | anny | 134******** | 0 | ####-####-####-1234 | ********163.com | 99999.9990 | 1970-01-01 00:00:00 2 | bob | 182******** | 0 | ####-####-####-3456 | ***********qq.com | 9999.9990 | 1970-01-01 00:00:00 3 | cici | 155******** | | | ************sina.com | | 1970-01-01 00:00:00 (3 rows) 通过视图redaction_policies和redaction_columns查看当前脱敏策略mask_emp的详细信息。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT * FROM redaction_policies; object_schema | object_owner | object_name | policy_name | expression | enable | policy_description | inherited ---------------+--------------+-------------+-------------+-----------------------------------+--------+--------------------+----------- public | alice | emp | mask_emp | ("current_user"() = 'july'::name) | t | | f (1 row) SELECT object_name, column_name, function_info FROM redaction_columns; object_name | column_name | function_info -------------+-------------+------------------------------------------------------------------------------------------------------- emp | card_no | mask_full(card_no) emp | card_string | mask_partial(card_string, 'VVVVFVVVVFVVVVFVVVV'::text, 'VVVV-VVVV-VVVV-VVVV'::text, '#'::text, 1, 12) emp | email | mask_partial(email, '*'::text, 1, "position"(email, '@'::text)) emp | salary | mask_partial(salary, '9'::text, 1, (length((salary)::text) - 2)) emp | birthday | mask_full(birthday) emp | phone_no | mask_partial(phone_no, '*'::text, 4) (6 rows) 新增一列salary_info，若需要将文本类型的薪资信息统一脱敏成“*.*”，可以创建自定义脱敏函数实现。此处采用PL/PGSQL语言定义脱敏函数mask_regexp_salary，创建脱敏列时，只需自定义脱敏的函数名和参数列表，详细内容可参考用户自定义函数。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SET ROLE alice PASSWORD 'password'; ALTER TABLE emp ADD COLUMN salary_info TEXT; UPDATE emp SET salary_info = salary::text; CREATE FUNCTION mask_regexp_salary(salary_info text) RETURNS text AS $$ SELECT regexp_replace($1, '[0-9]+','*','g'); $$ LANGUAGE SQL STRICT SHIPPABLE; ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN salary_info WITH mask_regexp_salary(salary_info); SET ROLE july PASSWORD 'password'; SELECT id, name, salary_info FROM emp; id | name | salary_info ----+------+------------- 1 | anny | *.* 2 | bob | *.* 3 | cici | (3 rows) 无需为表emp设置敏感策略，删除脱敏策略mask_emp。 1 2 SET ROLE alice PASSWORD 'password'; DROP REDACTION POLICY mask_emp ON emp;

锁定用户 ALTER USER语句中ACCOUNT LOCK | ACCOUNT UNLOCK参数用于锁定或者解锁用户，被锁定的用户不允许登录。若管理员发现某账户被盗、非法访问等异常情况，可手动锁定该账户；当管理员认为账户恢复正常后，可手动解锁该账户。 示例： 锁定用户u1： 1 ALTER USER u1 ACCOUNT LOCK; 解锁用户u1： 1 ALTER USER u1 ACCOUNT UNLOCK;

删除用户 DROP USER语句用于删除一个或多个GaussDB(DWS)用户。当确认账户不再使用，管理员可以删除用户账户。用户删除后不可恢复。 同时删除多个用户时，用","隔开。 成功删除用户后，该用户的所有权限也会被一同删除。 当删除的用户正处于活动状态时，此会话状态不会立马断开，用户在会话状态断开后才会被完全删除。 DROP USER语句指定CASCADE时，可级联删除依赖用户的表等对象。即删除owner是该用户的对象，并清理掉其他对象对该用户的授权信息。 示例： 删除用户u1： 1 DROP USER u1; 级联删除账户u2： 1 DROP USER u2 CASCADE;

创建用户 CREATE USER语句用于创建新的GaussDB(DWS)用户。创建新用户后，可以使用该用户连接数据库。 创建普通用户u1，并设置用户拥有CREATEDB属性。 1 CREATE USER u1 WITH CREATEDB PASSWORD '{Password}'; 创建系统管理员mydbadmin，需指定参数SYSADMIN 。 1 CREATE USER mydbadmin sysadmin PASSWORD '{Password}'; 通过视图PG_USER查看已创建的用户。 1 SELECT * FROM pg_user; 要查看用户属性，请查询系统表PG_AUTHID。 1 SELECT * FROM pg_authid;

三权分立 默认情况下拥有SYSADMIN属性的系统管理员，具备系统最高权限。在实际业务管理中，为了避免系统管理员拥有过度集中的权利带来高风险，可以设置三权分立，将系统管理员的权限分立给安全管理员和审计管理员。 三权分立后，系统管理员将不再具有CREATEROLE属性（安全管理员）和AUDITADMIN属性（审计管理员）能力。即不再拥有创建角色和用户的权限，并不再拥有查看和维护数据库审计日志的权限。关于CREATEROLE属性和AUDITADMIN属性的更多信息请参考CREATE ROLE。 三权分立后，系统管理员只会对自己作为所有者的对象有权限。 三权分立的设置办法请参考设置GaussDB(DWS)集群三权分立章节。 三权分立前的权限详情及三权分立后的权限变化，请分别参见表1和表2。 表1 默认的用户权限 对象名称 系统管理员 安全管理员 审计管理员 普通用户 表空间 对表空间有创建、修改、删除、访问、分配操作的权限。 不具有对表空间进行创建、修改、删除、分配的权限，访问需要被赋权。 表 对所有表有所有的权限。 仅对自己的表有所有的权限，对其他用户的表无权限。 索引 可以在所有的表上建立索引。 仅可以在自己的表上建立索引。 模式 对所有模式有所有的权限。 仅对自己的模式有所有的权限，对其他用户的模式无权限。 函数 对所有的函数有所有的权限。 仅对自己的函数有所有的权限，对其他用户放在public这个公共模式下的函数有调用的权限，对其他用户放在其他模式下的函数无权限。 自定义视图 对所有的视图有所有的权限。 仅对自己的视图有所有的权限，对其他用户的视图无权限。 系统表和系统视图 可以查看所有系统表和视图。 只可以查看部分系统表和视图。详细请参见系统表和系统视图。 表2 三权分立较非三权分立权限变化说明 对象名称 系统管理员 安全管理员 审计管理员 普通用户 表空间 无变化 无变化。 表 权限缩小。 只对自己的表有所有权限，对其他用户放在属于各自模式下的表无权限。 无变化。 索引 权限缩小。 只可以在自己的表上建立索引。 无变化。 模式 权限缩小。 只对自己的模式有所有的权限，对其他用户的模式无权限。 无变化。 函数 权限缩小。 只对自己的函数有所有的权限，对其他用户放在属于各自模式下的函数无权限。 无变化。 自定义视图 权限缩小。 只对自己的视图及其他用户放在public模式下的视图有所有的权限，对其他用户放在属于各自模式下的视图无权限。 无变化。 系统表和系统视图 无变化。 无变化。 无变化。 无权查看任何系统表和视图。 父主题： 管理用户及权限

数据库用户类型 表1 数据库用户类型 用户类型 描述 可进行的操作 如何创建 管理员dbadmin 管理员也称作系统管理员，是指具有SYSADMIN属性的账户。 非三权分立模式下，拥有系统的最高权限，能够执行所有的操作。系统管理员具有与对象所有者相同的权限。 在GaussDB(DWS) 管理控制台创建集群时创建的用户dbadmin是系统管理员。 使用CREATE USER或ALTER USER语法创建和设置管理员用户。 CREATE USER sysadmin WITH SYSADMIN password '{Password}'; ALTER USER u1 SYSADMIN; 普通用户 普通用户 使用工具连接数据库。 拥有数据库系统特定操作的属性，如CREATEDB、CREATEROLE、SYSADMIN。 访问数据库对象。 执行SQL语句。 使用CREATE USER语法创建普通用户。 CREATE USER u1 PASSWORD '{Password}'; 私有用户 在非三权分立模式下，创建的具有INDEPENDENT属性的私有用户。 数据库管理员在未经其授权前，只能进行控制操作（DROP、ALTER、TRUNCATE），无权进行INSERT、DELETE、SELECT、UPDATE、COPY、GRANT、REVOKE、ALTER OWNER操作。 使用CREATE USER语法创建私有用户。 CREATE USER user_independent WITH INDEPENDENT IDENTIFIED BY '{Password}';

预置角色 GaussDB(DWS)提供了一组预置角色，以“gs_role_”开头命名，提供对特定的、通常需要高权限的操作的访问，可以将这些角色授权予数据库中的其他用户或角色，使这些用户能够访问或使用特定的信息和功能。请谨慎使用预置角色，以确保预置角色权限的安全使用。 预置角色允许的权限范围可参考下表： 表1 预置角色允许的权限范围 角色 权限描述 gs_role_signal_backend 具有调用函数pg_cancel_backend、pg_terminate_backend、pg_terminate_query、pg_cancel_query、pgxc_terminate_query、pgxc_cancel_query来取消或终止其他会话的权限，但不能操作属于初始用户的会话。 gs_role_read_all_stats 读取系统状态视图并且使用与扩展相关的各种统计信息，包括有些通常只对系统管理员可见的信息。包括： 资源管理类： pgxc_wlm_operator_history pgxc_wlm_operator_info pgxc_wlm_operator_statistics pgxc_wlm_session_info pgxc_wlm_session_statistics pgxc_wlm_workload_records pgxc_workload_sql_count pgxc_workload_sql_elapse_time pgxc_workload_transaction 状态信息类： pgxc_stat_activity pgxc_get_table_skewness table_distribution pgxc_total_memory_detail pgxc_os_run_info pg_nodes_memory pgxc_instance_time pgxc_redo_stat gs_role_analyze_any 具有系统级ANALYZE权限类似系统管理员用户，跳过schema权限检查，对所有的表可以执行ANALYZE。 gs_role_vacuum_any 具有系统级VACUUM权限类似系统管理员用户，跳过schema权限检查，对所有的表可以执行VACUUM。 gs_redaction_policy 具有创建、修改、删除脱敏策略的权限，对所有的表都可以执行CREATE | ALTER | DROP REDACTION POLICY。9.1.0及以上集群版本支持。 预置角色的使用约束： 以gs_role_开头的角色名作为数据库的预置角色保留字，禁止新建以“gs_role_”开头的用户/角色，也禁止将已有的用户/角色重命名为以“gs_role_”开头。 禁止对预置角色执行ALTER和DROP操作。 预置角色默认没有 LOG IN权限，不设置预置登录密码。 gsql元命令\du和\dg不显示预置角色的相关信息，但若指定了PATTERN（用来指定要被显示的对象名称）则预置角色信息会显示。 三权分立关闭时，系统管理员和具有预置角色ADMIN OPTION权限的用户有权对预置角色执行GRANT/REVOKE管理；三权分立打开时，安全管理员（具有CREATEROLE属性）和具有预置角色ADMIN OPTION权限的用户有权对预置角色执行GRANT/REVOKE管理。例如： 1 2 GRANT gs_role_signal_backend TO user1; REVOKE gs_role_signal_backend FROM user1;

权限授予或撤销 数据库对象创建后，进行对象创建的用户就是该对象的所有者。集群安装后的默认情况下，未开启三权分立，数据库系统管理员具有与对象所有者相同的权限。 也就是说对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和删除对象，以及通过GRANT将对象的权限授予其他用户。为使其他用户能够使用对象，可以由对象所有者或管理员通过GRANT/REVOKE对其他用户或角色授予与撤销。 使用GRANT语句授予权限。 例如，将模式myschema的权限赋给角色u1后，将表myschema.t1的SELECT权限授予角色u1。 1 2 GRANT USAGE ON SCHEMA myschema TO u1; GRANT SELECT ON TABLE myschema.t1 to u1; 使用REVOKE撤销已经授予的权限。 例如：撤销用户u1在指定表myschema.t1上的所有权限。 REVOKE ALL PRIVILEGES ON myschema.t1 FROM u1;

权限概述 权限表示用户访问某个数据库对象（包括模式、表、函数、序列等）的操作（包括增、删、改、查、创建等）是否被允许。 GaussDB(DWS)中的权限管理分为三种场景： 系统权限 系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。 系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。 数据对象权限 将数据库对象（表和视图、指定字段、数据库、函数、模式等）的相关权限授予特定角色或用户。GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。 用户权限 将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。 当声明了WITH ADMIN OPTION，被授权的用户可以将该权限再次授予其他角色或用户，以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。 数据库系统管理员可以给任何角色或用户授予/撤销任何权限。拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。

角色 GaussDB(DWS)的权限管理模型，是一种典型的RBAC（基于角色的权限控制）的实现。其将用户、角色、权限通过此模型管理起来。 角色是一组权限的集合。 “用户”概念和“角色”概念实际是等同的，唯一的区别在于“用户”拥有login权限，而“角色”拥有nologin权限。 按照数据库系统中承担的责任划分具有不同权限的角色。角色是数据库权限的集合，代表了一个数据库用户、或一组数据用户的行为约束。 角色和用户可以转换，通过ALTER将角色拥有登录权限。 通过GRANT把角色授予用户后，用户即具有了角色的所有权限。推荐使用角色进行高效权限分配。例如，可以为设计、开发和维护人员创建不同的角色，将角色GRANT给用户后，再向每个角色中的用户授予其所需数据的差异权限。在角色级别授予或撤销权限时，这些权限更改会对角色下的所有成员生效。 非三权分立时，只有系统管理员和具有CREATEROLE属性的用户才能创建、修改或删除角色。三权分立下，只有具有CREATEROLE属性的用户才能创建、修改或删除角色。 要查看所有角色，请查询系统表PG_ROLES： 1 SELECT * FROM PG_ROLES; 具体的创建，修改和删除角色操作，请参考SQL语法参考中CREARE ROLE/ALTER ROLE/DROP ROLE。

层级权限管理 GaussDB(DWS)通过Database、Schema和数据对象权限实现层级权限管理。 Database之间无法直接互访，通过连接隔离实现彻底的权限隔离。各个Database之间共享资源极少，可实现连接隔离、权限隔离等。数据库集群包含一个或多个已命名数据库。用户和角色在整个集群范围内是共享的，但是其数据并不共享。即用户可以连接任何数据库，但当连接成功后，任何用户都只能访问连接请求里所声明的数据库。 Schema隔离的方式共用资源较多，可以通过GRANT与REVOKE语法便捷地控制不同用户对各Schema及其下属对象的权限，从而赋给业务更多的灵活性。每个数据库包括一个或多个Schema。每个Schema包含表、函数等其他类型的对象。用户要访问包含在指定Schema中的对象，需要被授予Schema的USAGE权限。 对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和删除对象。其他用户要访问包含具体的数据库对象，例如table1，需要首先被授予database的CONNECT权限，再被授予Schema的USAGE权限，最后授予table1的SELECT权限。用户要访问底层的对象，必须先赋予上层对象的权限。比如用户要创建或者删除Schema，需要首先被授予database的CREATE权限； 图1 层级权限管理

定时任务管理 创建测试表： 1 CREATE TABLE test(id int, time date); 当结果显示为如下信息，则表示创建成功。 1 CREATE TABLE 创建自定义存储过程： 1 2 3 4 5 6 7 8 9 CREATE OR REPLACE PROCEDURE PRC_JOB_1() AS N_NUM integer :=1; BEGIN FOR I IN 1..1000 LOOP INSERT INTO test VALUES(I,SYSDATE); END LOOP; END; / 当结果显示为如下信息，则表示创建成功。 1 CREATE PROCEDURE 创建任务： 新创建的任务（未指定job_id）表示每隔1分钟执行一次存储过程PRC_JOB_1。 1 2 3 4 5 call dbms_job.submit('call public.prc_job_1(); ', sysdate, 'interval ''1 minute''', :a); job ----- 1 (1 row) 指定job_id创建任务。 1 2 3 4 5 call dbms_job.isubmit(2,'call public.prc_job_1(); ', sysdate, 'interval ''1 minute'''); isubmit --------- (1 row) 通过USER_JOBS视图查看当前用户已创建的任务信息。 需要有系统管理员权限才可以访问此系统视图，字段说明详见表1。 1 2 3 4 5 6 7 8 9 10 select job,dbname,start_date,last_date,this_date,next_date,broken,status,interval,failures,what from user_jobs; job | dbname | start_date | last_date | this_date | next_date | broken | status | interval | failures | what -----+----------+----------------------------+----------------------------+----------------------------+---------------------+--------+--------+---------------------+----------+---------------- ----------- 1 | db_demo | 2022-03-25 07:58:01.829436 | 2022-03-25 07:58:03.174817 | 2022-03-25 07:58:01.829436 | 2022-03-25 07:59:01 | n | s | interval '1 minute' | 0 | call public.prc _job_1(); 2 | db_demo | 2022-03-25 07:58:15.893383 | 2022-03-25 07:58:16.608959 | 2022-03-25 07:58:15.893383 | 2022-03-25 07:59:15 | n | s | interval '1 minute' | 0 | call public.prc _job_1(); (2 rows) 停止任务。 1 2 3 4 5 call dbms_job.broken(1,true); broken -------- (1 row) 启动任务。 1 2 3 4 5 call dbms_job.broken(1,false); broken -------- (1 row) 修改任务属性。 修改JOB的Next_date参数信息。例如，修改Job1的Next_date为1小时以后开始执行。 1 2 3 4 5 call dbms_job.next_date(1, sysdate+1.0/24); next_date ----------- (1 row) 修改JOB的Interval参数信息。例如，修改Job1的Interval为每隔1小时执行一次。 1 2 3 4 5 call dbms_job.interval(1,'sysdate + 1.0/24'); interval ---------- (1 row) 修改JOB的What参数信息。例如，修改Job1的What为执行SQL语句“insert into public.test values(333, sysdate+5);”。 1 2 3 4 5 call dbms_job.what(1,'insert into public.test values(333, sysdate+5);'); what ------ (1 row) 同时修改JOB的Next_date、Interval、What等多个参数信息。 1 2 3 4 5 call dbms_job.change(1, 'call public.prc_job_1();', sysdate, 'interval ''1 minute'''); change -------- (1 row) 删除JOB。 1 2 3 4 5 call dbms_job.remove(1); remove -------- (1 row) JOB的权限控制。 当创建一个JOB时，该JOB会和创建该JOB的数据库和用户绑定（即：pg_job系统视图新增的JOB记录中的dbname和log_user）。 如果当前用户是DBA用户、系统管理员、该JOB的创建用户（即：pg_job中的log_user），那么该用户有权限通过高级包接口remove、change、next_data、what、interval删除或修改JOB的参数信息。否则，会提示当前用户没有权限操作该JOB。 如果当前数据库是该JOB创建所属的数据库（即：为pg_job系统视图中的dbname），那么连接到当前数据库上可以通过高级包接口remove、change、next_data、what、interval删除或修改JOB的参数信息。 当删除JOB所属的数据库（即：为pg_job系统视图中的dbname）时，系统会关联删除该数据库从属的JOB记录。 当删除JOB所属的用户（即：为pg_job系统视图中的log_user）时，系统会关联删除该用户从属的JOB记录。

注意事项 新序列值的产生是靠GTM维护的，默认情况下，每申请一个序列值都要向GTM发送一次申请，GTM在当前值的基础上加上步长值作为产生的新值返回给调用者。GTM作为全局唯一的节点，势必成为性能的瓶颈，所以对于需要大量频繁产生序列号的操作，如使用Bulkload（批量快速导入数据）功能进行数据导入场景，是非常不推荐产生默认序列值的。比如，在下面所示的场景中， INSERT FROM SELECT语句的性能会非常慢。 1 2 3 4 5 6 7 CREATE SEQUENCE newSeq1; CREATE TABLE newT1 ( id int not null default nextval('newSeq1'), name text ); INSERT INTO newT1(name) SELECT name from T1; 可以提高性能的写法是（假设T1表导入newT1表中的数据为10000行）： 1 2 INSERT INTO newT1(id, name) SELECT id,name from T1; SELECT SETVAL('newSeq1',10000); 序列操作函数nextval()，setval() 等均不支持回滚。另外setval设置的新值，会对当前会话的nextval立即生效，但对其他会话，如果定义了cache，不会立即生效，在用尽所有缓存的值后，其变动才被其他会话感知。所以为了避免产生重复值，要谨慎使用setval，设置的新值不能是已经产生的值或者在缓存中的值。 如果必须要在bulkload场景下产生默认序列值，则一定要为newSeq1定义足够大的cache，并且不要定义Maxvalue或者Minvalue。数据库会试图将nextval('sequence_name')的调用下推到Data Node，以提高性能。 目前GTM对并发的连接请求是有限制的，当Data Node很多时，将产生大量并发连接， 这时一定要控制bulkload的并发数目，避免耗尽GTM的连接资源。如果目标表为复制表(DISTRIBUTE BY REPLICATION)时下推将不能进行。当数据量较大时，这对数据库将是个灾难。除了性能问题之外，空间也可能会剧烈膨胀，在导入结束后，需要用vacuum full来恢复。最好的方式还是如上建议的，不要在bulkload的场景中产生默认序列值。 另外，序列创建后，在每个节点上都维护了一张单行表，存储序列的定义及当前值，但此当前值并非GTM上的当前值，只是保存本节点与GTM交互后的状态。如果其他节点也向GTM申请了新值，或者调用了Setval修改了序列的状态，不会刷新本节点的单行表，但因每次申请序列值是向GTM申请，所以对序列正确性没有影响。

创建序列 方法一： 声明字段类型为序列整型来定义标识符字段。例如： 1 2 3 4 5 CREATE TABLE T1 ( id serial, name text ); 方法二： 创建序列，并通过nextval('sequence_name')函数指定为某一字段的默认值。这种方式更灵活，可以为序列定义cache，一次预申请多个序列值，减少与GTM的交互次数，来提高性能。 创建序列 1 CREATE SEQUENCE seq1 cache 100; 指定为某一字段的默认值，使该字段具有唯一标识属性。 1 2 3 4 5 CREATE TABLE T2 ( id int not null default nextval('seq1'), name text ); 除了为序列指定了cache，方法二所实现的功能基本与方法一类似。但是一旦定义cache，序列将会产生空洞(序列值为不连贯的数值，如：1.4.5)，并且不能保序。另外为某序列指定从属列后，该列删除，对应的sequence也会被删除。 虽然数据库并不限制序列只能为一列产生默认值，但最好不要多列共用同一个序列。 当前版本只支持在定义表的时候指定自增列，或者指定某列的默认值为nextval('seqname')， 不支持在已有表中增加自增列或者增加默认值为nextval('seqname')的列。

修改一个序列 ALTER SEQUENCE命令更改现有序列的属性，包括修改拥有者、归属列和最大值。 指定序列与列的归属关系。 将序列和一个表的指定字段进行关联。在删除那个字段或其所在表的时候会自动删除已关联的序列。 1 ALTER SEQUENCE seq1 OWNED BY T2.id; 将序列serial的最大值修改为300： 1 ALTER SEQUENCE seq1 MAXVALUE 300;

查看视图 查看MyView视图，查询结果为当前实时数据。 1 SELECT * FROM myview; 查看当前用户下的视图。 1 SELECT * FROM user_views; 查看所有视图。 1 SELECT * FROM dba_views; 查看某视图的具体信息。 执行如下命令查询dba_users视图的详细信息。 1 2 3 4 5 6 7 8 \d+ dba_users View "PG_CATALOG.DBA_USERS" Column | Type | Modifiers | Storage | Description ----------+-----------------------+-----------+----------+------------- USERNAME | CHARACTER VARYING(64) | | extended | View definition: SELECT PG_AUTHID.ROLNAME::CHARACTER VARYING(64) AS USERNAME FROM PG_AUTHID;