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

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

Best Answer

  • Bryan_HBryan_H Vertica Employee Administrator
    Answer ✓

    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

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file