如何给指定用户赋予某张表的权限?
给指定用户赋予某张表的权限主要通过以下语法实现,本章主要介绍常见的几种场景,包括只读(SELECT)、插入(INSERT)、改写(UPDATE)和拥有所有权限。
语法格式
1 2 3 4 5 6 |
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]; |
场景介绍
假设当前有用户u1~u5,在系统中有对应的同名Schema u1~u5,各用户的权限管控如下:
- u2作为只读用户,需要表u1.t1的SELECT权限。
- u3作为插入用户,需要表u1.t1的INSERT权限。
- u4作为改写用户,需要表u1.t1的UPDATE权限。
- u5作为拥有所有权限的用户,需要表u1.t1的所有权限。
用户名 |
用户类型 |
Grant授权语句 |
查询 |
插入 |
修改 |
删除 |
||
---|---|---|---|---|---|---|---|---|
u1 |
所有者 |
- |
√ |
√ |
√ |
√ |
||
u2 |
只读用户 |
|
√ |
x |
x |
x |
||
u3 |
插入用户 |
|
x |
√ |
x |
x |
||
u4 |
改写用户 |
须知:
授予UPDATE权限必须同时授予SELECT权限,否则会出现信息泄露。 |
√ |
x |
√ |
x |
||
u5 |
拥有所有权限的用户 |
|
√ |
√ |
√ |
√ |
操作步骤
以下将演示不同权限的授权方法和验证过程。
- 打开窗口1(即dbadmin连接会话窗口,后续不再提示),使用系统管理员dbadmin连接DWS数据库,创建用户u1~u5(系统默认会创建u1~u5的同名SCHEMA)。
1 2 3 4 5
CREATE USER u1 PASSWORD '{password}'; CREATE USER u2 PASSWORD '{password}'; CREATE USER u3 PASSWORD '{password}'; CREATE USER u4 PASSWORD '{password}'; CREATE USER u5 PASSWORD '{password}';
- 在SCHEMA u1下创建表u1.t1。
1
CREATE TABLE u1.t1 (c1 int, c2 int);
- 为表中插入两条数据。
1 2
INSERT INTO u1.t1 VALUES (1,2); INSERT INTO u1.t1 VALUES (1,2);
- DWS中引入了SCHEMA层概念,如果有SCHEMA,需要先给用户赋予SCHEMA的使用权限。
1
GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;
- 给只读用户u2赋予表u1.t1的查询权限。
1
GRANT SELECT ON u1.t1 TO u2;
- 打开窗口2(即用户u2连接会话窗口,后续不再提示),使用用户u2连接DWS数据库,验证u2可以查询u1.t1表,但是不能写入和修改,此时u2为只读用户。
1 2 3
SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;
- 切回窗口1,分别给u3、u4、u5赋予对应的权限。
1 2 3
GRANT INSERT ON u1.t1 TO u3; --插入用户u3,可以插入数据 GRANT SELECT,UPDATE ON u1.t1 TO u4; --改写用户u4,可以修改表 GRANT ALL PRIVILEGES ON u1.t1 TO u5; --拥有所有权限的用户u5,可以对表进行查询、插入、改写和删除
- 打开窗口3,使用用户u3连接DWS数据库,验证u3可以插入u1.t1,但是不能查询和修改,此时u3为插入用户。
1 2 3
SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;
- 打开窗口4,使用用户u4连接DWS数据库,验证u4可以修改u1.t1,同时还可以查询,但是不能插入,此时u4为改写用户。
1 2 3
SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;
- 打开窗口5,使用用户u5连接DWS数据库,验证u5可以查询、插入、修改和删除u1.t1,此时u5为拥有所有权限的用户。
1 2 3 4
SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; DELETE FROM u1.t1;
- 最后切回窗口1,通过函数has_table_privilege分别查询每个用户的权限。
1
SELECT * FROM pg_class WHERE relname = 't1';
返回结果,查看relacl字段,该字段回显结果如下。"rolename=xxxx/yyyy" --表示rolename对该表有xxxx权限,且权限来自yyyy;
例如下图,与以上验证结果完全一致。
- u1=arwdDxtA/u1,表示u1为owner,拥有所有权限。
- u2=r/u1,表示u2拥有读权限。
- u3=a/u1,表示u3拥有插入权限。
- u4=rw/u1,表示u4拥有读和修改权限。
- u5=arwdDxtA/u1,表示u5拥有所有权限。