create or replace package method5.method5_admin authid current_user is

function generate_remote_install_script

(

p_allow_run_as_sys varchar2 default 'YES',

p_allow_run_shell_script varchar2 default 'YES',

--Currently supported values: NORMAL and RDS.

p_database_type varchar2 default 'NORMAL'

) return clob;

procedure set_local_and_remote_sys_key(p_db_link in varchar2);

function set_all_missing_sys_keys return clob;

function generate_password_reset_one_db return clob;

function generate_link_test_script(p_link_name varchar2, p_database_name varchar2, p_host_name varchar2, p_port_number number) return clob;

procedure drop_m5_db_links_for_user(p_username varchar2);

procedure drop_m5_db_link_for_m5(p_db_link varchar2);

procedure refresh_m5_ptime(p_targets varchar2);

function refresh_all_user_m5_db_links return clob;

procedure change_m5_user_password;

procedure change_remote_m5_passwords;

procedure change_local_m5_link_passwords;

procedure send_daily_summary_email;

end;

/

create or replace package body method5.method5_admin is

--Copyright (C) 2019 Jon Heller, Ventech Solutions, and CMS. This program is licensed under the LGPLv3.

--See https://method5.github.io/ for more information.

/******************************************************************************

* See administer_method5.md for how to use these methods.

******************************************************************************/

--------------------------------------------------------------------------------

--Purpose: Stop from running in SQL*Plus.

--SQL*Plus is great for running scripts but it sucks for displaying lots of data.

procedure do_not_allow_sqlplus is

v_program varchar2(4000);

begin

execute immediate

q'[

select program

from v$session

where audsid = sys_context('userenv', 'sessionid')

]'

into v_program;

if lower(v_program) like '%sqlplus%' then

dbms_output.enable;

dbms_output.put_line(' ______ _____ _____ ____ _____ ');

dbms_output.put_line('| ____|| __ \ | __ \ / __ \ | __ \ ');

dbms_output.put_line('| |__ | |__) || |__) || | | || |__) | ');

dbms_output.put_line('| __| | _ / | _ / | | | || _ / ');

dbms_output.put_line('| |____ | | \ \ | | \ \ | |__| || | \ \ ');

dbms_output.put_line('|______||_| \_\|_| \_\ \____/ |_| \_\ ');

raise_application_error(-20000,

'Run this step in a GUI, such as Oracle SQL Developer or Toad. SQL*Plus is not good at displaying large amounts of data.');

end if;

end do_not_allow_sqlplus;

--------------------------------------------------------------------------------

--Purpose: Return '.'||DB_DOMAIN if the DB_DOMAIN is set.

function get_db_domain_suffix return varchar2 is

v_db_domain varchar2(4000);

begin

--DB_DOMAIN from V$PARAMETER.

--The DB_DOMAIN changes all the database links if it exists.

select value

into v_db_domain

from v$parameter

where name = 'db_domain';

return case when v_db_domain is null then null else '.' || upper(v_db_domain) end;

end get_db_domain_suffix;

--------------------------------------------------------------------------------

--Purpose: Get a clean link name - trim whitespace, convert to upper case, and

-- remove everything after the first period to remove any current or old DB_DOMAIN.

function get_trim_upper_clean_link_name(p_db_link varchar2) return varchar2 is

begin

return trim(upper(regexp_replace(p_db_link, '\..*')));

end get_trim_upper_clean_link_name;

--------------------------------------------------------------------------------

--Purpose: Generate a script to install Method5 on remote databases.

function generate_remote_install_script

(

p_allow_run_as_sys varchar2 default 'YES',

p_allow_run_shell_script varchar2 default 'YES',

--Currently supported values: NORMAL and RDS.

p_database_type varchar2 default 'NORMAL'

) return clob

is

v_script clob;

function create_header return clob is

begin

return replace(

q'[

----------------------------------------

--Install Method5 on remote target.

--Run this script as SYS using SQL*Plus.

--Do NOT save this output - it contains password hashes and should be regenerated each time.

--(This script is intended for non-RDS databases.)

----------------------------------------

]'

, ' ', null)||chr(10);

end create_header;

function create_rds_header return clob is

begin

