search

oravirt / ansible-oracle-modules

Oracle modules for Ansible
MIT License
214 stars 159 forks source link

oracle_user: changing password does not result in a changed task state in Oracle 18 #87

Open chrisbrookes opened 5 years ago

chrisbrookes commented 5 years ago

Given a working install through the ansible-oracle scripts with config something like the following (cut parts for brevity):

dbpasswords:
  orcl18:
    appuser1: 123456

oracle_databases:
- home: 18300-base
  oracle_db_name: orcl18 
  oracle_db_type: SI
  # etc...

  users:
    - schema: appuser1
      update_password: "always"
      #...

If I change the password for appuser1 and then run through the oradb-manage-users role in ansible-oracle (which uses the oracle_user module), the password is changed in Oracle, but the task does not report changed:

TASK [ansible-oracle/roles/oradb-manage-users : Manage users (db/cdb)] *************************************************************************************************************
ok: [centosora1] => (item=port: 1521 service: orcl18 schema: appuser1 state:present)

The problem is down to the get_user_password_hash function and the fact it uses sys.user$ password. This is probably a problem in 12c+ since it seems from this point, Oracle has stopped putting the hashes in the sys.user$ password column (a good thing). There is the spare4 column that has a hash in but I believe it's stored with the salt so it's different every time and cannot be used to compare before and after.

I'm not sure if there's a good solution to this. There's some information how the spare4 hash is built up here but unsure if it's possible to use this to figure out how to run a hash function from python against existing salt + incoming password to compare against existing hash.

oravirt commented 5 years ago

Yeah, I was mucking around with this maybe a year ago (using the same info as you linked to), and at the time I ended up putting it in the 'too-hard' basket thinking I would maybe re-visit it at some point. I don't have a lot of time to put into investigating this (at least for now) so maybe I'll just settle for the fact that the password will be enforced to whatever the input is.

I'll look into the task not returning as changed, though.