Oracle Forensics

YML script

- name: Execute SQL using shell

hosts: all

gather_facts: no

vars_prompt:

- name: db_name

prompt: "Enter the Oracle database name (service name or SID)"

private: no

- name: db_host

prompt: "Enter the Oracle hostname or IP"

private: no

- name: db_user

prompt: "Enter DB username"

private: no

- name: db_pass

prompt: "Enter DB password"

private: yes

vars:

sql_statement: "revoke update on ashwani.test from tarun;"

oracle_home: "/u01/app/oracle/product/19.0.0/db_1/"

tasks:

- name: Run SQL script

shell: |

export ORACLE_HOME={{ oracle_home }}

echo "{{ sql_statement }}" | $ORACLE_HOME/bin/sqlplus {{ db_user }}/{{ db_pass }}@'{{ db_host }}/{{ db_name }}'

- name: Display query result

debug:

var: query_result.stdout_lines

================= EXECUTION LOGS============================

[root@srv1 mypb]# ansible-playbook revoke.yml

Enter the Oracle database name (service name or SID): finance

Enter the Oracle hostname or IP: 192.168.1.122

Enter DB username: ashwani

Enter DB password:

PLAY [Execute SQL using shell] ****************************************************************************************************************************************

TASK [Run SQL script] *************************************************************************************************************************************************

[WARNING]: Platform linux on host 192.168.1.122 is using the discovered Python interpreter at /usr/bin/python, but future installation of another Python interpreter

could change this. See https://docs.ansible.com/ansible/2.9/reference_appendices/interpreter_discovery.html for more information.

changed: [192.168.1.122]

TASK [Display query result] *******************************************************************************************************************************************

ok: [192.168.1.122] => {

"query_result.stdout_lines": [

"",

"SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 19 23:31:15 2025",

"Version 19.3.0.0.0",

"",

"Last Successful login time: Wed Nov 19 2025 23:27:00 +04:00",

"",

"Connected to:",

"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production",

"Version 19.3.0.0.0",

"",

"SQL> ",

"Revoke succeeded.",

"",

"SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production",

"Version 19.3.0.0.0"

]

}

PLAY RECAP ************************************************************************************************************************************************************

192.168.1.122 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

=================================================================

Other Used case:

Option 1: Read DB list from an external YAML file

step 1)

Create a YAML file with the database list

Create a file named: db_list.yml

databases:

- host: 192.168.1.50

service: FINANCE

- host: 192.168.1.60

service: HRDB

- host: 10.10.10.5

service: SALES

- host: srv-db01.domain.local

service: ORCL

Step 2). Updated Ansible Playbook Using db_list.yml

- name: Execute SQL on multiple Oracle databases from file

hosts: all

gather_facts: no

vars_files:

- db_list.yml

vars_prompt:

- name: db_user

prompt: "Enter DB username"

private: no

- name: db_pass

prompt: "Enter DB password"

private: yes

vars:

sql_statement: "revoke update on ashwani.test from tarun;"

oracle_home: "/u01/app/oracle/product/19.0.0/db_1/"

tasks:

- name: Execute SQL on each DB from the file

shell: |

export ORACLE_HOME={{ oracle_home }}

echo "{{ sql_statement }}" | \

$ORACLE_HOME/bin/sqlplus {{ db_user }}/{{ db_pass }}@{{ item.host }}/{{ item.service }}

loop: "{{ databases }}"

loop_control:

label: "{{ item.host }} / {{ item.service }}"

- name: Display results

debug:

msg: |

DB: {{ item.item.host }} / {{ item.item.service }}

Output:

loop: "{{ sql_outputs.results }}"

=================================================================

Option 2: Different SQL per database

1. Create db_list.yml with per-DB SQL

You define each DB along with the SQL you want to run on it:

databases:

- host: 192.168.1.50

service: FINANCE

sql: "revoke update on ashwani.test from tarun;"

- host: 192.168.1.60

service: HRDB

sql: "SELECT username FROM dba_users;"

- host: 10.10.10.5

service: SALES

sql: "alter user sales_app account unlock;"

- host: 172.16.20.25

service: INVENTORY

sql: "grant select on inv.items to report_user;"

2. Updated Playbook: Runs SQL per DB automatically

Save as: run_sql_multi_db_custom.yml

- name: Execute custom SQL on multiple Oracle databases

hosts: all

gather_facts: no

vars_files:

- db_list.yml

vars_prompt:

- name: db_user

prompt: "Enter DB username"

private: no

- name: db_pass

prompt: "Enter DB password"

private: yes

vars:

oracle_home: "/u01/app/oracle/product/19.0.0/db_1/"

tasks:

- name: Execute DB-specific SQL

shell: |

export ORACLE_HOME={{ oracle_home }}

echo "{{ item.sql }}" | \

$ORACLE_HOME/bin/sqlplus {{ db_user }}/{{ db_pass }}@{{ item.host }}/{{ item.service }}

loop: "{{ databases }}"

loop_control:

label: "{{ item.host }} / {{ item.service }}"

- name: Show SQL output per DB

debug:

msg: |

Database: {{ item.item.host }} / {{ item.item.service }}

SQL Executed: {{ item.item.sql }}

Output:

loop: "{{ sql_outputs.results }}"

Oracle Forensics

Total Pageviews

Wednesday, November 19, 2025

Revoke using ansible