return replace(

q'[

----------------------------------------

--Install Method5 on remote target.

--Run this script as a user with the DBA role, using SQL*Plus.

--Do NOT save this output - it contains password hashes and should be regenerated each time.

--(This script is intended for RDS databases.)

----------------------------------------

]'

, ' ', null)||chr(10);

end create_rds_header;

function create_check_is_sys return clob is

begin

return replace(replace(

q'[

--Check the user. This script will only work as SYS.

whenever sqlerror exit;

begin

if user <> 'SYS' then

raise_application_error(-20000,

'This step must be run as SYS.'||chr(13)||chr(10)||

'Logon as SYS and re-run.');

end if;

end;

#SLASH#

]'

, chr(10)||' ', chr(10))||chr(10)

, '#SLASH#', '/');

end create_check_is_sys;

function create_check_is_rds_dba return clob is

begin

return replace(replace(

q'[

--Check the user. This script will only work for DBA with ADMIN option.

whenever sqlerror exit;

declare

v_has_dba_role number;

v_has_admin_option number;

begin

--Check that the current user has been granted DBA with the ADMIN_OPTION.

select

count(*) has_dba_role,

sum(case when admin_option = 'YES' then 1 else 0 end) has_admin_option

into v_has_dba_role, v_has_admin_option

from dba_role_privs

where grantee = user

and granted_role = 'DBA';

if v_has_dba_role = 0 then

raise_application_error(-20000, 'Your user must have the DBA role to install Method5.');

elsif v_has_admin_option = 0 then

raise_application_error(-20000, 'Your user must have the DBA role granted with the ADMIN option to install Method5.');

end if;

end;

#SLASH#

]'

, chr(10)||' ', chr(10))||chr(10)

, '#SLASH#', '/');

end create_check_is_rds_dba;

function create_profile return clob is

v_profile varchar2(128);

begin

--Find the current Method5 profile.

select profile

into v_profile

from dba_users

where username = 'METHOD5';

--Create profile if it doesn't exist.

return replace(replace(q'[

--Create the profile that Method5 uses on the management server, if it doesn't exist.

declare

v_count number;

begin

select count(*) into v_count from dba_profiles where profile = '#PROFILE#';

if v_count = 0 then

execute immediate 'create profile #PROFILE# limit cpu_per_call unlimited';

end if;

end;

/]'||chr(10)||chr(10)

, '#PROFILE#', v_profile)

, ' ', null);

end;

function create_user return clob is

v_12c_hash varchar2(4000);

v_11g_hash_without_des varchar2(4000);

v_11g_hash_with_des varchar2(4000);

v_10g_hash varchar2(4000);

v_profile varchar2(4000);

begin

sys.get_method5_hashes(v_12c_hash, v_11g_hash_without_des, v_11g_hash_with_des, v_10g_hash);

select profile

into v_profile

from dba_users

where username = 'METHOD5';

return replace(replace(replace(replace(replace(replace(

q'[

--Create the Method5 user with the appropriate hash.

declare

v_sec_case_sensitive_logon varchar2(4000);

v_method5_user_count number;

begin

select upper(value)

into v_sec_case_sensitive_logon

from v$parameter

where name = 'sec_case_sensitive_logon';

select count(*)

into v_method5_user_count

from dba_users

where username = 'METHOD5';

--Do nothing if the user already exists.

if v_method5_user_count >= 1 then

null;

else

--Change the hash for 10g and 11g.

$if dbms_db_version.ver_le_11_2 $then

if v_sec_case_sensitive_logon = 'TRUE' then

execute immediate q'!create user method5 profile #PROFILE# identified by values '#11G_HASH_WITHOUT_DES#'!';

else

if '#10G_HASH#' is null then

raise_application_error(-20000, 'The 10g hash is not available. You must set '||

'the target database sec_case_sensitive_logon to TRUE for this to work.');

else

execute immediate q'!create user method5 profile #PROFILE# identified by values '#11G_HASH_WITH_DES#'!';

end if;

end if;

--Change the hash for 12c.

$else

execute immediate q'!create user method5 profile #PROFILE# identified by values '#12C_HASH#'!';

$end

end if;

end;

/]'

, '#12C_HASH#', nvl(v_12c_hash, v_11g_hash_without_des))

, '#11G_HASH_WITHOUT_DES#', v_11g_hash_without_des)

, '#10G_HASH#', v_10g_hash)

, '#11G_HASH_WITH_DES#', v_11g_hash_with_des)

, '#PROFILE#', v_profile)

