Two ways to change Oracle passwords with Ansible

We do our share of database cloning for various purposes and usually the cloning process is fairly scripted.

However lately we have been looking more and more into Ansible which as per their site is simple automation/orchestration tool. (Not so sure about simple!) Recently there has been also lot of presentations on automating your Oracle infrastructure with Ansible.

Some good Oracle related blog posts on Ansible are:

Maris Elsins: https://blog.pythian.com/automating-password-rotation-oracle-databases/

Frits Hoogland: https://fritshoogland.wordpress.com/2014/09/14/using-ansible-for-executing-oracle-dba-tasks/

The post from Maris actually inspired me to do simple version of password change to static passwords we use for test environments.

One task we always have for test environments is to change the passwords on the instances and in this post I’ll look two different ways to change them via Ansible.

Initial configuration in this example:

We have a host which is dedicated for Ansible scripts and that hosts all the scripts we have for Ansible.

We have defined the target database host in /etc/ansible/hosts.

We have a playbook file which calls just one role which will be used to change the database passwords.

All the variables are defined in the group_vars/myhost file.

First method:

My idea was to define all required variables first in the group_vars file. As the file contains passwords it is created via ansible-vault create group_vars/myhost so file will be cryped and edited via ansible-vault edit with the password you define when you create the file. In my file I have following variables defined:

db_os_user: oracle
db_users:
   appsuser1:
       pass: "mypass1"
   appsuser2:
      pass: "mypass2"

So nothing more than the OS Oracle user on destination host and the database users with their passwords. I also have a sql script called changepass.sql which has:

set ver off pages 0
alter user &1 identified by "&2";
exit;

And then the playbook file dbpass.yml is defined as below:

---

- name: password check
 hosts: dbhost1
 roles:
 - dbpassword

So here I define the target host is dbhost1 defined in /etc/ansible/hosts and the role it will execute is dbpassword. The only file I use is then located under ../roles/dbpassword/tasks/main.yml

In the file I make sure there is directory called /usr/local/ansible where I will copy the script which changes the password. When you use become_user to change the executor of process there isn’t to my knowledge good way to get the home directory which is used in final task to source the environment file. That’s why I grep it from /etc/password

After that I will execute the sqlscript as the oracle user as sys.

File looks like this:

---
  - file:
      path: /usr/local/ansible
      state: directory
      mode: 0755

  - copy:
      src: files/
      dest: /usr/local/ansible/
      mode: 0777
      owner: "{{db_os_user}}"

  - name: grep and register home
      shell: >
      egrep "^{{ db_os_user }}:" /etc/passwd | awk -F: '{ print $6 }'
     changed_when: false
     register: user_home

  - name: change password
     shell: |
        source "{{ user_home.stdout }}"/"{{ SID }}".env && \
        sqlplus -S / as sysdba \
        "@/usr/local/ansible/change_pass.sql" {{ item.key }} {{ item.value.pass }}
     become_user: "{{db_os_user}}"
     become: true
     with_dict: "{{ db_users }}"

In the last task I use with_dict to loop through the group_vars db_users. The key is then defined as item.key and the value by item.value.yourvaluename. That wasn’t said very clearly anywhere so had to search it for some time.

That’s it! Now I just run the playbook with below and passwords are changed!

[ansible@ans_host1]# ansible-playbook --ask-vault-pass dbpass.yml

Vault password:

PLAY [password check] ******************************************************************************************************************************************************************************

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

TASK [dbpassword : file] ***************************************************************************************************************************************************************************
ok: [dbhost1]

TASK [dbpassword : copy] ***************************************************************************************************************************************************************************
ok: [dbhost1]

TASK [dbpassword : grep and register] **************************************************************************************************************************************************************
ok: [dbhost1]

TASK [dbpassword : verify user existence] **********************************************************************************************************************************************************
changed: [dbhost1] => (item={'key': u'appsuser1', 'value': {u'pass': u'mypass1'}})
changed: [dbhost1] => (item={'key': u'appsuser2', 'value': {u'pass': u'mypass2'}})

PLAY RECAP *****************************************************************************************************************************************************************************************
dbhost1 : ok=5 changed=1 unreachable=0 failed=0

Second method:

The beautiful thing with Ansible is that there are million ways to do tasks. I was searching further and found that there exists Oracle modules for Ansible by “oravirt” Mikael Sandström.  The modules can be found from:  https://github.com/oravirt/ansible-oracle-modules.

The only thing you require before using these modules is the cx_Oracle for Python. My test dbhost1 was Oracle Enterprise Linux 5 and I just couldn’t get the installation of cx_Oracle sorted.

That’s why in the second method I take connection from my ansible host (OEL7) which has the Oracle 12.1 instant client installed. For that I had to edit the /etc/ansible/hosts for [dbhost1] to use localhost:

localhost ansible_connection=local ansible_user=oracle

Playbook file:

---

    - name: password check
      hosts: pprddb
      roles:
         - dbpass_with_module

This time in the group_vars I have bit more variables:

db_users:
   appsuser1:
       pass: "mypass1"
   appsuser2:
      pass: "mypass2"
db_hostname: myhost1
db_servicename: TEST1
db_user: system
db_password: mypass1
oracle_env:
     LD_LIBRARY_PATH: /u01/app/oracle/product/12.1.0/client_2/
     ORACLE_HOME: /u01/app/oracle/product/12.1.0/client_2

If you look oravirt git instructions you can see there is test-modules.yml which has good examples how to use the module. So I will need above variables defined. I think they are quite easy to figure out why they are there. Note that you need two environment variables defined also so the modules run, ORACLE_HOME and LD_LIBRARY_PATH.

In the task file under ../roles/dbpass_with_module/tasks/main.yml I now have only one task which uses the oracle_user which modifies the existing user. With the same you can also create new users if needed and lots of other tasks as well!

---

   - name: change Oracle password on the host you connect
        oracle_user: hostname="{{db_hostname}}" service_name="{{db_servicename}}"        user="{{db_user}}" password="{{db_password}}" schema="{{item.key}}" s  chema_password="{{item.value.pass}}"
        environment: "{{oracle_env}}"
        with_dict: "{{ db_users }}"

So I define the variables and again loop through all database users similar way than earlier. Also the environment needs to be defined as you can see. The output?

[ansible@ans_host1]# ansible-playbook --ask-vault-pass dbpass_with_module.yml
Vault password:

PLAY [password check] ******************************************************************************************************************************************************************************

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

TASK [dbpass_with_module : change Oracle password on the host you connect] *************************************************************************************************************************
ok: [localhost] => (item={'key': u'appsuser1', 'value': {u'pass': u'mypass1'}})
ok: [localhost] => (item={'key': u'appsuser2', 'value': {u'pass': u'mypass2'}})

PLAY RECAP *****************************************************************************************************************************************************************************************
localhost : ok=2 changed=0 unreachable=0 failed=0

As you can see the play is now run on localhost instead of dbhost1 but it connects to the defined database.

Conclusion

Different ways to achieve same thing but the second one is lot easier to execute. Still it’s always good to see how you can achieve same thing via different ways. This is just one step on our clone automation but something which is easy to take in use.

And huge thanks for people working on the Oracle Ansible modules!