ORA-04063: package body "DBSNMP.BSLN" has errors
问题描述：
警告日志出现报错：
Sun Jun 28 00:00:01 2020 Errors in file /u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_j000_8274.trc: ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" ORA-04063: package body "DBSNMP.BSLN" has errors ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN" ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1
问题类似我之前不久的一片博文里边描述的问题，ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors。
虽然不再搜索到同样的官方处理方式，不过其实根因是一样的。
问题原因：
1.BSLN包体对象处于无效状态。
select owner,object_name,object_type,status from dba_objects where owner='DBSNMP' and object_name='BSLN'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------- ------------------------------ --------------------------------------------------------- ---------- DBSNMP BSLN PACKAGE VALID DBSNMP BSLN PACKAGE BODY INVALID
处理过程
08:26:15 sys@xxxxxxi1(xxxxxx1)> alter package DBSNMP.BSLN compile body; Warning: Package Body altered with compilation errors. Elapsed: 00:00:00.10 08:27:36 sys@xxxxxxi1(xxxxxx1)> show error 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 08:39:51 sys@xxxxxx(xxxxxx2)> select * from dba_sys_privs where grantee='DBSNMP'; GRANTEE PRIVILEGE ADM ------------ -------------------------------------------------- --- DBSNMP CREATE PROCEDURE NO DBSNMP UNLIMITED TABLESPACE NO DBSNMP SELECT ANY DICTIONARY NO DBSNMP CREATE TABLE NO Elapsed: 00:00:00.00 08:39:52 sys@xxxxxx(xxxxxx2)> select * from dba_role_privs where grantee='DBSNMP'; GRANTEE GRANTED_ROLE ADM DEF ------------ ------------------------------ --- --- DBSNMP OEM_MONITOR NO YES Elapsed: 00:00:00.02 08:40:05 sys@xxxxxx(xxxxxx2)> select * from dba_tab_privs where grantee='DBSNMP'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------ ---------- ----------------------------------- ------------ -------------------------------------------------- --- --- DBSNMP SYS DBMS_SERVER_ALERT SYS EXECUTE NO NO DBSNMP SYS DBMS_JOB SYS EXECUTE NO NO DBSNMP SYS DBMS_MANAGEMENT_PACKS SYS EXECUTE NO NO DBSNMP APPQOSSYS WLM_CLASSIFIER_PLAN APPQOSSYS SELECT NO NO DBSNMP APPQOSSYS WLM_METRICS_STREAM APPQOSSYS SELECT NO NO DBSNMP APPQOSSYS WLM_MPA_STREAM APPQOSSYS SELECT NO NO DBSNMP APPQOSSYS WLM_VIOLATION_STREAM APPQOSSYS SELECT NO NO 7 rows selected. Elapsed: 00:00:00.05 08:40:15 sys@xxxxxx(xxxxxx2)> select * from dba_tab_privs where grantee='OEM_MONITOR'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------ ---------- ----------------------------------- ------------ -------------------------------------------------- --- --- OEM_MONITOR SYS ALERT_QUE SYS DEQUEUE NO NO OEM_MONITOR SYS DBMS_SYSTEM SYS EXECUTE NO NO OEM_MONITOR SYS DBMS_SERVER_ALERT SYS EXECUTE NO NO OEM_MONITOR SYS DBMS_MONITOR SYS EXECUTE NO NO OEM_MONITOR SYS DBMS_AQ SYS EXECUTE NO NO OEM_MONITOR SYS DBMS_AQADM SYS EXECUTE NO NO OEM_MONITOR SYS DBMS_WORKLOAD_REPOSITORY SYS EXECUTE NO NO OEM_MONITOR DBSNMP BSLN DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_BASELINES DBSNMP SELECT NO NO OEM_MONITOR DBSNMP BSLN_INTERNAL DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_METRIC_DEFAULTS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP BSLN_METRIC_SET DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_METRIC_T DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_OBSERVATION_SET DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_OBSERVATION_T DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_STATISTICS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP BSLN_STATISTICS_SET DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_STATISTICS_T DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_THRESHOLD_PARAMS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP BSLN_TIMEGROUPS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP BSLN_VARIANCE_SET DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP BSLN_VARIANCE_T DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP MGMT_BASELINE DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BASELINE_SQL DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_BASELINES DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_DATASOURCES DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_INTERVALS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_METRICS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_STATISTICS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_BSLN_THRESHOLD_PARMS DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_HISTORY DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_HISTORY_SQL DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_LATEST DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_LATEST_SQL DBSNMP SELECT NO NO OEM_MONITOR DBSNMP MGMT_RESPONSE DBSNMP EXECUTE NO NO OEM_MONITOR DBSNMP MGMT_UPDATE_DB_FEATURE_LOG DBSNMP EXECUTE NO NO OEM_MONITOR SYS DBMS_DRS SYS EXECUTE NO NO 37 rows selected. Elapsed: 00:00:00.04 08:43:46 sys@xxxxxx(xxxxxx2)> select owner,object_name,object_type from dba_objects where object_name='DBMS_OBFUSCATION_TOOLKIT'; OWNER OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------- SYS DBMS_OBFUSCATION_TOOLKIT PACKAGE BODY SYS DBMS_OBFUSCATION_TOOLKIT PACKAGE PUBLIC DBMS_OBFUSCATION_TOOLKIT SYNONYM Elapsed: 00:00:00.00 08:43:52 sys@xxxxxx(xxxxxx2)> grant execute on sys.DBMS_OBFUSCATION_TOOLKIT to DBSNMP; Grant succeeded. Elapsed: 00:00:00.02 08:44:19 sys@xxxxxx(xxxxxx2)> alter package DBSNMP.BSLN compile body; Package body altered. Elapsed: 00:00:00.23 08:44:42 sys@xxxxxx(xxxxxx2)> select * from dba_tab_privs where table_name='DBMS_OBFUSCATION_TOOLKIT'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------ ---------- ----------------------------------- ------------ -------------------- --- --- DBSNMP SYS DBMS_OBFUSCATION_TOOLKIT SYS EXECUTE NO NO
深入分析
根据此次问题和上次的问题，原本以为出于安全原因，删除了某些包上的public特权是Oracle随着版本的自身的行为。
但是其实不是这样，此次版本为11.2.0.4.0。在发生问题的生产环境上，某些包确实没有了public的特权。
但是我看了其他同样是11.2.0.4.0的环境，确发现这些权限还在。
那么原因只有两个：
- 建库之后，由于企业的生产规范要求，按照Oracle安全白皮书中的建议撤消了某些包的公开的执行特权。
- 可能是建库的时候存在一些特殊的配置。
第一个的可能性更大一些。因为Oracle确实有mos文档和官方文档建议撤销一些包的public权限。
比如官方文档：Granting to and Revoking from the PUBLIC Role
比如：Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks (Doc ID 1545816.1)中。
Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks (Doc ID 1545816.1) In this Document Goal Solution Step 1: Change passwords for SYS and SYSTEM Step 2: Lock, expire, and change passwords for default or unused accounts Step 3: Restrict access to the Oracle home and installation files Step 4: Review database user privileges Step 5: Revoke privileges from PUBLIC where not necessary Step 6: Protect the data dictionary from unauthorized users Step 7: Set security related parameters to their recommended values a. remote_os_authent = false b. sec_case_sensitive_logon = true c. global_names = true d. unset parameter utl_file_dir Step 8: Protect listener and network connections Automatic instance registration and CVE-2012-1675 Encrypt sqlnet connections using network encryption. Step 9: Protect the database host Step 10: Check Oracle websites for Security Alerts and critical patches Other Items to Consider Further Reading Online Discussion (My Oracle Support Community) References
对应步骤5内容，
Step 5: Revoke privileges from PUBLIC where not necessary The PUBLIC role is automatically assumed by every database user account. By default, it has no privileges granted to it, but it does have numerous grants, mostly to Java objects. Because all users have the PUBLIC role, any database user can exercise privileges that are granted to this role. Security administrators and database users should grant a privilege or role to PUBLIC only if every database user requires the privilege or role. Note 247093.1 - Be Cautious When Revoking Privileges Granted to PUBLIC Note 234551.1 - PUBLIC Is it a User, a Role, a User Group, a Privilege ? Note 390225.1 - Execute Privileges Are Reset For Public After Applying Patchset
其中，Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)一文中指出，
Of course this does not cover ad-hoc usage from anonymous blocks or dynamic sql, typically it would only be an application owner schema that needs the direct grant. Building on the above example you can create targeted grants if you revoke public privileges for the most common packages ( for example based on EM Policy violations ) REVOKE execute ON SYS.DBMS_RANDOM from public; REVOKE execute ON SYS.DBMS_EXPORT_EXTENSION from public; REVOKE execute ON SYS.UTL_FILE from public; REVOKE execute ON SYS.DBMS_JOB from public; REVOKE execute ON SYS.DBMS_LOB from public; REVOKE execute ON SYS.UTL_SMTP from public; REVOKE execute ON SYS.UTL_TCP from public; REVOKE execute ON SYS.UTL_HTTP from public; We can generate the following replacement grant statements based on the current dependencies (again this does not cover the use from dynamic sql, since that is not recorded in DBA_DEPENDENCIES): select unique statements from ( select 'grant execute on '|| referenced_name||' to '||owner||';' statements from dba_dependencies where referenced_owner in ('SYS','PUBLIC') and referenced_type in ('PACKAGE','SYNONYM') and referenced_name in ('DBMS_RANDOM','DBMS_EXPORT_EXTENSION','UTL_FILE', 'DBMS_JOB','DBMS_LOB','UTL_SMTP','UTL_TCP','UTL_HTTP') and owner <> 'SYS' and owner <> 'PUBLIC') order by statements;
上边只是列举了常见的一些包，并不包含此次故障的DBMS_OBFUSCATION_TOOLKIT，使用脚本更改谓词条件，
10:46:02 sys@xxxxxx1(xxxxx1)> select unique statements from ( 10:46:39 2 select 'grant execute on '|| 10:46:39 3 referenced_name||' to '||owner||';' statements 10:46:39 4 from dba_dependencies 10:46:39 5 where 10:46:39 6 referenced_owner in ('SYS','PUBLIC') and 10:46:39 7 referenced_type in ('PACKAGE','SYNONYM') and 10:46:39 8 referenced_name in 10:46:39 9 ('DBMS_OBFUSCATION_TOOLKIT','DBMS_JOB') and 10:46:39 10 owner <> 'SYS' and 10:46:39 11 owner <> 'PUBLIC') 10:46:39 12 order by statements; STATEMENTS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ grant execute on DBMS_JOB to DBSNMP; grant execute on DBMS_OBFUSCATION_TOOLKIT to DBSNMP; grant execute on DBMS_OBFUSCATION_TOOLKIT to DYCK_PUB; grant execute on DBMS_OBFUSCATION_TOOLKIT to DYCK_VIEW; Elapsed: 00:00:00.04
因此，若是撤销某个包的public权限，需要检测是否有其他联级对象将会失效，此刻需要使用mos文档提供的脚本授权。
至于其他当初显式撤销public的包，暂时不知道有哪些，只能遇到报错的时候在处理了。
应该是当初撤销权限的人没有处理联级对象失效的问题，操作不规范。
后续
询问了当初数据库是否存在相关操作，果然有过回收动作。
回收的原因是由于等保测评软件DBAPPSecurity扫描之后的建议，部分截图如下：
像这类等保测评软件，在数据库方面都是半桶水的水平。
像这个地方这样，检测出包被授权给public执行权限，修复建议中仅仅只是回收而已。
没有考虑回收之后，其他地方可以因为回收出现的问题。
绿盟软件则更那个，打了补丁的漏洞都能扫出来说没打。
关于绿盟，看看业内比较权威人士的看法吧！