APPS Schema security changes delivered with HR EOY Phase I patch 8947666 for R11i

As an APPS DBA, there are occurences where an application patch will do things you don't expect. That is why reviewing the readme document and performing regression testing is important.


Some odd behaviour was seen when applying the HR EOY Phase I patch (8947666) for 2009. A customization in place that required the APPS account to access a data dictionary table stopped working after the patch was applied. After a little research the issue was traced to some security changes Oracle bundled with the EOY Phase I patch.

The EOY Phase I patch includes a call to adrevdba.sql in the directory $AD_TOP/patch/115/sql. This script will revoke some grants from the APPS user. The specific commands run are:


revoke dba from apps;

revoke select any dictionary from apps;


These grants are not required for functioning of the application. The grants are being revoked by Oracle to help secure the application. If these grants used by any custom work, they will need to be granted after the patch has been applied. Future patches may also revoke the grants, so they will need to be monitored after patching to validate they still exist.

The custom code using these grants should be reviewed to determine if the grants are neccessary. Changes should be made to the code to work around the requirement for the grants. This will allow for a more secured application.