privs.yml

- hosts: all
  vars:
    oracle_home: /home/ansible/instantclient_12_2
    user: USERNAME
    password: change12
    prt: "1521"
    hostname: "SERVER NAME"
    sname: DBName
    oracle_env:
      ORACLE_HOME: "{{oracle_home}}"
      LD_LIBRARY_PATH: "{{oracle_home}}"
      PATH: "{{oracle_home}}"

  vars_files:
    - /home/ansible/protect/vault-pass.yml
    - /home/ansible/protect/RevokePrivs.yml

  tasks:
      debug:
        msg: " {{item}}"
      with_items:
        - "{{sql1}}"
      register: sqlqry

    - name : Display the content
      debug:
        msg: "{{sqlqry.results}}"

    - name: "loop through list from a privilges"
      debug:
        msg: " {{item}}"
      with_items:
        - "{{privs}}"
      register: privu

    - name : Display the content
      debug:
        msg: "{{privu.results.item.msg}}"

    - name: Script will revoke grants from certain user for complaince in AWS RDS
      oracle_sql:
        username: "{{user}}"
        password: "{{password}}"
        service_name: "{{sname}}"
        port: "{{prt}}"
        hostname: "{{hostname}}"
        sql: "{{sqlqry}}"
      environment: "{{oracle_env}}"
      register: query_result
      connection: local

    - local_action:
        copy content={{sql1}}
        dest=/home/ansible/protegrityt/log/user_privs.log

    - name : Display the content
      debug:
        msg: "{{ query_result }}"

    - name: This playbook will grant or revoke privilges in the user.
      oracle_privs:
        user: "{{user}}"
        password: "{{password}}"
        service_name: "{{sname}}"
        port: "{{prt}}"
        hostname: "{{hostname}}"
        privs: "{{privu}}"
        state: absent
        roles: "{{item}}"
        quiet: false
      environment: "{{oracle_env}}"
      register: user_privs
      with_items : "{{query_result.msg}}"
      delegate_to: localhost

    - name: Display the query results
      debug:
        msg: "{{ user_privs }}"
      with_items: msg.stdout

#    - local_action:
#        copy content={{sql1}}
#        dest=/home/ansible/{{sname}}/log/user_privs.log