, chr(10)||' ', chr(10))||chr(10)||chr(10);

end;

function create_grants return clob is

begin

return replace(replace(q'[

--REQUIRED: Create and grant role of minimum Method5 remote target privileges.

--Do NOT remove or change this block or Method5 will not work properly.

declare

v_role_conflicts exception;

pragma exception_init(v_role_conflicts, -1921);

begin

begin

execute immediate 'create role m5_minimum_remote_privs';

exception when v_role_conflicts then null;

end;

execute immediate 'grant m5_minimum_remote_privs to method5';

execute immediate 'grant create session to m5_minimum_remote_privs';

execute immediate 'grant create table to m5_minimum_remote_privs';

execute immediate 'grant create procedure to m5_minimum_remote_privs';

execute immediate 'grant execute on sys.dbms_sql to m5_minimum_remote_privs';

--These direct privileges are required for the view db_name_or_con_name_vw.

execute immediate 'grant select on sys.v_$instance to method5';

execute immediate 'grant select on sys.v_$database to method5';

end;

#SLASH#

--REQUIRED: Grant Method5 unlimited access to the default tablespace.

--You can change the quota or tablespace but Method5 must have at least a little space.

declare

v_default_tablespace varchar2(128);

begin

select property_value

into v_default_tablespace

from database_properties

where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

execute immediate 'alter user method5 quota unlimited on '||v_default_tablespace;

end;

#SLASH#

--REQUIRED: Create and grant role for additional Method5 remote target privileges.

--Do NOT remove or change this block or Method5 will not work properly.

declare

v_role_conflicts exception;

pragma exception_init(v_role_conflicts, -1921);

begin

begin

execute immediate 'create role m5_optional_remote_privs';

exception when v_role_conflicts then null;

end;

execute immediate 'grant m5_optional_remote_privs to method5';

end;

#SLASH#

--REQUIRED: DBMS_SCHEDULER should already be granted to PUBLIC.

--Some security/audit/hardening scripts may revoke it but only because they are using an

--old version of the DoD STIG (secure technical implementation guidelines). The new version

--does not revoke this public grant anymore.

--It is required to make the SANDBOX users work.

grant execute on sys.dbms_scheduler to public;

--OPTIONAL, but recommended: Grant DBA to Method5 role.

--WARNING: The privilege granted here is the upper-limit applied to ALL users.

-- If you only want to block specific users from having DBA look at the table M5_USER.

--

--If you don't trust Method5 or are not allowed to grant DBA, you can manually modify this block.

--Simply removing it would make Method5 worthless. But you may want to replace it with something

--less powerful. For example, you could make a read-only Method5 with these two commented out lines:

-- grant select any table to m5_optional_remote_privs;

-- grant select any dictionary to m5_optional_remote_privs;

grant dba to m5_optional_remote_privs with admin option;

--OPTIONAL, but recommended: Grant access to a table useful for password management and synchronization.

grant select on sys.user$ to m5_optional_remote_privs;

--OPTIONAL, but recommended: Direct grants for objects that are frequently revoked from PUBLIC, as

--recommended by the Security Technical Implementation Guide (STIG).

--Use "with grant option" since these will probably also need to be granted to users.

begin

for packages in

(

select 'grant execute on '||column_value||' to m5_optional_remote_privs with grant option' v_sql

from table(sys.odcivarchar2list(

'DBMS_ADVISOR','DBMS_BACKUP_RESTORE','DBMS_CRYPTO','DBMS_JAVA','DBMS_JAVA_TEST',

'DBMS_JOB','DBMS_JVM_EXP_PERMS','DBMS_LDAP','DBMS_LOB','DBMS_OBFUSCATION_TOOLKIT',

'DBMS_PIPE','DBMS_RANDOM','DBMS_SCHEDULER','DBMS_SQL','DBMS_SYS_SQL','DBMS_XMLGEN',

'DBMS_XMLQUERY','HTTPURITYPE','UTL_FILE','UTL_HTTP','UTL_INADDR','UTL_SMTP','UTL_TCP'

))

) loop

begin

execute immediate packages.v_sql;

exception when others then null;

end;

end loop;

end;

/]'||chr(10)||chr(10)

,' ', null)

