文档中心MogDBMogDB StackUqbar
v5.0

文档:v5.0

支持的版本:

其他版本:

GRANT/REVOKE PROXY

功能描述

授予、召回代理者权限。

注意事项

N/A

语法格式

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

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

参数说明

  • {PROXY}

    语法关键词。

  • user

    用户(角色)名。

示例

--创建简单表
MogDB=# CREATE SCHEMA tst_schema1;
MogDB=# SET SEARCH_PATH TO tst_schema1;
MogDB=# CREATE TABLE tst_t1
(
id int,
name varchar(20)
);
INSERT INTO tst_t1 values(20220101, 'proxy_example');

--创建用户
MogDB=# DROP ROLE if EXISTS test_proxy_u1;
MogDB=# CREATE ROLE test_proxy_u1 IDENTIFIED BY 'test_proxy_u1@123';
MogDB=# DROP ROLE if EXISTS test_proxy_u2;
MogDB=# CREATE ROLE test_proxy_u3 IDENTIFIED BY 'test_proxy_u2@123';
MogDB=# DROP ROLE if EXISTS test_proxy_u3;
MogDB=# CREATE ROLE test_proxy_u3 IDENTIFIED BY 'test_proxy_u3@123';

--schema、表权限授予
MogDB=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
MogDB=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
MogDB=# GRANT ALL ON SCHEMA tst_schema1 TO test_proxy_u2;
MogDB=# GRANT ALL ON tst_t1 to test_proxy_u1;

--权限检测(无权限)
MogDB=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
MogDB=> SELECT * FROM tst_schema1.tst_t1;
ERROR:  permission denied for relation tst_t1
DETAIL:  N/A

--权限检测(拥有代理者权限)
MogDB=> RESET ROLE;
MogDB=# GRANT PROXY ON test_proxy_u1 TO test_proxy_u2;
MogDB=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
MogDB=>  SELECT * FROM tst_schema1.tst_t1;
    id    |     name      
----------+---------------
 20220101 | proxy_example
 
 --权限检测(级联式检测usr_1->usr_2->usr_3)
 MogDB=> RESET ROLE;
MogDB=# GRANT PROXY ON test_proxy_u2 TO test_proxy_u3;
MogDB=# SET ROLE test_proxy_u3 PASSWORD 'test_proxy_u3@123';
MogDB=>  SELECT * FROM tst_schema1.tst_t1;
    id    |     name      
----------+---------------
 20220101 | proxy_example
 
--对被代理者授予的权限检测(with grant option)
MogDB=> RESET ROLE;
MogDB=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
MogDB=> grant proxy on test_proxy_u1 to test_proxy_u3;
ERROR:  must have admin option on role "test_proxy_u1"
MogDB=> RESET ROLE;
RESET
MogDB=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
SET
MogDB=> grant proxy on test_proxy_u1 to test_proxy_u3;
ERROR:  must have admin option on role "test_proxy_u1"
MogDB=> RESET ROLE;
MogDB=# grant proxy on test_proxy_u1 to test_proxy_u2 with grant option;
MogDB=# SET ROLE test_proxy_u2 PASSWORD 'test_proxy_u2@123';
MogDB=> grant proxy on test_proxy_u1 to test_proxy_u3;

--召回代理权限测试
MogDB=> revoke proxy on test_proxy_u1 from test_proxy_u3;
MogDB=> revoke proxy on test_proxy_u1 from test_proxy_u2;
MogDB=>  SET ROLE test_proxy_u3 password 'test_proxy_u3@123';
MogDB=> SELECT * FROM tst_schema1.tst_t1;
ERROR:  permission denied for relation tst_t1
DETAIL:  N/A
Copyright © 2011-2024 www.enmotech.com All rights reserved.