Documentation | |
---|---|
Name: | Project Takedown |
Description: | Born to takedown OracleDB |
Modification date : | 26/10/2020 |
Owner: | dodger |
Tags: | OracleDB, MySQL, PostgreSQL, Ceph… |
This project has born to “takedown” Oracle Database from any organization that wants to be “free as in freedom” using any or many of the available free options in the “GPL” world.
ssh dodger@192.168.56.100 tmux a sqlplus '/ as sysdba'
-- object information SELECT owner, object_type, object_name FROM dba_objects WHERE owner='HR' ORDER BY object_type ; SELECT object_name FROM dba_objects WHERE owner='HR' AND object_type='TABLE' ORDER BY object_type ; -- table row information ALTER SESSION SET CURRENT_SCHEMA=HR ; SET feed off SELECT COUNT(*) AS REGIONS FROM REGIONS ; SELECT COUNT(*) AS COUNTRIES FROM COUNTRIES ; SELECT COUNT(*) AS LOCATIONS FROM LOCATIONS ; SELECT COUNT(*) AS DEPARTMENTS FROM DEPARTMENTS ; SELECT COUNT(*) AS JOBS FROM JOBS ; SELECT COUNT(*) AS EMPLOYEES FROM EMPLOYEES ; SELECT COUNT(*) AS JOB_HISTORY FROM JOB_HISTORY ;
Using fully customizable ora2my script:
ssh dodger@192.168.56.100 tmux a cd /home/dodger/ora2my_transfer cat tables.txt bash ora2my_sync_v002.sh recreate "$(cat tables.txt)"
Check:
mysql -p$(cat mysql_password.txt) use takedown_hr -- table count select count(*) as REGIONS from REGIONS ; select count(*) as COUNTRIES from COUNTRIES ; select count(*) as LOCATIONS from LOCATIONS ; select count(*) as DEPARTMENTS from DEPARTMENTS ; select count(*) as JOBS from JOBS ; select count(*) as EMPLOYEES from EMPLOYEES ; select count(*) as JOB_HISTORY from JOB_HISTORY ; -- table description show create table REGIONS ; show create table COUNTRIES ; show create table LOCATIONS ; show create table DEPARTMENTS ; show create table JOBS ; show create table EMPLOYEES ; show create table JOB_HISTORY ;
Demonstration on how We can move oracle LOB (Large OBjects) to Amazon S3 (or any compatible technology, in this demo we will use min.io:
cd ./minio server data
Check: http://192.168.56.200:9000/
Using fully customizable script kill_tbuffer.py
:
cd ~/kill_tbuffer/ bash get_all_metadata.sh nosync HR
This script will:
Using fully customizable script get_all_metadata.sh
:
cd ~/get_all_metadata bash get_all_metadata.sh nosync HR
This script will:
${SQLGENERATOR}
inside script), which will generate one .sql
file for every object in the configured database/scheme.${SQLGENERATOR}
in the target database, this will generate 1 sql file for every object.MariaDB 10.3 introduces the “Oracle Mode” So we can take PL/SQL from oracle and plug in MariaDB with a little work.
Oracle SECURE_DML
:
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."SECURE_DML" IS BEGIN IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours'); END IF; END secure_dml; /
MySQL SECURE_DML
:
SET sql_mode=ORACLE; DROP PROCEDURE SECURE_DML; DELIMITER $$ CREATE PROCEDURE SECURE_DML IS BEGIN IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '08:00' AND '18:00' OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 1 AND 6 THEN signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!'; END IF; END SECURE_DML; $$ DELIMITER ;
Test:
CALL SECURE_DML ;
Maybe you want test it out of the secure time window:
DROP PROCEDURE SECURE_DML; DELIMITER $$ CREATE PROCEDURE SECURE_DML IS BEGIN IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '11:00' AND '11:01' OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 0 AND 1 THEN signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!'; END IF; END SECURE_DML; $$ DELIMITER ;
Oracle:
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."ADD_JOB_HISTORY" ( p_emp_id job_history.employee_id%TYPE , p_start_date job_history.start_date%TYPE , p_end_date job_history.end_date%TYPE , p_job_id job_history.job_id%TYPE , p_department_id job_history.department_id%TYPE ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; /
MySQL:
SET sql_mode=ORACLE; DROP PROCEDURE ADD_JOB_HISTORY; DELIMITER $$ CREATE PROCEDURE ADD_JOB_HISTORY ( p_emp_id JOB_HISTORY.EMPLOYEE_ID%TYPE , p_start_date JOB_HISTORY.START_DATE%TYPE , p_end_date JOB_HISTORY.END_DATE%TYPE , p_job_id JOB_HISTORY.JOB_ID%TYPE , p_department_id JOB_HISTORY.DEPARTMENT_ID%TYPE ) AS BEGIN INSERT INTO JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END ADD_JOB_HISTORY; $$ DELIMITER ;
Test:
SELECT * FROM JOB_HISTORY ; -- CALL ADD_JOB_HISTORY(666, '1997-09-21 00:00:00.000000', '1998-09-21 00:00:00.000000', 'DBA', 99) ; -- SELECT * FROM JOB_HISTORY ;