,'#SLASH#', '/');

end create_grants;

--This function is very similar to CREATE_GRANTS.

--But there are some things RDS cannot have, and some things RDS is guaranteed to have and don't need to be re-granted.

function create_rds_grants return clob is

begin

return replace(replace(q'[

--REQUIRED: Grant Method5 unlimited access to the default tablespace.

--You can change the quota or tablespace but Method5 must have at least a little space.

declare

v_default_tablespace varchar2(128);

begin

select property_value

into v_default_tablespace

from database_properties

where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

execute immediate 'alter user method5 quota unlimited on '||v_default_tablespace;

end;

#SLASH#

--NOTE: Permissions work slightly different in RDS than normal databases.

--These privileges must be granted directly to Method5 using the "WITH ADMIN OPTION"

-- or they will not work.

--REQUIRED: Create and grant role of minimum Method5 remote target privileges.

--Do NOT remove or change this block or Method5 will not work properly.

grant create session to method5 with admin option;

grant create table to method5 with admin option;

grant create procedure to method5 with admin option;

--OPTIONAL, but recommended: Grant DBA to Method5 role.

--WARNING: The privilege granted here is the upper-limit applied to ALL users.

-- If you only want to block specific users from having DBA look at the table M5_USER.

--

--If you don't trust Method5 or are not allowed to grant DBA, you can manually modify this block.

--Simply removing it would make Method5 worthless. But you may want to replace it with something

--less powerful. For example, you could make a read-only Method5 with these two commented out lines:

-- grant select any table to method5 with admin option;

-- grant select any dictionary to method5 with admin option;

grant dba to method5 with admin option;]'||chr(10)||chr(10)

,' ', null)

,'#SLASH#', '/');

end create_rds_grants;

function create_audits return clob is

begin

return replace(q'[

--Audit everything done by Method5.

audit all statements by method5;]'||chr(10)

,' ', null);

end;

function create_trigger(p_trigger_owner varchar2) return clob is

v_host_list varchar2(4000);

begin

execute immediate

q'[

select '(' || listagg(''''||lower(host_name)||'''', ',') within group (order by host_name) || ') ' host_list

from gv$instance

]'

into v_host_list;

return replace(replace(replace(q'[

--Prevent Method5 from connecting directly.

create or replace trigger #OWNER#.m5_prevent_direct_logon

after logon on method5.schema

/*

Purpose: Prevent anyone from connecting directly as Method5.

All Method5 connections must be authenticated by the Method5

program and go through a database link.

Note: These checks are not foolproof and it's possible to spoof some

of these values. The primary protection of the Method5 comes from

only using password hashes and nobody ever knowing the password.

This trigger is another layer of protection, but not a great one.

*/

declare

--Only an ORA-600 error can stop logons for users with either

--"ADMINISTER DATABASE TRIGGER" or "ALTER ANY TRIGGER".

--The ORA-600 also generates an alert log entry and may warn an admin.

internal_exception exception;

pragma exception_init( internal_exception, -600 );

procedure check_module_for_link is

begin

--TODO: This is not tested!

if

sys_context('userenv','module') like 'oracle@%'

or

sys_context('userenv','BG_JOB_ID') is not null

then

null;

else

raise internal_exception;

end if;

end;

begin

--Check that the connection comes from the management server.

if sys_context('userenv', 'session_user') = 'METHOD5'

and lower(sys_context('userenv', 'host')) not in #HOST_LIST# then

raise internal_exception;

end if;

--Check that the connection comes over a database link or through a scheduled job.

$if dbms_db_version.ver_le_9 $then

check_module_for_link;

$elsif dbms_db_version.ver_le_10 $then

check_module_for_link;

$elsif dbms_db_version.ver_le_11_1 $then

check_module_for_link;

$else

if

sys_context('userenv', 'dblink_info') is not null

or

sys_context('userenv','BG_JOB_ID') is not null

then

null;

else

raise internal_exception;

end if;

$end

end;

/]'||chr(10)||chr(10)

,'#OWNER#', p_trigger_owner)

,'#HOST_LIST#', v_host_list)

