[SOLVED] VPD Context with VPD_GRANULARITY=D

[chrisplough]

A client was recently trying to improve OTM saved query and reports performance by optimizing VPD within OTM. One of the ways to do this is to decrease the granularity of VPD, so that fewer calls need to be made during a query, by setting VPD_GRANULARITY=D.

After making this change initially, they found an issue where users with the standard security policy started experiencing the following errors:

Error Exception insert into
gl_login_history (gl_user_login_attempt, login_sequence, login_status,
login_mode, ip_address, login_time, domain_name) values (?,
gl_login_history_sequence.nextval, ?, ?, ?, sysdate, ?); [GUEST.ADMIN,
Authenticated - Password Expired, Interactive, null, GUEST] [ExecuteThread: '42'
for q]
2007-01-29 10:48:05.837 0 Error Exception java.sql.SQLException:
ORA-28115: policy with check option violation
java.sql.SQLException: ORA-28115: policy with check option violation

This seemed to be occurring because DBA.ADMIN was not able to see the record and therefore it attempted to write a duplicate record. Oracle support came up with a workaround for this issue, by adding a row to domain_grants_made that gives DBA domain read/write on the ALL_TS table set.

However, after this issue, they found another side effect of VPD_GRANULARITY=D: This setting interferes with the SERVPROV VPD profile such that the SERVRPOV domain users automatically created when a servprov is created do not have the expected visibility to shipments (in fact, they cannot see any).

I'm not currently aware of any resolution for this issue and just wanted to post this for information purposes.

Hope this helps!

--Chris