Permission Management

By default, stored procedures are granted the SECURITYINVOKER permission. To change this default to the SECURITYDEFINER permission, set the GUC parameter behavior_compat_options to 'plsql_security_definer'. For details about permissions, see "SQL Reference > SQL Syntax > C > CREATE FUNCTION" in Developer Guide.

Improper permission mode may cause unauthorized access to sensitive data or unauthorized resource operations. Therefore, select and configure the permission mode with caution to ensure system security.

gaussdb=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA
-- Create two different users.
gaussdb=# CREATE USER test_user1 PASSWORD '********';
CREATE ROLE
gaussdb=# CREATE USER test_user2 PASSWORD '********';
CREATE ROLE
-- Set the permissions of the two users on 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
-- Switch to the test_user1 user and create a table and a stored procedure.
gaussdb=# SET SESSION AUTHORIZATION test_user1 PASSWORD '********';
SET
gaussdb=> CREATE TABLE best_practices_for_procedure.user1_tb (a int, b int);
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
-- Switch to the test_user2 user to execute the stored procedure created by the test_user1 user. An error is reported, indicating that the user does not have the permission on the user1_tb table because stored procedures are executed with the caller's permissions by default.
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
-- Set the GUC parameter to use the creator's permissions by default when creating stored procedures.
gaussdb=> SET behavior_compat_options='plsql_security_definer';
SET
-- Switch to the test_user1 user and re-create the stored procedure.
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
-- Switch to the test_user2 user and execute the stored procedure. The execution is successful.
gaussdb=> RESET SESSION AUTHORIZATION;
RESET
gaussdb=# SET SESSION AUTHORIZATION test_user2 PASSWORD '********';
SET
gaussdb=> CALL best_practices_for_procedure.user1_proc();
 proc_user1
------------

(1 row)

-- Switch to the test_user1 user and view the table content.
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)

-- Clean the environment.
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