权限控制

存储过程默认具有SECURITYINVOKER权限。如果希望将默认行为改为SECURITYDEFINER权限，需要设置GUC参数 behavior_compat_options='plsql_security_definer'。权限详情请参见《开发指南》中“SQL参考 > SQL语法 > C > CREATE FUNCTION”章节。

选择不当的权限模式可能导致越权访问敏感数据，或进行未授权的资源操作。因此，应谨慎选择和配置权限模式，以确保系统的安全性。

gaussdb=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA
--创建两个不同的用户。
gaussdb=# CREATE USER test_user1 PASSWORD '********';
CREATE ROLE
gaussdb=# CREATE USER test_user2 PASSWORD '********';
CREATE ROLE
--设置两个用户在SCHEMA best_practices_for_procedure上的权限。
gaussdb=# GRANT usage, create ON SCHEMA best_practices_for_procedure TO test_user1;
GRANT
gaussdb=# GRANT usage, create ON SCHEMA best_practices_for_procedure TO test_user2;
GRANT
--切换用户test_user1,创建表和存储过程。
gaussdb=# SET SESSION AUTHORIZATION test_user1 PASSWORD '********';
SET
gaussdb=> CREATE TABLE best_practices_for_procedure.user1_tb (a int, b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=> CREATE OR REPLACE PROCEDURE best_practices_for_procedure.user1_proc() AS
  BEGIN
    INSERT INTO best_practices_for_procedure.user1_tb VALUES(1,1);
  END;
/
CREATE PROCEDURE
--切换test_user2执行test_user1创建的存储过程，执行报错，对表user1_tb没有权限，因为执行存储过程默认使用调用者的权限。
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# SET SESSION AUTHORIZATION test_user2 PASSWORD '********';
SET
gaussdb=> CALL best_practices_for_procedure.user1_proc();
ERROR:  Permission denied for relation user1_tb.
DETAIL:  N/A.
CONTEXT:  SQL statement "insert into best_practices_for_procedure.user1_tb values(1,1)"
PL/pgSQL function best_practices_for_procedure.user1_proc() line 3 at SQL statement
--设置guc将创建存储过程默认使用创建者权限。
gaussdb=> SET behavior_compat_options='plsql_security_definer';
SET
--切换用户test_user1重新创建存储过程。
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# SET SESSION AUTHORIZATION test_user1 password '********';
SET
gaussdb=> CREATE OR REPLACE PROCEDURE best_practices_for_procedure.user1_proc() AS
  BEGIN
    INSERT INTO best_practices_for_procedure.user1_tb VALUES(1,1);
  END;
/
CREATE PROCEDURE
--切换用户test_user2执行存储过程，执行成功。
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# SET SESSION AUTHORIZATION test_user2 PASSWORD '********';
SET
gaussdb=> CALL best_practices_for_procedure.user1_proc();
 user1_proc
------------

(1 row)

--切换用户test_user1查看表中内容。
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# SET SESSION AUTHORIZATION test_user1 PASSWORD '********';
SET
gaussdb=> SELECT * FROM best_practices_for_procedure.user1_tb;
 a | b
---+---
 1 | 1
(1 row)

--清理环境。
gaussdb=> RESET behavior_compat_options;
RESET
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table best_practices_for_procedure.user1_tb
drop cascades to function best_practices_for_procedure.user1_proc()
DROP SCHEMA
gaussdb=# DROP USER test_user1;
DROP ROLE
gaussdb=# DROP USER test_user2;
DROP ROLE