11i: How to Check for Correct APPLSYSPUB Privileges in 11i

The APPLSYSPUB account is used by Oracle Applications to initially connect to the database and establish a session.  This account normally should have limited privileges.  However, during our audits the permissions assigned to APPLSYSPUB and PUBLIC are often a security risk and need to be corrected.

Review APPLSYSPUB Privileges

The first step is to review the object permissions granted to APPLSYSPUB by APPS.  These grants are made by "Recreate Grants and Synonyms" in ADADMIN, which executes the script $FND_TOP/admin/sql/afpub.sql. This SQL will create the correct grants, however, it will not remove any unnecessary grants.  Critical Patch Update July 2005 included Oracle Patch 4074867, which removed unnecessary grants in 11.5.1 to 11.5.9.

The following SQL statement will list all the grants from APPS to APPLSYSPUB -

SELECT * FROM dba_tab_privs
WHERE grantee = 'APPLSYSPUB'
AND grantor = 'APPS'
ORDER BY table_name

Compare the results with the list of required grants in the below table.  Remove all unnecessary grants.

Required APPLSYSPUB Privileges (11.5.1 - 11.5.10.2)

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_lookups
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_product_groups 
select on fnd_product_installations 

See $FND_TOP/admin/sql/afpub.sql for exact list of grants in your implementation and version of Oracle Applications.

A common grant that should be removed is "SELECT on FND_USER_VIEW". 
FND_USER_VIEW displays the encrypted foundation passwords, which may be
decrypted to reveal the APPS password. FND_USER_VIEW is no longer required in 11i.  As part of the upgrade
process, the FNDDOLD.sql should have been executed.  This script is
often missed and not executed as part of the upgrade process,
therefore, privileges on FND_USER_VIEW must be manually revoked.  It is important to note that some older versions of ADI require FND_USER_VIEW.  See Metalink Note ID 237917.1 for more information.

Review PUBLIC Privileges

Frequently the privileges granted to PUBLIC are incorrect, particularly for custom objects.  The following SQL statement will list all the grants made to PUBLIC for non-system type objects -

SELECT * FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
AND owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS', 'OWAPUB', 'MDSYS', 'ORDPLUG', 'ORDSYS', 'ORDPLUGINS', 'CSMIG')
ORDER BY table_name

A number of packages, types, and Java classes will be listed.  Concentrate on any custom developed objects, especially packages, procedures, or functions.

Custom developed objects - other than views and packages - will reside in custom schemas.  All privileges should be granted to APPS rather than PUBLIC.  It is common during development to grant to PUBLIC rather than APPS to ensure a custom program will work.