Use SA_USER_NAME in function

Question

I tried to create a simple function like this but it not working. Data is returned but it's the same as if there were no policy.

CREATE OR REPLACE FUNCTION test (schema in varchar2, tab in varchar2)
RETURN varchar2 AS
   predicate varchar2(4000);
   pre2 VARCHAR2(2000);
BEGIN
   pre2 := sa_session.SA_USER_NAME('policy');
   predicate := '"USER" = '''|| pre2 ||'''';
   return (predicate);
END test;
/

I will use this function with a VPD policy so when a user selects from a table, they can select only their row.

there are no error. just nothing happen. it work same as did not have that policy — Vũ Đình Hùng, Jun 12 '13 at 15:24
By "nothing happen" do you mean you get a `NULL` return value? What happens when you execute the query `SELECT sa_session.sa_user_name('policy') FROM DUAL` from SQLPlus (or Toad or SQL Developer)? — Ed Gibbs, Jun 12 '13 at 15:27
when I execute the query SELECT sa_session.sa_user_name('policy') FROM DUAL; it return the user name of database user that I'm using. — Vũ Đình Hùng, Jun 12 '13 at 15:43
"nothing happen" that mean I want that function and VPD policy add to my query "where "user"= 'value of pre2'" but the query still return all of my table. — Vũ Đình Hùng, Jun 12 '13 at 15:45
Are you _sure_ that your policy name is `policy`? Also, you're passing two unneeded variables into your function... — Ben, Jun 12 '13 at 15:52
my policy has another name, just use policy as an example here. I'm sure that I use correct name of the policy in my function. How can I check what my function return? — Vũ Đình Hùng, Jun 12 '13 at 16:01

score 0 · Answer 1 · answered Jun 12 '13 at 19:04

The problem is this:

"I execute the query SELECT sa_session.sa_user_name('policy') FROM DUAL; it return the user name of database user that I'm using"

USER is the SQL pseudo-column which also returns "the user name of database user". So basically you predicate evaluates to ...

where 1 = 1

... and that's why it doesn't restrict the result set.

What you need is a predicate which applies the policy name to the table column. So if your table column is USER_NAME it would be:

BEGIN
   pre2 := sa_session.SA_USER_NAME('policy');
   predicate := ' user_name = '''|| pre2 ||'''';
   return (predicate);
END test;

Now perhaps your table has USER as a column name. That would be unfortunate (and astoundingly bad practice) but you might be able to salvage the situation with:

BEGIN
   pre2 := sa_session.SA_USER_NAME('policy');
   predicate := tab||'.user = '''|| pre2 ||'''';
   return (predicate);
END test;

Use SA_USER_NAME in function

1 Answers1