Automating Oracle Databases Password Rotation using Ansible

Overview

Password rotation is not the most exciting task especially with different type of Oracle Databases like Standalone, RAC & Multitenant and it’s not fun to change sys or system passwords every 30/60 days . We typically use Ansible for task automation, and I like it mainly because of its non-intrusive configuration (no agents need to be installed on the target servers), and its scalability (tasks are executed in parallel on the target servers).

Basically Shell script with PLSQL that alerts the database users with new password from Ansible vault.
With Multitenant databases, it is not easy to use PLSQL logic to change passwords for PDB databases in CDB, since we either need to use OEM scheduler trigger the Job or concat perl script to run custom script.

Prerequisites:

Ansible runs on Oracle Linux 6 or RHEL 6 or RHEL7.
Ansible 2.3
jmespath plugin on Ansible control host (pip install jmespath)
jinja2 plugin on Ansible control host (I had to update it using pip install -U jinja2 in few cases)

The Playbook

This is the whole list of files that are included in the playbook

└───oracle-passwd-change-master
    └───roles
        └───ansible-oracle-change-pwd
            ├───defaults
            ├───files
            ├───handlers
            ├───tasks
            ├───templates
            ├───tests
            └───vars

Let’s take a look of each files in the playbook.

-default/main.yml
---
# defaults file for ansible-oracle-change-pwd

orclchangepwd:
    #
    # This installer is capable of three different installation
    # methods:
    #
    #   pkg:  Fetch the pkg yml file which helps to install on non cdb databases
    #   pdb:  Fetch the pdb yml file change database passwords on pdb databases
    #
    #
    install_method: pkg
    install_temp_dir: ""

tasks/main.yml
- name: Oracle Change Password Installer | Validate Required Parameters and Environment   assert:     that:         
- ansible_pkg_mgr is defined         
- ansible_pkg_mgr in ['yum', 'pdb', 'pkg'] 
- name: Oracle Change Password Installer | Beginning for PDB Databases   include_tasks: pdb.yml   
when: orclchangepwd.install_method == "pdb" 
- name: Oracle Change Password Installer | Beginning for Standalone Databases Process   
include_tasks: pkg.yml   
when: orclchangepwd.install_method == "pkg"

tasks/pkg.yml

- name: create ansible stage directory Oracle Password change     
file:       path: '{{ stage_folder }}/'       
state: directory   
- name: Copy Oracle password change template file to remote server     template: src=templates/chg_dbpasswd.j2 dest={{ stage_folder }}/chg_dbpasswd.sh mode=755   
- name: Check the file     
shell: ls '{{ stage_folder }}'/'{{ file_name }}'       
- name: set env path shell script     
shell: sed -i -e 's/\r$//' {{ stage_folder }}/{{ file_name }}   
- name: Run change password 
shell script     
command: '{{ stage_folder }}/{{ file_name }} {{ db_users }}'     
register: script_output   
- debug:       
  var: script_output

tasks/pdb.yml

- name: create ansible stage directory Oracle Password change
 file:
   path: '{{ stage_folder }}/'
   state: directory
- name: create PDB stage directory Oracle Password change
 file:
   path: '{{ pdb_working_folder }}/'
   state: directory  
- name: Copy Oracle password change template file to remote server
 template: src=templates/chg_pdbpasswd.j2 dest={{ pdb_working_folder }}/chg_pdbpasswd.sh mode=755
- name: Check the file
 shell: ls '{{ pdb_working_folder }}'/'{{ pdb_file_name }}'
- name: set env path shell script
 shell: sed -i -e 's/\r$//' {{ pdb_working_folder }}/{{ pdb_file_name }}
- name: Run change password shell script
 command: '{{ pdb_working_folder }}/{{ pdb_file_name }} {{ db_users }}'
 register: script_output
 debug:
   var: script_output 

templates/chg_pdbpasswd.j2, chg_dbpasswd.j2
or 
files/chg_pdbpasswd.j2, chg_dbpasswd.j2 
Modify according to your need 

Create these file from below link 
https://dbadeeds.wordpress.com/2020/12/04/change-all-oracle-databases-password-on-server-using-shell-scripts/

