oracle_grants - not idempotent - flags all roles grants as changed on each run

[topher-debusk]

There is a good chance I am just doing this wrong... so feel free to correct me :)

Here are my vars:

oracle_roles:
  - { name: "prtg_role", state: present, role_grants: [ "create session",
                                                        "select on sys.dba_data_files",
                                                        "select on sys.dba_free_space",
                                                        "select on sys.dba_segments",
                                                        "select on sys.dba_tablespaces",
                                                        "select on sys.v_$archived_log",
                                                        "select on sys.v_$open_cursor",
                                                        "select on sys.v_$parameter",
                                                        "select on sys.v_$recovery_file_dest",
                                                        "select on sys.v_$session",
                                                        "select on sys.v_$sesstat",
                                                        "select on sys.v_$sga_target_advice",
                                                        "select on sys.v_$statname" ] }

Here are my tasks:

- name: Create role
  oracle_role:
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: sys
    mode: sysdba
    password: "{{ password }}"
    role: "{{ item.name }}"
    state: "{{ item.state }}"
  environment: "{{ oracle_env }}"
  with_items: "{{ oracle_roles }}"
  delegate_to: localhost
  become: no

- name: Add grants to role
  oracle_grants:
    hostname: "{{ ansible_fqdn }}"
    service_name: "{{ oracle_sid }}"
    user: sys
    mode: sysdba
    password: "{{ password }}"
    role: "{{ item.0.name }}"
    grants: "{{ item.1 }}"
    state: "{{ item.0.state }}"
  environment: "{{ oracle_env }}"
  with_subelements:
         - "{{ oracle_roles }}"
         - role_grants
  delegate_to: localhost
  become: no

Here is the output I get on each run:

TASK [oracle_common : Create application role] ***********************************************************************************************************
ok: [dbdevdb01  -> localhost] => (item={u'state': u'present', u'name': u'prtg_role', u'role_grants': [u'create session', u'select on sys.dba_data_files', u'select on sys.dba_free_space', u'select on sys.dba_segments', u'select on sys.dba_tablespaces', u'select on sys.v_$archived_log', u'select on sys.v_$open_cursor', u'select on sys.v_$parameter', u'select on sys.v_$recovery_file_dest', u'select on sys.v_$session', u'select on sys.v_$sesstat', u'select on sys.v_$sga_target_advice', u'select on sys.v_$statname']})

TASK [oracle_common : Add grants to role] ****************************************************************************************************************
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'create session'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_data_files'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_free_space'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_segments'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.dba_tablespaces'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$archived_log'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$open_cursor'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$parameter'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$recovery_file_dest'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$session'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$sesstat'))
changed: [dbdevdb01  -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$sga_target_advice'))
changed: [dbdevdb01 -> localhost] => (item=({u'state': u'present', u'name': u'prtg_role'}, u'select on sys.v_$statname'))

It does not seem to be checking if the role already has the priv, it just updates it an marks it as changed, even though the role already had the priv granted.

Thanks for your time! I love this module, you should get it added to the core ansible project!!!

[oravirt]

Hi, a couple of things:

1. I think you may be running an older version of the module
2. If 1 is true then - the version you're using 'half-managed' object privs, (select/delete etc on any object). You could add them, but they were never considered after the initial add. In the current version, I'm actually not sure they can be added at all (since I changed the way I manage the privs) I'm about to start on an update that should fix this though.

In the meantime, there is another module called oracle_privs which I use to set object privs. So in your case, I would have split the privileges into multiple keys and 2 plays, e.g (note, this isn't actually tested but you get the idea):

• role_grants deals with system privileges
• object_grants sets the type of priv (select/insert etc)
• object lists the objects object_grants should be applied to

oracle_roles:

• name: prtg_role state: present role_grants:

  • create session object_grants:
    • select objects:
      • "sys.dba_data_files"
      • "sys.dba_free_space"
      • "sys.dba_segments"
      • "sys.dba_tablespaces"
      • "sys.v_$archived_log"
      • "sys.v_$open_cursor"
      • "sys.v_$parameter"
      • "sys.v_$recovery_file_dest"
      • "sys.v_$session"
      • "sys.v_$sesstat"
      • "sys.v_$sga_target_advice"
      • "sys.v_$statname"

• task: Manage object privs oracle_privs: roles={{ item.name }} state={{ item.state }} privs={{ item.obj_privs }} objs={{ item.obj_objects }} hostname: "{{ ansible_fqdn }}" service_name: "{{ oracle_sid }}" user: sys mode: sysdba password: "{{ password }}" with_items:

  • "{{ oracle_roles  }}"

• name: Add grants to role oracle_grants: hostname: "{{ ansible_fqdn }}" service_name: "{{ oracle_sid }}" user: sys mode: sysdba password: "{{ password }}" role: "{{ item.name }}" grants: "{{ item.role_grans }}" state: "{{ item.state }}" environment: "{{ oracle_env }}" with_items:

  • "{{ oracle_roles }}" delegate_to: localhost become: no

[topher-debusk]

I did a pull, just to double check my version... I think I was pretty up to date.

I'll give your suggestion a shot. Thanks for taking a look.

Unpacking objects: 100% (6/6), done.
From https://github.com/oravirt/ansible-oracle-modules
   19665a0..6b675e5  master     -> origin/master
Updating 19665a0..6b675e5
Fast-forward
 oracle_tablespace | 191 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 188 insertions(+), 3 deletions(-)

[oravirt]

Ok.

FYI - I'm adding object priv management now to oracle_grants, so should be done in a couple of days (hopefully)

[oravirt]

Hi, I've added the functionality to deal with object privileges to oracle_grants. I've added another key (object_privs), which is passed as a list. Each key should be in the format:

priv:owner.object
e.g
select:sys.dba_tablespaces

grants: 
          - create session
          ...
object_privs:
           - select:sys.v_$session
           - select:sys.dba_data_files
           -  insert,update,delete,select:owner.table
           ...

The module is in need of a pretty big re-factor, but it works for now.

[topher-debusk]

Sorry it took me so long to get around to checking this, but it appears to be working :)

Thanks a bunch!