华为云用户手册

  • 场景三:大量SAVEPOINT/RELEASE语句处于idle in transaction(8.1.0之前集群版本) 执行如下命令查看PGXC_STAT_ACTIVITY视图: 1 SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack'; 结果显示SAVEPOINT/RELEASE语句处于idle in transaction。
  • 场景二:存储过程中有DDL语句,该存储过程结束前,其他节点上DDL语句执行完后的状态是idle in transaction 先创建存储过程: 1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE FUNCTION public.test_sleep() RETURNS void LANGUAGE plpgsql AS $$ BEGIN truncate t1; truncate t2; EXECUTE IMMEDIATE 'select pg_sleep(6)'; RETURN; END$$; 再执行如下命令查看PGXC_STAT_ACTIVITY视图: 1 SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack'; 查看结果显示:truncate t2处于idle in transaction状态,coorname为coordinator2。说明cn2上该语句已经执行完成,该存储过程在执行下一条语句。 解决方法:此类场景是由于存储过程执行慢导致,等存储过程执行完成即可,也可考虑优化存储过程中执行时间较长的语句。
  • 8.1.3及以上版本 8.1.3及以上版本的集群,AUTO VACUUM默认是打开的(由GUC参数autovacuum控制),用户通过设置对应的GUC参数后,系统会自动触发所有系统表和用户的行存表进行VACUUM FULL,用户不需要手动执行vacuum。 autovacuum_max_workers = 0,系统表和普通表都不会触发。 autovacuum = off,普通表不会触发,但系统表会触发。 以上仅针对行存表的AUTO VACCUM触发,如果需要针对列存表做自动触发VACUUM,还需要用户在管理控制台上配置智能调度任务。具体参见运维计划。
  • 使用string_agg 场景:使用string_agg查询表employee,出现查询结果不一致。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT * FROM employee; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+---------+------+---------------------+-------+------+-------- 7654 | MARTIN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 12000 | 1400 | 30 7566 | JONES | MANAGER | 7839 | 2022-11-08 00:00:00 | 32000 | 0 | 20 7499 | ALLEN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 16000 | 300 | 30 (3 rows) SELECT count(*) FROM (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg; count ------- 2 (1 row) SELECT count(*) FROM (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg; count ------- 1 (1 row) 原因分析: String_agg函数的作用是将组内的数据合并成一行,但是如果某用户的用法是string_agg(ename, ',') ,结果集就是不稳定的,因为没有指定组合的顺序。例如,上述语句中,对于select deptno, string_agg(ename, ',') from employee group by deptno; 输出结果既可以是: 1 30 | ALLEN,MARTIN 也可能是: 1 30 |MARTIN,ALLEN 两个结果都是合理的,因此上述关联场景下,有可能出现t1这个subquery中的结果和t2这个subquery中的结果对于deptno=30时的输出结果不一致。 解决方法: String_agg中增加order by排序,保证按顺序拼接。 1 SELECT count(*) FROM (select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t1 ,(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
  • 数据库兼容模式 场景:在数据库中查询空串结果不一致。 database1(TD兼容模式): 1 2 3 4 5 td=# select '' is null; isnull -------- f (1 row) database2(ORA兼容模式): 1 2 3 4 5 ora=# select '' is null; isnull -------- t (1 row) 原因分析: 查询空串结果不同是由于不同数据库兼容模式下空串与null语法有差异导致。 目前,DWS支持三种数据库兼容模式:Oracle、TD和MySql,不同兼容模式下语法和行为存在差异,兼容性差异说明可参考Oracle、Teradata和MySQL语法兼容性差异。 不同兼容模式下的database表现出不同的兼容性行为属于正常现象。可以通过查看select datname, datcompatibility from pg_database;确认数据库兼容性设置是否相同。 解决方法: 这种场景下只能将两个database的兼容性模式设置为一致的才能解决。Database的DBCOMPATIBILITY属性不支持ALTER,只能通过新建数据库的方法,在创建数据库时指定相同的DBCOMPATIBILITY属性解决。
  • 数据库兼容性行为配置项behavior_compat_options设置不同 场景:add_months函数计算结果不一致。 database1: 1 2 3 4 5 SELECT add_months('2018-02-28',3) from dual; add_months --------------------- 2018-05-28 00:00:00 (1 row) database2: 1 2 3 4 5 SELECT add_months('2018-02-28',3) from dual; add_months --------------------- 2018-05-31 00:00:00 (1 row) 原因分析: 数据库兼容性配置项behavior_compat_options不同会导致部分行为不同,该参数选项可参考behavior_compat_options中的相关选项描述。 此场景中behavior_compat_options配置项中的end_month_calculate参数控制add_months函数计算逻辑配置项。设置end_month_calculate配置项时,如果param1的日期(Day字段)为月末,并且param1的日期(Day字段)比result月份的月末日期比小,计算结果中的日期字段(Day字段)和result的月末日期保持一致。 解决方法: 需要将数据库中参数behavior_compat_options的兼容性配置项设置为一致。该参数类型为USERSET类型,可session级别设置或集群级修改。
  • 使用UN LOG GED表 场景: 使用unlogged表后,在集群重启后,关联查询结果集异常,查看unlogged表缺少部分数据。 原因分析: 如果设置max_query_retry_times为0,且在建表时指定UNLOGGED关键字,则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中,这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断,会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。因此当集群发生异常重启(进程重启、节点故障、集群重启)时,会导致部分内存中的数据未及时落盘,造成部分数据丢失,从而导致结果集异常。 解决方法: unlogged表在集群异常情况下的安全性无法保证,一般不能作为业务表使用,更多的场景是作为临时表使用。当出现集群故障后,为了保证数据正常,需要重建unlogged表或将数据备份后重新导入数据库。
  • 自定义函数属性设置不合理 场景:自定义函数get_count()并调用该函数出现结果不一致场景。 1 2 3 4 5 6 7 8 9 10 11 CREATE FUNCTION get_count() returns int SHIPPABLE as $$ declare result int; begin result = (select count(*) from test); --test表是hash表 return result; end; $$ language plpgsql; 调用该函数。 1 2 3 4 5 6 7 8 9 10 11 SELECT get_count(); get_count ----------- 2106 (1 row) SELECT get_count() FROM t_src; get_count ----------- 1032 (1 row) 原因分析: 由于该函数指定了SHIPPABLE的函数属性,因此生成计划时该函数会下推到DN上执行,该函数下推到DN后,由于函数定义中的test表是hash表,因此每个DN上只有该表的一部分数据,所以select count(*) from test; 返回的结果不是test表全量数据的结果,而是每个DN上部分数据的结果,因此导致加上from表后函数返回预期发生变化。 解决方法: 以下两种方法任选其一即可(推荐第一种方法): 将函数改为不下推:ALTER FUNCTION get_count() not shippable; 将函数中用到的表改为复制表,这样每个DN上都是一份该表的全量数据,即使下推到DN执行,也能保证结果集符合预期。
  • 窗口函数中使用排序后取部分结果 场景: 窗口函数row_number()中使用排序后查询表t3的c列,两次查询结果不同。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT * FROM t3 order by 1,2,3; a | b | c ---+---+--- 1 | 2 | 1 1 | 2 | 2 1 | 2 | 3 (3 rows) SELECT c,rn FROM (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; c | rn ---+---- 1 | 1 (1 row) SELECT c,rn FROM (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; c | rn ---+---- 3 | 1 (1 row) 原因分析: 如上所示,执行同一条语句:select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; 两次查询结果不同,因为在窗口函数的排序列a、b上存在重复值1、2且重复值在c列上的值不同,就会导致每次按照a,b列排序结果取第一条时,所取的数据是随机的,造成结果集不一致。 解决方法: 该场景需要将取值列c列也加到排序中,使排序结果获取的第一条数据固定。 1 2 3 4 5 SELECT c,rn FROM (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1; c | rn ---+---- 1 | 1 (1 row)
  • 子视图/子查询中使用排序 场景: 创建表test和视图v后,子查询中使用排序查询表test,出现查询结果不一致。 1 2 3 4 5 6 CREATE TABLE test(a serial ,b int); INSERT INTO test(b) VALUES(1); INSERT INTO test(b) SELECT b FROM test; … INSERT INTO test(b) SELECT b FROM test; CREATE VIEW v as SELECT * FROM test ORDER BY a; 问题SQL: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT * FROM v limit 1; a | b ---+--- 3 | 1 (1 row) SELECT * FROM (select * from test order by a) limit 10; a | b ----+--- 14 | 1 (1 row) SELECT * FROM test order by a limit 10; a | b ---+--- 1 | 1 (1 row) 原因分析: 对于子视图和子查询中order by是无效的。 解决方法: 不建议在子视图和子查询中使用order by,若要保证结果有序,需在最外层查询中使用order by。
  • 子查询limit 场景:子查询中使用limit,两次查询结果不一致。 1 2 3 4 5 6 7 8 9 10 11 SELECT * FROM (select a from test limit 1 ) order by 1; a --- 5 (1 row) SELECT * FROM (select a from test limit 1 ) order by 1; a --- 1 (1 row) 原因分析: 子查询中的limit会导致获取随机结果,从而最终查询结果为随机提取。 解决方法: 要保证最终查询结果的稳定,需避免在子查询中使用limit。
  • DWS以pg_toast_temp*或pg_temp*开头的Schema是什么? 查询Schema列表的时候,发现查询结果存在pg_temp*或pg_toast_temp*的Schema,如下图所示。 1 SELECT * FROM pg_namespace; 这些Schema是在创建临时表时,该临时表通过每个会话独立的以pg_temp开头的Schema来保证只对当前会话可见,因此,不建议用户在日常操作中手动删除以pg_temp,pg_toast_temp开头的Schema。 临时表只在当前会话可见,本会话结束后会自动删除,这些相应的Schema也会被删除。 父主题: 数据库使用
  • 场景一:返回结果集很大 以行存表的Seq Scan和Index Scan为例: Seq Scan:按照表的记录的排列顺序从头到尾依次检索扫描,每次扫描要取到所有的记录。这也是最简单最基础的扫表方式,扫描的代价比较大。 Index Scan:对于指定的查询,先扫描一遍索引,从索引中找到符合要求的记录的位置(指针),再定位到表中具体的Page去获取,即先走索引,再读表数据。 因此,根据两种扫描方式的特点可知,多数情况下,Index Scan要比Seq Scan快。但是如果获取的结果集占所有数据的比重很大时(超过70%),这时Index Scan因为要先扫描索引再读表数据反而不如直接全表扫描的速度快。
  • 查询分区边界 1 2 3 4 5 6 7 8 9 SELECT relname, partstrategy, boundaries FROM pg_partition where parentid=(select parentid from pg_partition where relname='my_table'); relname | partstrategy | boundaries -------------+--------------+------------ my_table | r | my_table_p1 | r | {600} my_table_p2 | r | {800} my_table_p3 | r | {950} my_table_p4 | r | {1000} (5 rows)
  • 查询数据在各DN分布 1 2 3 4 5 6 7 8 9 10 SELECT table_skewness('my_table'); table_skewness ------------------------------------ ("dn_6007_6008 ",3,50.000%) ("dn_6009_6010 ",2,33.333%) ("dn_6003_6004 ",1,16.667%) ("dn_6001_6002 ",0,0.000%) ("dn_6005_6006 ",0,0.000%) ("dn_6011_6012 ",0,0.000%) (6 rows)
  • 查询某一有数据分布DN上分区P1所对应的cudesc和delta表名称 1 2 3 4 5 6 EXECUTE DIRECT ON (dn_6003_6004) 'select a.relname from pg_class a, pg_partition b where (a.oid=b.reldeltarelid or a.oid=b.relcudescrelid) and b.relname=''my_table_p1'''; relname ---------------------- pg_delta_part_60317 pg_cudesc_part_60317 (2 rows)
  • 算子下盘的概念 任何计算都需要耗费内存空间,差别在于多少而已,对于如果耗费内存过多,会导致其他作业运行内存空间不足,导致作业不稳定,因此需要对查询语句的作业内存使用进行限制,保证作业运行的稳定性。 假如作业想要使用500M内存,但实际上因为内存限制最终只分配到300M内存,则需要把临时不用的数据写到磁盘上,内存中只保留正在使用的数据,这就是中间数据落盘的由来。当发生中间数据落盘时,称之为算子下盘。算子落盘空间太大除了会对查询性能有较大影响,还有可能导致数据库只读甚至磁盘满,因此DWS提供了用户算子空间限制,可以限制用户算子落盘的大小,在超限时查询报错退出。
  • 哪些参数可以控制下盘 work_mem:可以判断执行作业可下盘算子是否触发已使用内存量下盘点,当内存使用超过该参数后将触发算子下盘。该参数仅在非内存自适应场景(enable_dynamic_workload=off)时生效。work_mem参数设置通常是一个权衡,即要保证并发的吞吐量,又要保证单查询作业的性能,故需要根据实际执行情况(结合Explain Performance输出)进行调优。 temp_file_limit:可以限制落盘算子的落盘文件大小,一般建议根据实际情况设置,防止下盘文件将磁盘空间占满,超过该值将报错退出。
  • 如何判断语句是否发生了下盘 通过下盘文件确认:下盘文件位于实例目录的base/pgsql_tmp路径下,下盘文件以pgsql_tmp$queryid_$pid命名,可以根据queryid确认是哪条sql发生了下盘。 根据等待视图(pgxc_thread_wait_status)确认:等待视图中,当出现write file时,表示发生了中间结果下盘。 根据执行计划确认(explain performance):performance中出现spill、written disk、temp file num等关键字时,说明对应的算子出现了下盘。 根据topsql确认(前提:topsql功能已开启):实时TopSQL语句或历史TopSQL语句中,spill_info字段中会包含下盘信息,如果该字段不为空,说明有DN实例出现了下盘。
  • 如何避免下盘 发生算子下盘时,算子运算数据将写入磁盘,由于磁盘操作相对内存访问缓慢导致性能下降,查询响应时间出现极大劣化,因此应尽可能避免查询执行过程中的算子下盘,建议使用以下方法: 减小中间结果集:发生下盘时往往是由于中间结果集过大,因此可以增加过滤条件减少中间结果集大小。 避免数据倾斜:数据倾斜严重时会导致单DN上数据量过大,引起单DN下盘。 及时analyze:当统计信息不准时,行数估算可能偏小,导致计划选择非最优,从而出现下盘。 单点调优:对业务sql进行单点调优。 非内存自适应场景下,当中间结果集无法减少时,应根据实际情况适当调大work_mem参数。 内存自适应场景下,内存使用配置要使得数据库可用内存尽量增大,减少下盘概率。
  • 如何将DWS联结查询的null结果替换成0? 在执行outer join(left join、right join、full join)联结查询时,outer join在匹配失败的情况下结果集会补空,产生大量NULL值, 可以在联结查询时将这部分null值替换为0。 可使用coalesce函数,它的作用是返回参数列表中第一个非NULL的参数值。例如: 1 2 3 4 5 SELECT coalesce(NULL,'hello'); coalesce ---------- hello (1 row) 有表course1和表course2,使用left join对两表进行联结查询: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT * FROM course1; stu_id | stu_name | cour_name ----------+------------+-------------------- 20110103 | ALLEN | Math 20110102 | JACK | Programming Design 20110101 | MAX | Science (3 rows) SELECT * FROM course2; cour_id | cour_name | teacher_name ---------+--------------------+-------------- 1002 | Programming Design | Mark 1001 | Science | Anne (2 rows) SELECT course1.stu_name,course2.cour_id,course2.cour_name,course2.teacher_name FROM course1 LEFT JOIN course2 ON course1.cour_name = course2.cour_name ORDER BY 1; stu_name | cour_id | cour_name | teacher_name ------------+---------+--------------------+-------------- ALLEN | | | JACK | 1002 | Programming Design | Mark MAX | 1001 | Science | Anne (3 rows) 使用coalesce函数将查询结果中的空值替换为0或其他非0值: 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT course1.stu_name, coalesce(course2.cour_id,0) AS cour_id, coalesce(course2.cour_name,'NA') AS cour_name, coalesce(course2.teacher_name,'NA') AS teacher_name FROM course1 LEFT JOIN course2 ON course1.cour_name = course2.cour_name ORDER BY 1; stu_name | cour_id | cour_name | teacher_name ------------+---------+--------------------+-------------- ALLEN | 0 | NA | NA JACK | 1002 | Programming Design | Mark MAX | 1001 | Science | Anne (3 rows) 父主题: 数据库使用
  • 如何查看DWS表是行存还是列存? 表的存储方式由建表语句中的ORIENTATION参数控制,row表示行存,column表示列存。 不指定ORIENTATION参数,默认为row行存。 查看已创建的表是行存还是列存,可通过表定义函数PG_GET_TABLEDEF查询。 如下orientation=column表示为列存表。 目前暂不支持通过ALTER TABLE语句修改ORIENTATION参数,即行存表和列存表无法直接进行转换。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT * FROM PG_GET_TABLEDEF('customer_t1'); pg_get_tabledef ----------------------------------------------------------------------------------- SET search_path = tpchobs; + CREATE TABLE customer_t1 ( + c_customer_sk integer, + c_customer_id character(5), + c_first_name character(6), + c_last_name character(8) + ) + WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+ DISTRIBUTE BY HASH(c_last_name) + TO GROUP group_version1; (1 row) 父主题: 数据库使用
  • 截取单位差异 substrb(),按字节截取。 substr(),按字符截取。 substring(),按字符截取。 以utf8编码为例,1个汉字占3个字节,当使用substrb()截取长度3的子串时,只能截取到一个字符,而substr()/substring()可以截取到三个字符。 示例: 1 2 3 4 SELECT substrb('data数据库',3,5),substr('data数据库',3,5),substring('data数据库',3,5); substrb | substr | substring ---------+----------+----------- ta数 | ta数据库 | ta数据库
  • 函数形式 substrb()、substr()和substring()均为字符串截取函数,都可带两个或三个参数,用于提取字符串中指定截取的开始位置和截取的长度。函数定义如下: 1 2 3 substrb(string, from [, count]) substr(string, from [, count]) substring(string, from [, count]) 参数描述:从参数string中抽取子字符串,from表示抽取的起始位置,count表示抽取的字符串长度。 返回值类型:text
  • DWS唯一约束和唯一索引有什么区别? 唯一约束和唯一索引概念上不同 唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。 如果没有声明DISTRIBUTE BY REPLICATION,则唯一约束的列集合中必须包含分布列。 唯一索引用于限制索引字段值的唯一性,或者是多个字段组合值的唯一性。CREATE UNIQUE INDEX创建唯一索引。 唯一约束和唯一索引功能上不同 约束主要是为了保证数据的完整性,索引主要是为了辅助查询。 唯一约束和唯一索引使用方法上不同 唯一约束和唯一索引,都可以实现列数据的唯一,列值可以有NULL。 创建唯一约束,会自动创建一个同名的唯一索引,该索引不能单独删除,删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。DWS行存表支持唯一约束,而列存表不支持。 创建一个唯一索引,这个索引独立的、可以单独删除。目前,DWS只有B-Tree可以创建唯一索引。 如果一个列上想有约束和索引,且两者可以单独的删除。可以先建唯一索引,再建同名的唯一约束。 如果表的一个字段,要作为另外一个表的外键,这个字段必须有唯一约束(或是主键),如果只是有唯一索引,就会报错。 示例:创建两个列的复合索引,并不要求是唯一索引。 1 2 CREATE TABLE t (n1 number,n2 number,n3 number,PRIMARY KEY (n3)); CREATE INDEX t_idx ON t(n1,n2); DWS支持多个唯一索引。 1 2 CREATE UNIQUE INDEX u_index ON t(n3); CREATE UNIQUE INDEX u_index1 ON t(n3); 可以使用上述示例创建的索引t_idx来创建唯一约束t_uk,而且它只在列n1上唯一,也就是说唯一约束比索引更加严格。 1 ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE USING INDEX u_index; 父主题: 数据库使用
  • 语法使用 表1 语法使用 热度排名 高频问题 页面地址 1 查询字符串的bit位数 截取子字符串 返回结果替换某些字符串 返回字符串的前面几个字符 过滤头尾部分字符串 获取指定字符串的字节数 字符处理函数和操作符 2 如何创建分区表 支持的分区类型 CREATE TABLE PARTITION 3 查看所有schema 查看某个schema下所有的表 Schema 4 增加表字段 修改数据类型 向表中的列添加NOT NULL约束 设置主键 修改表属性 ALTER TABLE 5 日期函数 pg_sleep()如何使用 月份相减 date类型转换函数 时间、日期处理函数和操作符 6 调整分布列 把分布列调整到另外一列 分布列的数据无法update,提示Distributed key column can't be updated in current version 如何调整分布列 7 分区管理 增加或删除分区 重命名分区 查询某个分区的数据 创建和管理分区表 8 查询某个分区的行数 合并两个分区 ALTER TABLE PARTITION 9 调用存储过程 CALL 10 创建表 create table like CREATE TABLE 11 授权命令 grant语法使用 将用户权限授权给其他用户 将表权限授权给用户 将整库权限授权给用户 外表权限 GRANT 12 REPLACE替换函数 to_timestamp 转换为指定格式的时间戳 current_timestamp to_number 类型转换函数
  • 数据库管理 表2 数据库管理 热度排名 高频问题 页面地址 1 查看表定义 查看DDL 查看视图结构 查询数据库和表大小 查看表和数据库的信息 2 清理表空间 VACUUM FULL 表在大量执行增删改后,如何提升查询性能 VACUUM 3 如何查看某用户在当前表上是否已有权限 查看某用户对某张表是否有某种权限 如何查看DWS某个用户有哪些表的权限? 4 查询和终止阻塞语句 查询活跃语句 终止会话 锁等待超时 分析正在执行的SQL 5 SQL执行计划详解 执行计划怎么看 Nested Loop、Hash Join和Merge Join的差异 SQL执行计划详解 6 解除只读 数据库进入只读状态 磁盘使用率高&集群只读处理方案 7 收集统计信息 ANALYZE | ANALYSE 8 优化器配置 打开或关闭nestloop 打开或关闭mergejoin 影响执行计划的参数 优化器方法配置 9 更改数据库时区 更改Timezone 数据库时间与系统时间不一致,如何更改数据库默认时区 10 怎么查函数定义 访问权限查询函数 查询视图定义 系统信息函数 11 技术指标 支持的分区表大小 单表最大数据量 表支持的最大列数 技术指标 12 开发人员选项 控制查询优化器是否使用分布式框架 开发人员选项
  • 如何查看DWS某个用户有哪些表的权限? 场景一:查看用户有哪些表的权限,可使用information_schema.table_privileges系统表查看。例如 1 SELECT * FROM information_schema.table_privileges WHERE GRANTEE='user_name'; 表1 table_privileges字段 字段 数据类型 描述 grantor sql_identifier 赋权用户。 grantee sql_identifier 被赋权用户。 table_catalog sql_identifier 包含该表的数据库名。 table_schema sql_identifier 包含该表的模式名。 table_name sql_identifier 表名。 privilege_type character_data 被赋予的权限类型:SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFEREN CES ,ANALYZE,VACUUM,ALTER,DROP或TRIGGER。 is_grantable yes_or_no 权限是否可赋予其他用户,YES表示可授予,NO表示不可授予。 with_hierarchy yes_or_no 是否允许在表继承层级上的特定操作。当特定操作为SELECT时显示YES,否则为NO。 如上图所示,表示用户u2拥有Schema u2下的t2的所有权限和Schema u1下的t1的SELECT权限。 需注意,在查询有哪些表权限时,information_schema.table_privileges只能查到当前用户被直接授予的权限,而函数has_table_privilege()除了能查询被直接授予的权限外还能查到间接的权限(即GRANT role to user获取的)。例如: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 CREATE TABLE t1 (c1 int); CREATE USER u1 password '********'; CREATE USER u2 password '********'; GRANT dbadmin to u2; // 间接通过角色成员关系赋予权限 GRANT SELECT on t1 to u1; // 直接授予权限 SET ROLE u1 password '********'; SELECT * FROM public.t1; 直接授权可以访问表 c1 ---- (0 rows) SET ROLE u2 password '********'; SELECT * FROM public.t1; //间接授权可以访问表 c1 ---- (0 rows) RESET role; //切回到dbadmin SELECT * FROM information_schema.table_privileges WHERE table_name = 't1'; // information_schema.table_privileges仅能看到直接授权 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+------------+---------------+--------------+------------+----------------+--------------+---------------- dbadmin | u1 | gaussdb | public | t1 | SELECT | NO | YES (1 rows) SELECT has_table_privilege('u2', 'public.t1', 'select'); // has_table_privilege还可以看到间接授权 has_table_privilege --------------------- t (1 row) 场景二:查看用户是否有某张表的权限,可以通过以下方法。 执行以下语句查询pg_class系统表。 1 SELECT * FROM pg_class WHERE relname = 'tablename'; 查看relacl字段,该字段回显结果如下,权限参数参见表2。 "rolename=xxxx/yyyy" --表示rolename对该表有xxxx权限,且权限来自yyyy; "=xxxx/yyyy" -- 表示public对该表有xxxx权限,且权限来自yyyy。 例如下图: joe=arwdDxtA,表示joe用户有所有权限(ALL PRIVILEGES)。 leo=arw/joe,表示leo用户拥有读、写、改权限,该权限来自joe授权。 表2 权限的参数说明 参数 参数说明 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|ANALYSE arwdDxtA ALL PRIVILEGES(用于表) * 给前面权限的授权选项 如果要查某用户对某张表是否有某种权限,也可以通过访问权限查询函数has_table_privilege进行查询。 1 SELECT * FROM has_table_privilege('用户名','表名','select'); 例如,查询joe对表t1是否有查询权限。 1 SELECT * FROM has_table_privilege('joe','t1','select'); 父主题: 账户与权限
  • USER/ROLE 用户或角色是数据库服务器(集群)全局范围内的权限控制系统,是集群业务的所有者和执行者,用于各种集群范围内所有的对象权限管理。因此角色不特定于某个单独的数据库,但角色登录集群的时候必须要显式指定登录的用户名,以保证当前连接执行的操作者的透明性。同时数据库也会通过权限管理限定用户的访问和操作权限。 用户是权限的最终体现者,所有的权限管理最终都体现在用户对数据库对象的操作权限是否被允许。
  • 权限管理 DWS中的权限管理分为三种场景: 系统权限 系统权限又称为用户属性,包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。 系统权限一般通过CREATE/ALTER ROLE语法来指定。其中,SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承,也无法授予PUBLIC。 用户权限 将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下,每个角色或用户都可视为拥有一个或多个数据库权限的集合。 当声明了WITH ADMIN OPTION,被授权的用户可以将该权限再次授予其他角色或用户,以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时,所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。 数据库系统管理员可以给任何角色或用户授予/撤销任何权限。拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。 数据对象权限 将数据库对象(表和视图、指定字段、数据库、函数、模式等)的相关权限授予特定角色或用户。GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。
共100000条
提示

您即将访问非华为云网站,请注意账号财产安全