[stoganv]

Hello folks,

My first post here.

I've started to dig into the internals of VPD within OTM and I'm trying to work out how it all hangs together.

I've come across something which doesn't quite make sense to me. Here's what I've found.

Location table :

- It has 2 policies for read
1. VPDWREXT_PREDICATE_READ584
2. VPD.EXTERNAL_PREDICATE_RULE_READ

The first one has a policy_type of dbms_rls.shared_context_sensitive while the second one has dbms_rls.dynamic. The first one basically does a call to VPD.EXTERNAL_PREDICATE_RULE_READ. Why are we doing the same thing twice? Doesn't the second dynamic policy of dynamic make it reparse the statement each time which basically means there's no reason for the first one?

I've run a seesion trace with 10046 and I can see the functions being called.

Anyone know why it works this way?

I'm about to raise a service request with Oracle to get some more info... oh, this is 5.5 and it's a 10.2.0.3 database on Linux. I can see similar stuff in 5 on 9.2.0.8.

Regards,

Stojan.

[stoganv]

For those interested I can see that they have changed the default policy group in 5.5 or thereabouts.

In our 5.0b systems when you call the procedure vpd.set_user the default policy group is DYNAMIC. In 5.5 the default policy group changes to STATIC. We are seeing a few policy predicate errors due to this as 5.5 and 10g has policies which are shared context sensitive.

I've rasied a service request with Oracle to explain why we are executing the policies twice but it could be related to the above even though we see it on old and new.

[chrisplough]

I'd be curious to see the results of your SR. Please post back when done

--Chris

[stoganv]

My take on how policies are executed wasn't right and I was getting strange results due the database i was testing on being hosed.

I can't remember off the top of my head but there's some sort of context you set to tell Oracle to only run static or dynamic policies via the policy groups. OTM changed the default behaviour of the vpd.set_user function to be static sometime around 5.5.

It still doesn't explain the policy predicate errors we are seeing intermittently in production which I originally thought was due to policies being executed twice.