oracle_user resets the permissions instead adds additional

[nalokin]

Hi,

There might be a small small issue with the oracle_grants and oracle_user. We first create user and then add grants to the user. If user already existed after executing the script we get only permissions that we requested and others removed.

For example: - before execution user has: CREATE SESSION, ROLE1 - execution adds user permissions to ROLE2 - after execution user has: CREATE SESSION, ROLE2

If we run it the second time it removes all the permissions from the user.

Everything works perfectly when user does not exist already!

Thanks. -N.

[nalokin]

Just to add comment on this sentence "If we run it the second time it removes all the permissions from the user".

When we run it exactly the same with the same parameters next time it reports that the user has changed ("13:47:22 changed: [localhost]") instead of result just being OK ("13:46:21 ok: [localhost]"). At that point all the permissions are removed, even the create session. There is only base user that exists. And then on the third consecutive run there is permissions added again but only to the ROLE2.

- name: oracle_user | create oracle user
  oracle_user: 
    hostname={{ hostname }} 
    service_name={{ service_name }} 
    mode={{ mode }} 
    user={{ user }} 
    password={{ password }} 
    schema={{ schema }} 
    schema_password={{ schema_password }}
    profile={{ profile }}
    state={{ state }}
    default_tablespace={{ default_tablespace }}
    update_password=on_create

- name: oracle_grants | grants for the user
  oracle_grants: 
    hostname={{ hostname }} 
    service_name={{ service_name }} 
    mode={{ mode }} 
    user={{ user }} 
    password={{ password }} 
    schema={{ schema }} 
    state=present 
    grants={{ user_grants }}    ---> This is from the message.

And the output is: 13:47:21 PLAY [localhost] *************************************************************** 13:47:21 TASK [debug] ******************************************************************* 13:47:21 ok: [localhost] => { 13:47:21 "msg": "'CREATE SESSION','ROLE2'" 13:47:21 } 13:47:21 TASK [oracle_user | create oracle user] ********************************** 13:47:22 changed: [localhost] 13:47:22 13:47:22 TASK [oracle_user | grants for the user] ********************************* 13:47:23 changed: [localhost]

[oravirt]

Hi, Can you show me the contents of the user_grants list you're using, or better yet, a gist that I can use to debug with?

The oracle_grants module works like this: whatever your user_grants list contains will be applied to the user/role it runs against, i.e it will enforce that state. e.g 1st run:

user_grants:
             - create session
             - role1

This will lead to the role/schema to have create session & role1 .

2nd run: Let's assume you've added role2 to the list:

user_grants:
             - create session
             - role1
             - role2

After the second run, the schema/role will have the create session, role1, role2 privileges.

3rd run: Let's assume you've changed the list of grants to:

user_grants:
             - role2

The third run will then revoke create session & role1 from the user/schema, but keep role2.

[nalokin]

OK. The enforce part answers my first question that list of roles will be enforced whatever we pass. In your example I see we need to maintain the inventory of users through the whole environment. Although in big environment it's going to be hard to maintain inventory of users.

i.e. Let's say that we want to automate user creation and then we build a new set of users, now we want to modify few of them and then add them a list. Now we need need to remove some of the roles and then add few more. And then again while the number of users is increasing and therefore our inventory file is increasing. It almost makes more sense to add parameter privilege_status[append,enforce] to make sure existing permissions are kept. unless you have an inventory up to date you need to maintain the list of permissions that are changing over time, constantly.

As for the second question with the removal of consecutive runs, list of grants is automatically built from our build script. I send the parameter directly as string "'CREATE SESSION','ROLE2'" when I call role execution. This parameter is being built automatically in the background.

Here is part from the oracle_grants.py that we used as an example that worked:

Add grants to the user oracle_grants: hostname=remote-db-server service_name=orcl user=system password=manager schema=myschema state=present grants='create session','create any table',connect

Thanks for your help with this. -N.

[oravirt]

Let's say that we want to automate user creation and then we build a new set of users, now we want to modify few of them and then add them a list. Now we need need to remove some of the roles and then add few more. And then again while the number of users is increasing and therefore our inventory file is increasing.

Well, this should only a problem if the users are added ad-hoc or there are other systems that do one part and you want this module to do another (i.e multiple sources) The most efficient way (in my experience) to use this is to have a list of users that is tied to a specific environment/configuration. If you do that, you will not have that problem. (I guess the main point is that there should be one source for the configuration)

We do this and it works perfectly, we change the config, commit and push and then (in our case) Jenkins takes over and enforces the configuration on that/those environment(s) using these modules.

However, the source could of course be something else than a Ansible configuration file, and you just feed the information from that source to the module. Again, I guess the main point is as long as there is one source there shouldn't really be a problem.

add parameter privilege_status[append,enforce]

I've been thinking about this before and I might add it at some point. I also started writing a quick-and-dirty-extract tool that extracts the existing users/roles/privs in a DB and builds a yaml file with that information (as a starting point) but I ran out of time and I haven't had time to finish it.

[nalokin]

I agree. One source file would ensure to do that but we have a big environment and everything existed before so the users are already in the environment. Also, that big inventory would be hard to update automatically. For now to update inventory you need software setup, git knowledge and basic environment knowledge with makes it hard to outsource this to somebody else. Unless you build automated inventory update which then complicates the stuff to the different level.

Also then you need to use this inventory as a starting point base and then you end up updating the inventory which at that point takes more time so you are replacing one process with another + you are exposing the inventory and user base to another side which makes it then another security issue. I think additional status would help very much to the ad-hoc ticketing approach. That way it's very usable in case of not just initial creation but also ongoing maintenance in bigger environments.

