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