Skip to main content
Question

How to find invoking user in PL/vSQL procedure with definer security

  • March 8, 2022
  • 2 replies
  • 4 views

Sergey_Cherepan_1
Forum|alt.badge.img+2

Hi,
It appears, if I am creating PL/vSQL procedure with definer security, call to current_user() return definer user.
How I can find what user is invoking procedure with definer security?
Thank you
Sergey

2 replies

Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • March 8, 2022

According to documentation "A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller."

You can pass the current user as an argument:

CREATE PROCEDURE who_are_you(caller VARCHAR(128)) SECURITY DEFINER AS $$
DECLARE
results VARCHAR(255);
BEGIN
results := EXECUTE 'SELECT CURRENT_USER;';
RAISE INFO 'current_user: %', results;
results := EXECUTE 'SELECT USER_NAME FROM CURRENT_SESSION;';
RAISE INFO 'current_user: %', results;
RAISE INFO 'actual user: %', caller;
END;
$$;

bryan=> call who_are_you(current_user);
INFO 2005: current_user: dbadmin
INFO 2005: current_user: dbadmin
INFO 2005: actual user: bryan


Sergey_Cherepan_1
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • March 9, 2022

This is a creative solution to problem, but it does work.
Thanks!