How can i get current username in PL/SQL?

Question

i have some code

create or replace function policy_test (p_schema varchar2, p_object varchar2)
return varchar2 is
    v VARCHAR2(30);
begin
    v := USER;
    return 'name = ' ||  v;
end;

Begin
DBMS_RLS.add_policy (
   object_schema => 'system',
   object_name  => 'WORKMAN',
   policy_name  => 'WORKMAN_policy_test2',
   function_schema => 'system',
   policy_function  => 'policy_test',
   statement_types => 'select',
   update_check => true
);
End;

and i'd like to ruturn 'name=system' or 'name=Jack' from function policy_test, but i get some error :

[28113] ORA-28113 policy predicate has error

How can i get current user name in policy_test and return sting like 'name=Jack' ?

https://stackoverflow.com/questions/33740973/ora-28113-policy-predicate-has-error — OldProgrammer, Dec 10 '20 at 22:00
Does this answer your question? [Predicate Error with Oracle VPD/RLS](https://stackoverflow.com/questions/8957425/predicate-error-with-oracle-vpd-rls) — Sam M, Dec 10 '20 at 22:39

score 1 · Answer 1 · answered Dec 10 '20 at 22:47

Your particular VPD policy will append the return value to an implicit WHERE clause. The problem lies in your desired behavior. You stated, "and i'd like to return 'name=system' or 'name=Jack' from function policy_test"

If you were to write out an SQL statement like this: SELECT * FROM mytable WHERE name=system or SELECT * FROM mytable WHERE name=Jack, what would happen? The query will fail every time. When doing string comparison in a WHERE clause, you must enclose the string literal with single tick marks. Change it to WHERE name = 'Jack' and you have valid SQL.

Back to your function. Your function is returning a string, name=Jack and so WHERE name=Jack is what Oracle is generating. You haven't quoted the string literal so the SQL fails. The nature of VPD hides the exception raised (very frustrating) but Oracle does log it in the trace file.

Thank you for answer, but i need to transform variable v from context like this v := USER; to string type and then concatenation it : return 'name = ' || v; — СuriousBoy, Dec 11 '20 at 07:33
Use concatenate to add the apostrophes. There are three methods for doing this. The one I use is the double single quote. You can also use the `q` method or `chr(39)`. `return 'name = ''' || v || '''';` — Sam M, Dec 11 '20 at 16:05

How can i get current username in PL/SQL?

1 Answers1