GRANT/REVOKE PROXY

功能描述

授予、召回代理者权限。

注意事项

N/A

语法格式

1. GRANT PROXY ON user
2. TO user [, user] ...
3. [WITH GRANT OPTION]

5. REVOKE PROXY ON user
6. FROM user [, user] ...

参数说明

• {PROXY}

语法关键词。 - user

用户（角色）名。

示例

1. --创建简单表
2. opengauss=# CREATE SCHEMA tst_schema1;
3. opengauss=# SET SEARCH_PATH TO tst_schema1;
4. opengauss=# CREATE TABLE tst_t1
5. (
6. id int,
7. name varchar(20)
8. );
9. INSERT INTO tst_t1 values(20220101, 'proxy_example');

11. --创建用户
12. opengauss=# DROP ROLE if EXISTS test_proxy_u1;
13. opengauss=# CREATE ROLE test_proxy_u1 IDENTIFIED BY 'test_proxy_u1@123';
14. opengauss=# DROP ROLE if EXISTS test_proxy_u2;
15. opengauss=# CREATE ROLE test_proxy_u3 IDENTIFIED BY 'test_proxy_u2@123';
16. opengauss=# DROP ROLE if EXISTS test_proxy_u3;
17. opengauss=# CREATE ROLE test_proxy_u3 IDENTIFIED BY 'test_proxy_u3@123';

19. --schema、表权限授予
20. opengauss=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
21. opengauss=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
22. opengauss=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
23. opengauss=# GRANT ALL ON tst_t1 to test_proxy_u1;

25. --权限检测（无权限）
26. opengauss=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
27. opengauss=> SELECT * FROM tst_schema1.tst_t1;
28. ERROR:  permission denied for relation tst_t1
29. DETAIL:  N/A

31. --权限检测（拥有代理者权限）
32. opengauss=> RESET ROLE;
33. opengauss=# GRANT PROXY ON test_proxy_u1 TO test_proxy_u2;
34. opengauss=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
35. opengauss=>  SELECT * FROM tst_schema1.tst_t1;
36. id    |     name
37. ----------+---------------
38. 20220101 | proxy_example

40. --权限检测（级联式检测usr_1->usr_2->usr_3)
41. opengauss=> RESET ROLE;
42. opengauss=# GRANT PROXY ON test_proxy_u2 TO test_proxy_u3;
43. opengauss=# SET ROLE test_proxy_u3 PASSWORD 'test_proxy_u3@123';
44. opengauss=>  SELECT * FROM tst_schema1.tst_t1;
45. id    |     name
46. ----------+---------------
47. 20220101 | proxy_example

49. --对被代理者授予的权限检测（with grant option)
50. opengauss=> RESET ROLE;
51. opengauss=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
52. opengauss=> grant proxy on test_proxy_u1 to test_proxy_u3;
53. ERROR:  must have admin option on role "test_proxy_u1"
54. opengauss=> RESET ROLE;
55. RESET
56. opengauss=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
57. SET
58. opengauss=> grant proxy on test_proxy_u1 to test_proxy_u3;
59. ERROR:  must have admin option on role "test_proxy_u1"
60. opengauss=> RESET ROLE;
61. opengauss=# grant proxy on test_proxy_u1 to test_proxy_u2 with grant option;
62. opengauss=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
63. opengauss=> grant proxy on test_proxy_u1 to test_proxy_u3;

65. --召回代理权限测试
66. opengauss=> revoke proxy on test_proxy_u1 from test_proxy_u3;
67. opengauss=> revoke proxy on test_proxy_u1 from test_proxy_u2;
68. opengauss=>  SET ROLE test_proxy_u3 password 'test_proxy_u3@123';
69. opengauss=> SELECT * FROM tst_schema1.tst_t1;
70. ERROR:  permission denied for relation tst_t1
71. DETAIL:  N/A