How to mask connection string Passwords while connecting through DB using sqlplus ansible

Archives Ansible Project
1

I am running into an issue where ansible is spitting up password, which is a security breach as these logs will automatically uploads to developer shared location for review. How can I mask the passwords in spitting up here. I need to use stdout_lines whether we run the job in clean state or not, so I cant take out that from list.

`

  • name: Disabling Queue
    shell: source ~/.bash_profile && sqlplus -S ‘{{ admin }}/{{ password }}@{{ TNS_NAME }}’ @“disable.sql”
    register: disable_queue
    delegate_to: localhost
    args:
    chdir: roles/db-deploy/files/
    tags: Jobs

  • debug: var=disable_jobqueue.stdout_lines
    delegate_to: localhost
    tags:Jobs
    `

TASK [db-deploy : Disabling Job Queue] ******************** 21:46:20 [buildNode1] fatal: [Host1 -> localhost]: FAILED! => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'userName/visiblePassword(DESCRIPTION = (CONNECT_TIMEOUT=5)(RETRY_COUNT=0)(LOAD_BALANCE = off)(FAILOVER = on)(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = Host1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = Host1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = serviceName1)))' @\"disable.sql\"", "delta": "0:00:00.412873", "end": "2019-04-25 02:46:20.036544", "msg": "non-zero return code", "rc": 127, "start": "2019-04-25 02:46:19.623671", "stderr": "/bin/sh: sqlplus: command not found", "stderr_lines": ["/bin/sh: sqlplus: command not found"], "stdout": "", "stdout_lines": []} 21:46:20 [buildNode1] fatal: [Host2 -> localhost]: FAILED! => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'userName/visiblePassword(DESCRIPTION = (CONNECT_TIMEOUT=5)(RETRY_COUNT=0)(LOAD_BALANCE = off)(FAILOVER = on)(ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = Host2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = Host2)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = serviceName2)))' @\"disable.sql\"", "delta": "0:00:00.419597", "end": "2019-04-25 02:46:20.041097", "msg": "non-zero return code", "rc": 127, "start": "2019-04-25 02:46:19.621500", "stderr": "/bin/sh: sqlplus: command not found", "stderr_lines": ["/bin/sh: sqlplus: command not found"], "stdout": "", "stdout_lines": []} 21:46:20 [buildNode1] fatal: [Host3 -> localhost]: FAILED! => {"changed": true, "cmd": "source ~/.bash_profile && sqlplus -S 'userName/visiblePassword(DESCRIPTION = (CONNECT_TIMEOUT=5)(RETRY_COUNT=0)(LOAD_BALANCE = off)(FAILOVER = on)(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = Host3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = Host3)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = serviceName3)))' @\"disable.sql\"", "delta": "0:00:00.419941", "end": "2019-04-25 02:46:20.054755", "msg": "non-zero return code", "rc": 127, "start": "2019-04-25 02:46:19.634814", "stderr": "/bin/sh: sqlplus: command not found", "stderr_lines": ["/bin/sh: sqlplus: command not found"], "stdout": "", "stdout_lines": []}

Thanks and Regards
Raj

2

Have you tried using vault ?

3

Yes, admin & password variables will replace here and those are encrypted using ansible vault

4

I am running into an issue where ansible is spitting up password, which is a security breach as these logs will
automatically uploads to developer shared location for review. How can I mask the passwords in spitting up here. I need
to use stdout_lines whether we run the job in clean state or not, so I cant take out that from list.

>
-name:DisablingQueue
shell:source ~/.bash_profile && sqlplus -S '{{ admin }}/{{password }}@{{TNS_NAME }}' @"disable.sql"
register: disable_queue
delegate_to: localhost
args:
chdir: roles/db-deploy/files/
tags: Jobs

- debug: var=disable_jobqueue.stdout_lines
delegate_to: localhost
tags:Jobs
>

Hello Raj,

you can disable logging of the task details with

no_log: True

Also you can pass the password in an environment variable and use that in the shell command.

Regards
        Racke

5

you could also look at using oracle wallet if you don’t want to use environment variables for some reason.
https://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm#g1033548