If you tighten up the security in an Oracle 11gR2 database, you may be tempted to remove access to the DBMS_OBFUSCATION_TOOLKIT package from PUBLIC (“
revoke execute on DBMS_OBFUSCATION_TOOLKIT from PUBLIC;“).
This causes an issue, seen in 11gR2 11.2.0.3.
The error was visible in the user traces:
“*** 2012-09-11 20:00:43.692
KEWBMBTA: Maintain BSLN Thresholds failed, check for details.”
This was tracked to an invalid package body DBSNMP.BSLN.
SQL> alter package DBSNMP.BSLN compile body;
Warning: Package Body altered with compilation errors.
SQL> show errors
Errors for PACKAGE BODY DBSNMP.BSLN:
LINE/COL ERROR
-------- -----------------------------------------------------------------
88/5 PL/SQL: Statement ignored
88/19 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
200/7 PL/SQL: Statement ignored
200/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
241/7 PL/SQL: Statement ignored
242/8 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
1332/7 PL/SQL: Statement ignored
1332/21 PLS-00201: identifier 'DBMS_OBFUSCATION_TOOLKIT' must be declared
Re-granting access to the DBMS_OBFUSCATION_TOOLKIT package to the PUBLIC user, fixes the issue again:
SQL> connect / as sysdba
Connected.
SQL> grant execute on DBMS_OBFUSCATION_TOOLKIT to PUBLIC;
Grant succeeded.
SQL> alter package DBSNMP.BSLN compile body;
Package body altered.