Keeping fingers crossed... :)

Thanks for the help. -N.

[nalokin]

Also for the alternate removal and addition I didn't check the current closed issues. Issue #80 might be the resolver for us also. Let me confirm this with new code pull before you use too much of your time please.

Thanks. -N.

[nalokin]

I've tested oracle_grants with ab908ef and b3066df and that resolved my other issues that I experienced. When nothing is changed we see the green light and permissions remain the same no matter how often we execute the same code.

Now it’s only related to the base enhancement from the title with appending or enforcing the permissions. Hope that’s OK.

Thank you for the help. -N.

[oravirt]

I just had a quick look at the code, and it looks like it should be pretty simple to add the enforce/append logic as it's basically a matter of not removing any existing privileges.

I'll see if I have time to take a stab at this tomorrow

[nalokin]

Fantastic. Thanks.

[oravirt]

https://github.com/oravirt/ansible-oracle-modules/commit/b90395ce894993d783b3011cc4e42c6d56acdb36 adds the enforce/append logic (default is enforce)

grants_mode: enforce/append

[nalokin]

I'm having some trouble with the execution.

Here is the whole output: 13:07:32 Traceback (most recent call last): 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/executor/task_executor.py", line 130, in run 13:07:32 res = self._execute() 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/executor/task_executor.py", line 528, in _execute 13:07:32 result = self._handler.run(task_vars=variables) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/plugins/action/normal.py", line 45, in run 13:07:32 results = merge_hash(results, self._execute_module(tmp=tmp, task_vars=task_vars, wrap_async=wrap_async)) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/plugins/action/init.py", line 633, in _execute_module 13:07:32 (module_style, shebang, module_data, module_path) = self._configure_module(module_name=module_name, module_args=module_args, task_vars=task_vars) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/plugins/action/init.py", line 165, in _configure_module 13:07:32 environment=final_environment) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/executor/module_common.py", line 869, in modify_module 13:07:32 environment=environment) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/executor/module_common.py", line 695, in _find_module_utils 13:07:32 recursive_finder(module_name, b_module_data, py_module_names, py_module_cache, zf) 13:07:32 File "/usr/lib/python2.7/site-packages/ansible/executor/module_common.py", line 466, in recursive_finder 13:07:32 tree = ast.parse(data) 13:07:32 File "/usr/lib64/python2.7/ast.py", line 37, in parse 13:07:32 return compile(source, filename, mode, PyCF_ONLY_AST) 13:07:32 File "", line 567 13:07:32 mutually_exclusive=[['schema', 'role']] 13:07:32 ^ 13:07:32 IndentationError: unexpected indent

Thanks for checking. Only added grants_mode=append to the playbook. -N.

[oravirt]

Fixed it now, sorry about this. Did a last second addition and didn’t test it.... my bad.

[nalokin]

Cool. np. Line 563?

Will check. -N.

[nalokin]

Hmmm. It works and adds the role but it acts out. Not sure if it's Oracle or not.

It does exactly as it should for the first one (ROLE1 through oracle_grants): ALTER USER TEST1 DEFAULT ROLE ALL; Then on the second execution while adding ROLE2 through oracle_grants same thing: ALTER USER TEST1 DEFAULT ROLE ALL; Then I manually add one more role (ROLE3) with default and then when I add ROLE4 through oracle_grants i see this: ALTER USER TEST1 DEFAULT ROLE ROLE1, ROLE2, ROLE3;

while I would expect this again: ALTER USER TEST1 DEFAULT ROLE ALL;

Hope that helps. Thanks. -N.

[oravirt]

I don't explicitly do any alter user xxxx default role ..... in the module, so whatever you get is from Oracle.

I'm not sure I follow what you do with role3, but I guess grant role3 to xxx & alter user xxx default role role1,role2,role3 ?

I think, as soon as you explicitly start setting default roles you're going to have to maintain it yourself or go back to default role all (this may be different across versions, I just tested this on 12.1 & 12.2). If you just add the role (without adding the alter user xxx default role....), it should be fine. So this doesn't really have anything to do with the module, but more how Oracle manages this.

[nalokin]

That's what I thought.. I thought Oracle has something to do with it but I'll have to play with various executions of the commands. I just found it strange that when I edit something manually on oracle on role3 and set role all default and after that assign role4 with oracle_grants does not have role4 set as default role but first three does.

I'll have to do more testing to get on top of this but either way addition works great. thanks. -N.

[matthiaslink77]

Does this also apply to roles or only to privileges assigned to users? To my it does not seem to work with roles, which could have been a work-around for other issues I had before. But the privileged will get overwritten, even if i use "grants_mode: append" with roles.

Should I open a separate request for this, if it's the case? Thanks

[oravirt]

@matthiaslink77 Not sure I understand what you mean? Do you have a role to which you assign privileges? And the privileges assigned to the role are not working? Or something completely different?

[matthiaslink77]

Sorry - yes. I was trying to assign privileges to a role and wanted to append them to the already existing role, which did not work for me so far. They were always replaced, instead of appended. So the question was, if the "grant_mode" is also implemented for privileges assigned to roles or only for privileged assigned directly to a user. Hope it is more clear now. Thanks.

[oravirt]

https://github.com/oravirt/ansible-oracle/commit/96c077ebd5735b67df2570f8823b13e52417dad4 should fix this.

I forgot to expose grants_mode in the tasks, so that is why it didn't work.