plsql authid current_user explained

====================

There are two types of rights on procedure

1. definer [by default]
2. invoker [can be used by adding authid current_user]

---------------------
Lets take an example, there are two users , user1, user2, both have defined temp table.

user1/user 1                        user2/user2/u

table: temp                         table: temp
empid empname                empid empname
100   smith                         100   johns
----------------------------------------------------------
create a procedure in user1 [schema]

create or replace procedure get_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

grant this procedure to user2 as well.
> grant execute on get_user to user2;
---------------------------------------------------------------

when you run it from user1> you get smith.
when you run it from user2> you get smith.

reason: since proc is run by definer rights by default.
--------------------------------------------------------------

to get johns printed:

you need to define as below in user1: so that while executed, it will be executed with invoker rights

create or replace procedure get_user authid current_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

==============================================================

We always create package and proc in custom schema, and then use those pkgs on schema's like HR and GL etc, then if don't use authid current_user, your proc will give undesired results because of above reason.

Refer: https://www.youtube.com/watch?v=L8YYimP7cAQ&list=PLVlQHNRLflP-B-e7daJkgKPS_vmya5mY6&index=13&t=0s