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

[oravirt]

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

[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!