Oracle Database 23c comes with 3 new (supplied) PL/SQL packages which are:
DBMS_HCHECK
DBMS_SEARCH
DBMS_SQL_FIREWALL
Here is a short description of what they can be used for:
1. DBMS_HCHECK is a read-only and lightweight PL/SQL package that helps you identify database dictionary inconsistencies that are manifested in unexpected entries in the RDBMS dictionary tables or invalid references between dictionary tables.
Database dictionary inconsistencies can cause process failures and, in some cases, instance crash: such inconsistencies may be exposed to internal ORA-600 errors.
DBMS_HCHECK assists you in identifying such inconsistencies and in some cases provides guided remediation to resolve the problem and avoid such database failures.
The execution reports the result as: CRITICAL: Requires an immediate fix; FAIL: Requires resolution on priority; WARN: Good to resolve; PASS: No issues.
There are 66 procedures in the package, the 2 most important being CRITICAL and FULL. Here is how to run them:
In all cases, any output reporting “problems” must be triaged by Oracle Support to confirm if any action is required.
2. The DBMS_SEARCH package can be used for indexing of multiple schema objects in a single index.
A ubiquitous search index is a JSON search index, with predefined set of preferences required for searching documents with contents in different columns, tables, and views.
You can add a set of tables and views as data sources into this index and all the columns in the specified sources are indexed, and available for a full-text and range-based search. In the example below I am using 2 tables called: DBA_ACE ad CONTRIBUTIONS:
This procedure combines the contents from all columns of the DBA_ACE and CONTRIBUTIONS tables into a single index table named DBAS.
View the virtual indexed doc using the following command: select DBMS_SEARCH.GET_DOCUMENT(‘DBAS’,METADATA ) from DBAS;
Here is an example on how to search multiple objects using ubiquitous search index.
3. DBMS_SQL_FIREWALL can be used to prevent SQL injection attacks.
SQL Firewall implements database firewall features such as allow-listing, deny-listing, and object- and command-based access control inside the Oracle Database kernel.
To implement SQL Firewall, the following features are available:
1. New PL/SQL package, DBMS_SQL_FIREWALL
2. New system privilege, ADMINISTER FIREWALL
3. A set of data dictionary views, DBA_SQL_FIREWALL_*
4. Two roles: SQL_FIREWALL_ADMIN and SQL_FIREWALL_VIEWER
Oracle recommends that you periodically purge capture or violations logs by using the DBMS_SQL_FIREWALL.PURGE_LOG procedure as part of routine SQL Firewall management tasks. In a well trained environment, violation logs are not expected to be voluminous. Here are details on how to configure the SQL Firewall.
Note: There are now about 1100 supplied packages in the Oracle databases (depending on if we count the ones without a BODY too). DBMS_HCHECK and DBMS_SQL_FIREWALL are available only in 23.2
Read MoreJulian Dontcheff’s Database Blog
