授权语法比照
新增功用
改动点有:
支撑同一SCEMA下政策授权
ALL [TABLES|SEQUENCES|FUNCTIONS] IN SCHEMA schema_name;
4.3不支撑,6.0操作示例:
# 6.0
yuanzhe_test=> dt s1.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------------
s1 | t1 | table | yuanzhe_test
s1 | t2 | table | yuanzhe_test
(2 rows)
yuanzhe_test=> grant select on all tables in schema s1 to u1;
GRANT
支撑列等级授权
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ]( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO { role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
4.3不支撑,6.0 操作示例:
yuanzhe_test=> d s1.t1
Table "s1.t1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
yuanzhe_test=> grant select (name) on s1.t1 to u1;
GRANT
yuanzhe_test=> c yuanzhe_test u1
Password for user u1:
You are now connected to database "yuanzhe_test" as user "u1".
yuanzhe_test=> select name from s1.t1;
name
------
(0 rows)
yuanzhe_test=> select id from s1.t1;
ERROR: permission denied for relation t1
支撑对自定义DOMAIN授权
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
4.3也支撑DOMAIN,可是不支撑授权,6.0支撑授权,示例:
# 4.3
yuanzhe_test=> CREATE DOMAIN us_zip_code AS TEXT CHECK
yuanzhe_test-> ( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' );
CREATE DOMAIN
yuanzhe_test=> grant USAGE on DOMAIN us_zip_code to u1;
ERROR: syntax error at or near "us_zip_code"
LINE 1: grant USAGE on DOMAIN us_zip_code to u1;
^
# 6.0
yuanzhe_test=> CREATE DOMAIN us_zip_code AS TEXT CHECK
yuanzhe_test-> ( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' );
CREATE DOMAIN
yuanzhe_test=> grant USAGE on DOMAIN us_zip_code to u1;
GRANT
支撑对FDW权限处理
6.0支撑FDW,对FDW相关的权限处理,现在只需superuser才支撑创立FDW,未翻开
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
支撑对TYPE进行授权
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
4.3 支撑创立TYPE,但不支撑授权,6.0支撑授权。示例:
# 4.3
yuanzhe_test=> CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
yuanzhe_test=> grant USAGE on TYPE compfoo to u1;
ERROR: syntax error at or near "compfoo"
LINE 1: grant USAGE on TYPE compfoo to u1;
^
# 6.0
yuanzhe_test=> CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
yuanzhe_test=> grant USAGE on TYPE compfoo to u1;
GRANT
yuanzhe_test=> c yuanzhe_test u1
Password for user u1:
You are now connected to database "yuanzhe_test" as user "u1".
yuanzhe_test=> create table t3(id int, dum compfoo);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
yuanzhe_test=> d+ t3
Table "public.t3"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
dum | compfoo | | extended | |
发表评论