Skip to Main Content
  • Questions
  • "SQL Error: ORA-28112: failed to execute policy function": Can't select with VPD.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, TrĂ­.

Asked: May 15, 2017 - 6:04 pm UTC

Last updated: May 21, 2017 - 1:14 am UTC

Version: Oracle 12C

Viewed 10K+ times! This question is

You Asked

I'm a newbie at Oracle.
Im trying use VPD for my exam.
But i could not select with other user.
You can see my code above.
Sorry about my english.

with LiveSQL Test Case:

and Connor said...

OK, lets look at the important part of your function

Begin 
    user := SYS_CONTEXT('userenv', 'SESSION_USER'); 
    select maPhong into v_maPhong from nhanvien where maNv = user; 
    return 'maPhong = ' || v_maPhong; 
End; 


The string that comes back will be added as a WHERE clause to your query. Let's say that v_maPhong comes back as CONNOR.

So a query such as:

select * from NHANVIEN

will now read like:

select * from NHANVIEN
where maPhong = CONNOR

That is wrong, because there is no column called CONNOR. You need to take care of the quotes as well. So a function like:

Begin 
    user := SYS_CONTEXT('userenv', 'SESSION_USER'); 
    select maPhong into v_maPhong from nhanvien where maNv = user; 
    return 'maPhong = ''' || v_maPhong || ''''; 
End; 


would return

maPhong = 'CONNOR'

which should then work in your query.


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.