Looking for tips on debugging Oracle row-level security functions

Question

I'm looking for tips in debugging some of my row-level security predicates in an Oracle database. These predicates use a few concepts to determine whether the current user can see a record:

• current user's Oracle username
• current user's assigned Oracle roles
• current user's affiliation with a record in one or more tables

I'm having trouble debugging this kind of thing on real data because I can't figure out a good way to simulate actually seeing what a specific user could see. So, I'm looking for tips. Is there a good basic framework for this kind of thing?

Here's an example of one of my predicates:

predicate := 'project_id in (' ||
    '(select upr.projectid project_id ' ||
    'from  chemreg.usergroups_projects_vu upr, ' ||
    '      chemreg.usergroups_personnel_vu upe, ' ||
    '      chemreg.personnel pe ' ||
    'where upr.usergroupid = upe.usergroup_id ' ||
    '      and upe.personnel_id = pe.person_id ' ||
    '      and upper(pe.username) = USER) ' ||
    'union ' ||
    '(select project_id from chemreg.project ' ||
    'where active = ''Y'' and private = ''N'' ) )';

Answer 1

If you're trying to work out why some rows are appearing when they shouldn't, and/or why some rows are not appearing when they should, try this:

1. Remove all the row-level security predicates.
2. Run the queries, but add in the row-level security predicates by hand.
3. Check the results.

You can then easily change the predicates one by one (e.g. comment out individual bits) until you work out why they are giving the unexpected results.