Invoker's Rights: AUTHID CURRENT_USER

After I read about SQL Injection, that make me think about "AUTHID CURRENT_USER" in my head.

By default, a stored procedure or SQL method executes with the privileges of its owner, not its current user.

So, a program unit does not need to be executed with the escalated privileges of the definer, we should specify that the program unit executes with the privileges of the caller, also known as the invoker.

Stored program units to be executed with invoker's rights must include the AUTHID CURRENT_USER clause.

We can use SYS_CONTEXT('USERENV','CURRENT_USER') function to check.

Example:


SQL> conn / as sysdba
Connected.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_getuser
2 IS
3 user varchar2(10);
4 begin
5 select SYS_CONTEXT('USERENV','CURRENT_USER') into user from dual;
6 dbms_output.put_line(user);
7 end;
8 /

Procedure created.

SQL>
SQL> grant execute on test_getuser to public;

Grant succeeded.


$ sqlplus hr

SQL> show user;
USER is "HR"
SQL>
SQL>
SQL> set serverout on
SQL> exec sys.test_getuser;

SYS

PL/SQL procedure successfully completed.

SQL>

modified program and use "AUTHID CURRENT_USER"


SQL> conn / as sysdba
Connected.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_getuser
2 AUTHID CURRENT_USER
3 IS
4 user varchar2(10);
5 begin
6 select SYS_CONTEXT('USERENV','CURRENT_USER') into user from dual;
7 dbms_output.put_line(user);
8 end;
9 /

Procedure created.

$ sqlplus hr

SQL> show user;
USER is "HR"

SQL> set serverout on

SQL> exec sys.test_getuser;
HR

PL/SQL procedure successfully completed.