re-runnign oracle_user module returns error

[gsirvas]

Hi, I started testing your modules today and I created a simple test to create a user. Running the playbook runs successfully and creates the user, but when I re-run the playbook with no change the playbook returns ""msg": "ORA-01031: insufficient privileges: sql: select password from sys.user$ where name = upper('dbauser2')""

Here is my playbook

---

• hosts: 127.0.0.1 connection: local user: opc

  sudo: yes

  sudo_user: oracle

  gather_facts: false

  vars:

  ora_user: oracle

  oracle_home: /usr/lib/oracle/18.3/client64 hostname: dbhost1 service_name: srv1 set_init_mode: sysdba set_init_user: sys user: system password: syspwd oracle_env: ORACLE_HOME: "{{ oracle_home }}" LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

  dba_user:

  • schema: dbauser2 schema_password_hash: B7C930D09B3AF263 # passwd = dbauser state: present default_tablespace: users grants:
    • "dba"

  tasks:

   - name: Create DBA user
     local_action: 
             module: oracle_user
             hostname: "{{ hostname }}"
             service_name: "{{ service_name }}"
             user: "{{ user }}"
             password: "{{ password }}" 
             schema: "{{ item.0.schema }}"
             schema_password_hash: "{{ item.0.schema_password_hash }}"
             state: "{{ item.0.state }}"
             default_tablespace: "{{ item.0.default_tablespace }}"
             grants: "{{ item.1 }}"
     environment: "{{ oracle_env }}"
     with_subelements: 
       - "{{ dba_user }}"
       - grants

I executed the query "select password from sys.user$ where name = upper('dbauser2')" as sys and it runs succesfully.

Is there additional setup needed that I'm missing?

[oravirt]

You need to run the play as sys as well. You're running as system: user: system

Also, I'm not sure this will be idempotent for 12.2/18.3 as they way the hashed passwords are stored has changed. It will probably always return as changed.

(you could also set update_password: on_create if you don't want to maintain the password after the initial run)