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

59 views

Skip to first unread message

Raj

unread,

Apr 29, 2019, 7:04:37 PM4/29/19

to Ansible Project

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

Sathya Narayanan

unread,

Apr 30, 2019, 12:53:06 AM4/30/19

to ansible...@googlegroups.com

Have you tried using vault ?

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To post to this group, send email to ansible...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/2c440447-07a0-4025-83db-053cc1454668%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Raj

unread,

Apr 30, 2019, 1:26:32 AM4/30/19

to Ansible Project

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

On Monday, April 29, 2019 at 11:53:06 PM UTC-5, Sathya Narayanan wrote:

Have you tried using vault ?

On Tue, Apr 30, 2019, 4:34 AM Raj <sheka...@gmail.com> wrote:

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_queue.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

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ansible...@googlegroups.com.

Stefan Hornburg (Racke)

unread,

Apr 30, 2019, 1:42:24 AM4/30/19

to ansible...@googlegroups.com

On 4/30/19 1:04 AM, Raj wrote:
> 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

>
>
>
>
> |
> TASK [db-deploy :DisablingJobQueue]********************
> 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
>

> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to

> ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> To post to this group, send email to ansible...@googlegroups.com <mailto:ansible...@googlegroups.com>.

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/2c440447-07a0-4025-83db-053cc1454668%40googlegroups.com

> <https://groups.google.com/d/msgid/ansible-project/2c440447-07a0-4025-83db-053cc1454668%40googlegroups.com?utm_medium=email&utm_source=footer>.

> For more options, visit https://groups.google.com/d/optout.

--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration. Provisioning with Ansible.

signature.asc

Adam E

unread,

Apr 30, 2019, 1:31:51 PM4/30/19

to Ansible Project

you could also look at using oracle wallet if you don't want to use environment variables for some reason.