oracle_user module error expecting cx_oracle in DB server

[anandsolomon]

I am trying to create user using oracle_user module in a RDS server. Here is my playbook

• hosts: all vars: oracle_home: /home/ansible/instantclient_12_2 user: SYSDBA password: ** prt: "1521" hostname: "RDS Instance" sname: TESTDB oracle_env: ORACLE_HOME: "{{oracle_home}}" LD_LIBRARY_PATH: "{{oracle_home}}" PATH: "{{oracle_home}}"

  vars_files:

  • vault-pass.yml

  tasks:

  • name: This playbook will create the user. oracle_user: oracle_home: /home/ansible/instantclient_12_2 user: "{{user}}" password: "{{password}}" service_name: "{{sname}}" port: "{{prt}}" hostname: "{{hostname}}" schema: username schema_password: **** default_tablespace: USERS

    state: present

    grants: "'create session', create any table'" environment: "{{oracle_env}}"

This fails with below error FAILED! => {"changed": false, "msg": "The cx_Oracle module is required. 'pip install cx_Oracle' should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME & LD_LIBRARY_PATH is set"}

DO we need to have cx_oracle installed in the target server too? I had this same issue with oracle_sql module too, but I used "connection: local" to enforced the playbook to look at the local cx_oracle install. This is not working in oracle_user module.

[tdavison784]

@anandsolomon

Try using the following in your task:

name: This playbook will create the user.
oracle_user:
  oracle_home: /home/ansible/instantclient_12_2
  user: "{{user}}"
  password: "{{password}}"
  service_name: "{{sname}}"
  port: "{{prt}}"
  hostname: "{{hostname}}"
  schema: ***********
  schema_password: ****
  default_tablespace: USERS
delegate_to: localhost

This should allow that task to execute on the Ansible controller. If that doesn't work, most likely due to firewall then you will need to install the cx_Oracle module on the remote hosts.

[anandsolomon]

This works fantastic.

I am able to grant to my user. Is there a way I can alter the user like ?

ALTER USER USER_NAME DEFAULT ROLE ALL ; ALTER USER USER_NAME QUOTA UNLIMITED ON USERS;

[oravirt]

If you specify default_tablespace, the user will get quota unlimited on that tablespace.

There is currently no way to specify default role all (unless you run the oracle_sql module to explicitly run the sql), and I think the reason I have not implemented it was that users (by default) have all granted roles enabled by default...

I should probably add the the possibility to : 1 - Explicitly add quotas on 'other' tablespaces as well 2 - Have a flag to explicitly set default role all

[anandsolomon]

Thanks