,chr(10)||' ', chr(10));

end;

function create_sys_m5_runner return clob is

v_host_list varchar2(4000);

begin

execute immediate

q'[

select '(' || listagg(''''||lower(host_name)||'''', ',') within group (order by host_name) || ') ' host_list

from gv$instance

]'

into v_host_list;

return replace(replace(replace(

q'[

--Create table to hold Session GUIDs.

declare

v_count number;

begin

select count(*)

into v_count

from dba_tables

where owner = 'SYS'

and table_name = 'M5_SYS_SESSION_GUID';

if v_count = 0 then

execute immediate

'

create table sys.m5_sys_session_guid

(

session_guid raw(16),

constraint m5_sys_session_guid_pk primary key(session_guid)

)

';

execute immediate

q'!

comment on table sys.m5_sys_session_guid is 'Session GUID to prevent Method5 SYS replay attacks.'

!';

end if;

end;

#SLASH#

--Create package to enable remote execution of commands as SYS.

create or replace package sys.m5_runner is

--Copyright (C) 2017 Jon Heller, Ventech Solutions, and CMS. This program is licensed under the LGPLv3.

--Version 1.0.1

--Read this page if you're curious about this program or concerned about security implications:

--https://github.com/method5/method5/blob/master/user_guide.md#security

procedure set_sys_key(p_sys_key in raw);

procedure run_as_sys(p_encrypted_command in raw);

procedure get_column_metadata

(

p_plsql_block in varchar2,

p_encrypted_select_statement in raw,

p_has_column_gt_30 in out number,

p_has_long in out number,

p_explicit_column_list in out varchar2,

p_explicit_expression_list in out varchar2

);

end;

#SLASH#

create or replace package body sys.m5_runner is

/******************************************************************************/

--Throw an error if the connection is not remote and not from an expected host.

procedure validate_remote_connection is

procedure check_module_for_link is

begin

--TODO: This is not tested!

if sys_context('userenv','module') not like 'oracle@%' then

raise_application_error(-20200, 'This procedure was called incorrectly.');

end if;

end;

begin

--Check that the connection comes from the management server.

if sys_context('userenv', 'session_user') = 'METHOD5'

and lower(sys_context('userenv', 'host')) not in #HOST_LIST# then

raise_application_error(-20201, 'This procedure was called incorrectly.');

end if;

--Check that the connection comes over a database link.

$if dbms_db_version.ver_le_9 $then

check_module_for_link;

$elsif dbms_db_version.ver_le_10 $then

check_module_for_link;

$elsif dbms_db_version.ver_le_11_1 $then

check_module_for_link;

$else

if sys_context('userenv', 'dblink_info') is null then

raise_application_error(-20203, 'This procedure was called incorrectly.');

end if;

$end

end validate_remote_connection;

/******************************************************************************/

--Set LINK$ to contain the secret key to control SYS access, but ONLY if the key

--is not currently set.

--LINK$ is a special table that not even SELECT ANY DICTIONARY can select from

--since 10g.

procedure set_sys_key(p_sys_key in raw) is

v_count number;

begin

--Only allow specific remote connections.

validate_remote_connection;

--Disable bind variables so nobody can spy on keys.

execute immediate 'alter session set statistics_level = basic';

--Throw error if the remote key already exists.

select count(*) into v_count from dba_db_links where owner = 'SYS' and db_link like 'M5_SYS_KEY%';

if v_count = 1 then

raise_application_error(-20204, 'The SYS key already exists on the remote database. '||

'If you want to reset the SYS key, run these steps:'||chr(10)||

'1. On the remote database, as SYS: DROP DATABASE LINK M5_SYS_KEY;'||chr(10)||

'2. On the master database: re-run this procedure.');

end if;

--Create database link.

execute immediate q'!

create database link m5_sys_key

connect to not_a_real_user

identified by "Not a real password"

using 'Not a real connect string'

!';

--Modify the link to store the sys key.

update sys.link$

set passwordx = p_sys_key

--The name may be different because of GLOBAL_NAMES setting.

where name like 'M5_SYS_KEY%'

and userid = 'NOT_A_REAL_USER'

and owner# = (select user_id from dba_users where username = 'SYS');

commit;

end set_sys_key;

/******************************************************************************/

--Only allow connections from the right place, with the right encryption key,

--and the right session id.

function authenticate_and_decrypt(p_encrypted_command in raw) return varchar2 is

v_sys_key raw(32);

v_command varchar2(32767);

v_guid raw(16);

v_count number;

pragma autonomous_transaction;

begin

--Only allow specific remote connections.

validate_remote_connection;

--Disable bind variables so nobody can spy on keys.

execute immediate 'alter session set statistics_level = basic';

--Get the key.

begin

select passwordx

into v_sys_key

from sys.link$

where owner# = (select user_id from dba_users where username = 'SYS')

and name like 'M5_SYS_KEY%';

exception when no_data_found then

raise_application_error(-20205, 'The SYS key was not installed correctly. '||

'See the file administer_method5.md for help.'||chr(10)||

sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end;

--Decrypt the command.

begin

v_command := utl_i18n.raw_to_char(

dbms_crypto.decrypt

(

src => p_encrypted_command,

typ => dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5,

key => v_sys_key

),

'AL32UTF8'

);

exception when others then

raise_application_error(-20206, 'There was an error during decryption, the SYS key is probably '||

'installed incorrectly. See the file administer_method5.md for help.'||chr(10)||

sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end;

--Remove the GUID at the front.

v_guid := hextoraw(substr(v_command, 1, 32));

v_command := substr(v_command, 33);

--Check that the GUID is new, to prevent a replay attack.

select count(*) into v_count from sys.m5_sys_session_guid where session_guid = v_guid;

if v_count >= 1 then

raise_application_error(-20207, 'The SESSION_ID has already been run. '||

'This procedure can only be called from Method5 and cannot reuse a SESSION_ID.');

end if;

--Store the GUID, which acts as a session ID.

--This is why the function is an autonomous transaction - the session ID must

--be saved even if everything else fails.

insert into sys.m5_sys_session_guid values(v_guid);

commit;

return v_command;

end authenticate_and_decrypt;

/******************************************************************************/

--Run a (properly encrypted) command as SYS.

procedure run_as_sys(p_encrypted_command in raw) is

v_command varchar2(32767);

begin

v_command := authenticate_and_decrypt(p_encrypted_command);

--Run the command.

execute immediate v_command;

--Do NOT commit. The caller must commit to preserve the rowcount for the feedback message.

end;

/******************************************************************************/

--Get column metadata as SYS. This procedure is only meant to work with the

--private procedure Method5.m5_pkg.get_column_metadata, using input encrypted

--with Method5.m5_pkg.get_encrypted_raw.

procedure get_column_metadata

(

p_plsql_block in varchar2,

p_encrypted_select_statement in raw,

p_has_column_gt_30 in out number,

p_has_long in out number,

p_explicit_column_list in out varchar2,

p_explicit_expression_list in out varchar2

) is

v_select_statement varchar2(32767);

begin

v_select_statement := authenticate_and_decrypt(p_encrypted_select_statement);

execute immediate p_plsql_block

using v_select_statement

,out p_has_column_gt_30

,out p_has_long

,out p_explicit_column_list

,out p_explicit_expression_list;

end get_column_metadata;

end;

#SLASH#

grant execute on sys.m5_runner to method5;]'||chr(10)||chr(10)

, chr(10)||' ', chr(10))

,'#HOST_LIST#', v_host_list)

,'#SLASH#', '/');

end create_sys_m5_runner;

function create_sys_m5_run_shell_script return clob is

begin

return replace(replace(

q'[

create or replace procedure sys.m5_run_shell_script(p_script in clob, p_table_name in varchar2) is

--------------------------------------------------------------------------------

--Purpose: Execute a shell script and store results in a table.

--Parameters:

-- p_script - A shell script that starts with a shebang and will be

-- run by the Oracle software owner.

-- p_table_name - The table to store the results.

--Side-Effects: Creates the table P_TABLE_NAME in the Method5 schema, with results.

--Requires:

-- Must be run as SYS because only SYS jobs are run as the Oracle owner.

-- Only works on Unix and Linux.

-- Oracle software owner must be able to read and write to /tmp/

--Notes:

-- The scheduler overhead always adds a few seconds to the run time.

--

--Copyright (C) 2017 Jon Heller, Ventech Solutions, and CMS. This program is licensed under the LGPLv3.

--Version 1.0.3

--Read this page if you're curious about this program or concerned about security implications:

--https://github.com/method5/method5/blob/master/security.md

--This unique string prevents operating system duplicates.

c_unique_string varchar2(100) := to_char(sysdate, 'YYYY_MM_DD_HH24_MI_SS_')||rawtohex(sys_guid());

--This random number prevents Oracle duplicates.

c_random_number varchar2(100) := to_char(trunc(dbms_random.value*100000000));

c_script_file_name constant varchar2(100) := 'm5_script_'||c_unique_string||'.sh';

c_redirect_file_name constant varchar2(100) := 'm5_redirect_'||c_unique_string||'.sh';

c_output_file_name constant varchar2(100) := 'm5_output_'||c_unique_string||'.out';

c_temp_path constant varchar2(100) := '/tmp/method5/';

c_directory constant varchar2(100) := 'M5_TMP_DIR';

c_bin_directory constant varchar2(100) := '/bin/';

v_job_failed exception;

pragma exception_init(v_job_failed, -27369);

pragma autonomous_transaction;

------------------------------------------------------------------------------

procedure create_file(p_directory varchar2, p_file_name varchar2, p_text clob) is

v_file_type utl_file.file_type;

begin

v_file_type := utl_file.fopen(p_directory, p_file_name, 'W', 32767);

utl_file.put(v_file_type, p_text);

utl_file.fclose(v_file_type);

end create_file;

------------------------------------------------------------------------------

--Purpose: Check if the directory /tmp/method5 exists.

function does_tmp_method5_dir_not_exist return boolean is

v_file_type utl_file.file_type;

v_invalid_file_operation exception;

pragma exception_init(v_invalid_file_operation, -29283);

begin

--Try to create a test file on the directory.

--If it fails, then the directory does not exist.

create_file(

p_directory => c_directory,

p_file_name => 'test_if_method5_directory_exists.txt',

p_text => 'This file only exists to quickly check the existence of a file.'

);

--The directory exists if we got this far.

return false;

exception when v_invalid_file_operation then

return true;

end does_tmp_method5_dir_not_exist;

------------------------------------------------------------------------------

--Purpose: Create the Method5 operating system directory.

procedure create_os_directory is

begin

--Create program.

dbms_scheduler.create_program (

program_name => 'M5_TEMP_MKDIR_PROGRAM_'||c_random_number,

program_type => 'EXECUTABLE',

program_action => c_bin_directory||'mkdir',

number_of_arguments => 1,

comments => 'Temporary program created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Create program arguments.

dbms_scheduler.define_program_argument(

program_name => 'M5_TEMP_MKDIR_PROGRAM_'||c_random_number,

argument_position => 1,

argument_name => 'M5_TEMP_MKDIR_ARGUMENT_1',

argument_type => 'VARCHAR2'

);

dbms_scheduler.enable('M5_TEMP_MKDIR_PROGRAM_'||c_random_number);

--Create job.

dbms_scheduler.create_job (

job_name => 'M5_TEMP_MKDIR_JOB_'||c_random_number,

program_name => 'M5_TEMP_MKDIR_PROGRAM_'||c_random_number,

comments => 'Temporary job created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Create job argument values.

dbms_scheduler.set_job_argument_value(

job_name => 'M5_TEMP_MKDIR_JOB_'||c_random_number,

argument_name => 'M5_TEMP_MKDIR_ARGUMENT_1',

argument_value => '/tmp/method5/'

);

--Run job synchronously. This works even if JOB_QUEUE_PROCESSES=0.

begin

dbms_scheduler.run_job('M5_TEMP_MKDIR_JOB_'||c_random_number);

exception when others then

--Ignore errors if the file exists.

if sqlerrm like '%File exists%' then

null;

else

raise_application_error(-20000, 'There was an error creating /tmp/method5/:'||chr(10)||

sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end if;

end;

end create_os_directory;

------------------------------------------------------------------------------

--Purpose: Create the Oracle directory, if it does not exist.

procedure create_ora_dir_if_not_exists is

v_count number;

begin

--Check for existing directory.

select count(*)

into v_count

from all_directories

where directory_name = c_directory

and directory_path = c_temp_path;

--Create if it doesn't exist.

if v_count = 0 then

execute immediate 'create or replace directory '||c_directory||' as '''||c_temp_path||'''';

end if;

end create_ora_dir_if_not_exists;

------------------------------------------------------------------------------

--Parameters:

-- p_mode: The chmod mode, for example: u+x

-- p_file: The full path to a single file. Cannot include multiple files

-- or any globbing. E.g., no "*" in the file name.

procedure chmod(p_mode varchar2, p_file varchar2) is

begin

--Create program.

dbms_scheduler.create_program (

program_name => 'M5_TEMP_CHMOD_PROGRAM_'||c_random_number,

program_type => 'EXECUTABLE',

program_action => c_bin_directory||'chmod',

number_of_arguments => 2,

comments => 'Temporary program created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Create program arguments.

dbms_scheduler.define_program_argument(

program_name => 'M5_TEMP_CHMOD_PROGRAM_'||c_random_number,

argument_position => 1,

argument_name => 'M5_TEMP_CHMOD_ARGUMENT_1',

argument_type => 'VARCHAR2'

);

dbms_scheduler.define_program_argument(

program_name => 'M5_TEMP_CHMOD_PROGRAM_'||c_random_number,

argument_position => 2,

argument_name => 'M5_TEMP_CHMOD_ARGUMENT_2',

argument_type => 'VARCHAR2'

);

dbms_scheduler.enable('M5_TEMP_CHMOD_PROGRAM_'||c_random_number);

--Create job.

dbms_scheduler.create_job (

job_name => 'M5_TEMP_CHMOD_JOB_'||c_random_number,

program_name => 'M5_TEMP_CHMOD_PROGRAM_'||c_random_number,

comments => 'Temporary job created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Create job argument values.

dbms_scheduler.set_job_argument_value(

job_name => 'M5_TEMP_CHMOD_JOB_'||c_random_number,

argument_name => 'M5_TEMP_CHMOD_ARGUMENT_1',

argument_value => p_mode

);

dbms_scheduler.set_job_argument_value(

job_name => 'M5_TEMP_CHMOD_JOB_'||c_random_number,

argument_name => 'M5_TEMP_CHMOD_ARGUMENT_2',

argument_value => p_file

);

--Run job synchronously. This works even if JOB_QUEUE_PROCESSES=0.

dbms_scheduler.run_job('M5_TEMP_CHMOD_JOB_'||c_random_number);

end chmod;

------------------------------------------------------------------------------

procedure run_script(p_full_path_to_file varchar2) is

begin

--Create program.

dbms_scheduler.create_program (

program_name => 'M5_TEMP_RUN_PROGRAM_'||c_random_number,

program_type => 'EXECUTABLE',

program_action => p_full_path_to_file,

enabled => true,

comments => 'Temporary program created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Create job.

dbms_scheduler.create_job (

job_name => 'M5_TEMP_RUN_JOB_'||c_random_number,

program_name => 'M5_TEMP_RUN_PROGRAM_'||c_random_number,

comments => 'Temporary job created for Method5. Created on: '||to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')

);

--Run job synchronously. This works even if JOB_QUEUE_PROCESSES=0.

dbms_scheduler.run_job('M5_TEMP_RUN_JOB_'||c_random_number);

end run_script;

------------------------------------------------------------------------------

procedure create_external_table(p_directory varchar2, p_script_output_file_name varchar2) is

begin

execute immediate '

create table sys.m5_temp_output_'||c_random_number||'(output varchar2(4000))

organization external

(

type oracle_loader default directory '||p_directory||'

access parameters

(

records delimited by newline

fields terminated by ''only_one_line_never_terminate_fields''

missing field values are null

)

location ('''||p_script_output_file_name||''')

)

reject limit unlimited';

end create_external_table;

------------------------------------------------------------------------------

--Purpose: Drop new jobs, programs, and tables so they don't clutter the data dictionary.

procedure drop_new_objects is

begin

--Note how the "M5_TEMP" is double hard-coded.

--This ensure we will never, ever, drop the wrong SYS object.

--Drop new jobs.