Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, July 19, 2007

Security best practice: Limited grants for APPLSYSPUB

In its 11i security quick reference (http://www.integrigy.com/security-resources/whitepapers/Integrigy_Oracle_11i_Security_Quick_Ref.pdf/view)
Integrigy, the security specialist company has recommended that APPLSYSPUB user should have only the following grants:

INSERT ON FND_UNSUCCESSFUL_LOGINS
INSERT ON FND_SESSIONS
EXECUTE ON FND_DISCONNECTED
EXECUTE ON FND_MESSAGE
EXECUTE ON FND_PUB_MESSAGE
EXECUTE ON FND_SECURITY_PKG
EXECUTE ON FND_SIGNON
EXECUTE ON FND_WEBFILEPUB
SELECT ON FND_APPLICATION
SELECT ON FND_APPLICATION_TL
SELECT ON FND_APPLICATION_VL
SELECT ON FND_LANGUAGES_TL
SELECT ON FND_LANGUAGES_VL
SELECT ON FND_LOOKUPS
SELECT ON FND_PRODUCT_GROUPS
SELECT ON FND_PRODUCT_INSTALLATIONS

These permissions are set in –
/admin/sql/afpub.sql

To check permissions –
SELECT * FROM dba_tab_privs
where grantee = 'APPLSYSPUB'

I opened $FND_TOP/admin/sql/afpub.sql and checked it:

REM Sample Syntax: START afpub applsys fnd applpub pub

which is incorrect because there is no user called applpub, so I changed it to :

@afpub applsys fnd applsyspub pub

The script was executed without issues. But in a minute or so, I was informed that none of the forms were opening and users were getting an error window like this:

APP-FND-01564: ORACLE error 980 in fdulictx

Cause: fdulictx failed due to ORA-00980 synonym translation is no longer valid

The SQL statement being executed at the time of the error was:
select LANGUAGE_CODE,DESCRIPTION, INSTALLED_FLAG
from FND_LANGUAGES_VL
where INSTALLED_FLAG in ('I','B')
order by LANGUAGE_CODE
and was executed from the file &ERRFILE.

On pressing Ok, a new error window would show this message:

FRM-10221: Cannot read file $AU_TOP/resource/stub/FNDM...

I realised that I should not have run that script when the instance was up and running. After informing users, I immediately shutdown the instance, started adadmin and ran "Recreate GRANTS and synonyms" after which I ran "Compile APPS schema". Once these tasks were complete, I restarted all the services. The problem had disappeared. While running "Recreate grants and synonyms", I saw that afpub.sql was being called by it. So, if you do "Recreate grants and synonyms" with adadmin, the security recommendation is automatically implemented.

No comments: