oracle_pdbs/roles/grants: Object privileges only work with 1 privilege?

[matthiaslink77]

Hi,

If I try to add object privileges to a created role in a PDB, but it only succeeds if I have 1 single privilege.

With 2 it already fails.

Maybe I am missing the syntax here, but to my understanding it should work as follows:

oracle_pdbs: ... roles:

• name: myRole1 grants:
  • select on dba_users
  • select on dba_advisor_findings

...

As soon as I have the 2nd line of "select on..." in there, the task fails: "msg": "Something went wrong while executing sql - ORA-00905: missing keyword sql: grant select on dba_users,select on dba_advisor_findings to myRole1"}

Does this have to be splitted differently or did I miss something?

Thanks a lot for helping me out here.

Best regards, Matthias.

[oravirt]

Hi,

So, the privilege model is split onto 3 parts e.g

    roles:
          - name: approle1
            grants_mode: enforce
            role_grants:
                    - create session
                    - select ANY table
                    - dba
             object_privs:
                    - select:appuser.table1
                    - delete,update:sys.dba_tablespaces
                    - execute:sys.dbms_lock
             directory_privs:
                    - read,write:sys.mydir

and then the task looks something like:

 - name: Manage role-grants
   oracle_grants:
           hostname={{ hostname }}
           service_name={{ service_name }}
           mode={{ mode |default (omit) }}
           user={{ user | default (omit) }}
           password={{ password | default (omit) }}
           role={{ item.name }}
           grants={{ item.role_grants | default (omit)}}
           object_privs={{ item.object_privs | default (omit)}}
           directory_privs={{ item.directory_privs | default (omit)}}
           grants_mode={{ item.grants_mode | default (omit)}}
    environment: "{{oracle_env}}"
    with_items:
         - "{{ roles }}"

grants_mode: enforce/append enforce means that the module will apply the state to the role/user, meaning that whatever privileges you had before will be replaced with the input to the module. append will just append privileges and not remove any

Hope this helps! Best Regards /M

[matthiaslink77]

Hi,

thanks for clarifying. I did not see any example, so this helped! But if I do it like this:

oracle_pdbs: ... users:

• schema: appUser1 ... grants:
  • appRole1 ... roles:
• name: appRole1 grants_mode: enforce role_grants:

  --user operations

       - create user
       - alter user

  ... object_privs:

  --grant specific selects

       - select:sys.dba_advisor_findings
       - select:sys.dba_users

  ... state: present

I will get all the object privs now, but the system privs are completely empty. What am I missing?

Best regards, Matthias.

[oravirt]

What does the task look like?

If you have an entire playbook I can use, or a gist with the relevant task/variables that I can run would help.

[matthiaslink77]

I am using your default role oradb-manage-grants and just saw it would be "grants" instead of "role_grants":

• name: Manage role grants (pdb) oracle_grants: role={{ item.1.name }} state={{ item.1.state }} grants={{ item.1.grants |default(omit) }} object_privs={{ item.1.object_privs | default (omit) }} hostname={{ ansible_hostname }} port={{ listener_port_template }} service_name={{ item.0.pdb_name }} user={{ db_user }} password={{ db_password_pdb}} mode={{ db_mode }}

I noticed, the directory_privs are missing here. Are you going to add them as well?

Best regards, Matthias.

[oravirt]

Great! Yes, I'll add directory_privs as well