On a few of our production (11g) databases we have a auditors security violation asking for:
Revoke all access to CTXSYS packages from PUBLIC.
My question is if execute on CTXSYS packages is revoked from Public, are there any implications/consequences IF the CTXSYS user is locked &/or CTXAPP role has a bearing? My understanding is that when the databases were created with Oracle Intermedia/cotext option Oracle creates CTXSYS user and at some point these features were not being used, so CTXSYS user was locked. But if the CTXSYS packages have execute granted to PUBLIC, there could be an issue, so either a work around or a security execption may be required.
in all likelyhood if you revoke execute privileges of CTXSYS packages from PUBLIC you will break the Oracle Text feature, check carefully if you don't have any Text indexes.
Officially it is not supported to revoke the default privileges that belong to a product feature, however since a lot of customers want to do this anyway Oracle support
has taken a somewhat more relaxed stance on this topic, the advise being: If you must revoke default privileges from public, it will become your own responsibility
that everything keeps working, this can typically be done by replacing the grants to PUBLIC with some individual grants to (application) schema's that require it.
Please check: note 247093.1 Be Cautious When Revoking Privileges Granted to PUBLIC
Harm ten Napel