vars/main.yml

 vars file for change_pwd-role
 pdb_working_folder: /u01/app/oracle/ansible/pdb
 stage_folder: /u01/app/oracle/ansible
 file_name: "chg_dbpasswd.sh"
 pdb_file_name: "chg_pdbpasswd.sh"
 db_users: "sys system dbsnmp"
 vault_orclpasswd: *** Copy your vault key string here with no spaces****
 db_passwd: "{{ vault_orclpasswd }}"

Dependencies

1. Ansible Vault

Example Playbook

Ansible Users you need to add list user which needs to be change in var/main.yml file
Define type your type of database using defaults/main.yml {pkg–> DB | pdb –> CDB/PDB}

  - name: Install Oracle Database Ansible roles
    hosts: local 
    roles:
      #-
      - ansible-oracle-change-pwd

Sample Output

  dbadeeds[oracle:]/home/dbadeeds/roles/oracle-passwd-change-master$ time ansible-playbook -i host.ini myroles.yml --vault-id @prompt -u oracle -k
SSH password:
Vault password (default):

PLAY [Install Oracle Database Ansible roles] ************************************************************************************************************************************************************

TASK [Gathering Facts] **********************************************************************************************************************************************************************************
ok: [db_oracle_vagrant]

TASK [oracle-change-pwd : Oracle Change Password Installer | Validate Required Parameters and Environment] **********************************************************************************************
ok: [db_oracle_vagrant] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [oracle-change-pwd : Oracle Change Password Installer | Beginning for PDB Databases] ***************************************************************************************************************
skipping: [db_oracle_vagrant]

TASK [oracle-change-pwd : Oracle Change Password Installer | Beginning for Standalone Databases Process] ************************************************************************************************
included: /home/dbadeeds/roles/oracle-passwd-change-master/roles/oracle-change-pwd/tasks/pkg.yml for db_oracle_vagrant

TASK [oracle-change-pwd : create ansible stage directory Oracle Password change] ************************************************************************************************************************
ok: [db_oracle_vagrant]

TASK [oracle-change-pwd : Copy Oracle password change template file to remote server] *******************************************************************************************************************
changed: [db_oracle_vagrant]

TASK [oracle-change-pwd : Check the file] ***************************************************************************************************************************************************************
changed: [db_oracle_vagrant]

TASK [oracle-change-pwd : set env path shell script] ****************************************************************************************************************************************************
 [WARNING]: Consider using the replace, lineinfile or template module rather than running sed.  If you need to use command because replace, lineinfile or template is insufficient you can add
warn=False to this command task or set command_warnings=False in ansible.cfg to get rid of this message.

changed: [db_oracle_vagrant]

TASK [oracle-change-pwd : Run change password shell script] *********************************************************************************************************************************************
changed: [db_oracle_vagrant]

TASK [oracle-change-pwd : debug] ************************************************************************************************************************************************************************
ok: [db_oracle_vagrant] => {
    "script_output": {
        "changed": true,
        "cmd": [
            "/u01/app/oracle/ansible/chg_dbpasswd.sh",
            "system",
            "sys"
        ],
        "delta": "0:00:07.607390",
        "end": "2020-11-30 13:02:24.874690",
        "stdout": "": [
            "deedsray1",
            "Checking Username - 1: system in deedsray1 database",
            "Altered system username  in deedsray1 database",
            "",
            "PL/SQL procedure successfully completed.",
            "",
            "Checking Username - 2: sys in deedsray1 database",
            "Altered sys username  in deedsray1 database",
            "",
            "PL/SQL procedure successfully completed.",
            "",
            "dbadeeds",
            "Checking Username - 1: system in dbadeeds database",
            "Altered system username in dbadeeds database",
            "",
            "PL/SQL procedure successfully completed."
            "Checking Username - 2: sys in dbadeeds database",
            "Altered sys username  in dbadeeds database",
            "",
            "PL/SQL procedure successfully completed.",
            "",
            "Script Completed"
        ]
    }
}

PLAY RECAP **********************************************************************************************************************************************************************************************
db_oracle_vagrant            : ok=9    changed=4    unreachable=0    failed=0

DBADEEDS