Support for AUTHID CURRENT_USER

Availability

This feature is available since MogDB version 5.0.6.

Introduction

This feature supports the specification of the AUTHID CURRENT_USER keyword when creating functions, stored procedures, and packages, allowing stored procedures or functions to execute SQL statements dynamically based on the caller's identity and with the search path set to the caller's schema.

Benefits

Enhances compatibility with Oracle, database security, and flexibility, better adapting to the permission requirements of different users or roles.

Description

AUTHID CURRENT_USER is a keyword used to specify the execution permissions for stored procedures, functions, packages, etc. When the AUTHID CURRENT_USER keyword is used when creating a function or stored procedure, the function or procedure will execute under the identity of the current caller, rather than the creator of the procedure, function, package, etc.

For example, users A and B have a table with the same name. A stored procedure with the AUTHID CURRENT_USER attribute is created under user A and is granted to user B for execution. When user A executes the stored procedure, the data will be inserted into A's table, and when user B executes the procedure, the data will be inserted into B's table.

The main purpose of using the AUTHID CURRENT_USER keyword is to create stored procedures or functions with more flexible permission control, allowing access permissions to be determined based on the executor's identity and to execute different logic based on the context, rather than strictly using the definer's permissions. Additionally, when using DBMS_OUTPUT.PUT_LINE or RAISE INFO to output information in stored procedures or functions, the current user's username can be obtained through the USER function.

It should also be noted that the behavior_compat_options = 'plsql_security_definer' switch will affect the default execution permissions of procedures, functions, packages, etc., when the keyword is omitted. If this switch is turned on and the keyword is omitted, it is in the creator mode; if this switch is turned off and the keyword is omitted, it is in the caller mode.

Syntax Description

CREATE [OR REPLACE] {PROCEDURE | FUNCTION | PACKAGE } object_name
    [[ EXTERNAL ] SECURITY INVOKER | AUTHID CURRENT_USER]
    {IS | AS}
    [declaration_section]
BEGIN
    -- body_section
END [object_name];

Note: SECURITY INVOKER and AUTHID CURRENT_USER have the same functionality.

Constraints

• Only supports the default A compatibility.
• This keyword requires the behavior_compat_options = 'set_procedure_current_schema' parameter to be used in conjunction.

Example

-- Create users and grant privileges
drop user if exists authid_user1 cascade;
drop user if exists authid_user2 cascade;
CREATE USER authid_user1 IDENTIFIED BY 'user1@123';
CREATE USER authid_user2 IDENTIFIED BY 'user2@123';
GRANT ALL PRIVILEGES to authid_user1;
GRANT ALL PRIVILEGES to authid_user2;

-- Switch to user 1 and create a stored procedure under user 1
gsql -d postgres -U authid_user1 -W 'user1@123'

create table authid_tab01(col1 integer, col2 integer);
create or replace procedure authid_proc01(val integer) AUTHID CURRENT_USER as
begin
    insert into authid_tab01 values(val, val);
        raise info 'insert values (%, %)', val, val;
        raise info 'current user %', USER;
end;
/

GRANT EXECUTE ON procedure authid_proc01(val in integer) TO authid_user2;

-- Switch to user 2, create a table with the same name, and call the stored procedure under user 1
gsql -d postgres -U authid_user2 -W 'user2@123'

SET behavior_compat_options = 'set_procedure_current_schema';

create table authid_tab01(col1 integer, col2 integer);

call authid_user1.authid_proc01(2);

-- Check the execution results
select * from authid_tab01;
 col1 | col2
------+------
    2 |    2
(1 row)

select * from authid_user1.authid_tab01;
(0 rows)

Related Pages

CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, behavior_compat_options