Problems running mysql_user

[Dirk Röder]

Hello together,
I do have a problem with the mysql_user module.
It does create my user with the provided password, but it does not create the permissions I do pass.

Here is my call:

- name: Create User with single database privilieges
  mysql_user: name="ttr"
              host=localhost
              password=testPass
              priv="tinytinyrss.*:USAGE"
              state=present

When I then log into MySQL and view allowed databases:

[root@munin ~]# mysql -u ttr -ptestPass
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 60
Server version: 5.5.40-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

I tried with different versions of quoting, but none of these helped. The Database tinytinyrss does exist, was successfully created the ansible task before.

My Ansible Version is 1.8.2

Regards
Dirk

[Tom Bamford]

Hi Dirk

Would you not need to grant SELECT instead of USAGE?

According to MySQL docs, USAGE is granted at the server level. When I create a user locally (with or without Ansible), that user gets USAGE on *.* but it doesn't allow them to see any databases.

Regards

Tom

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To post to this group, send email to ansible...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/d6fe4a7d-fcff-4253-a82e-6f11d43c18d3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dirk Röder]

Hi Tom,
I worked around it meanwhile with the following:

- name: Create TinyTinyRSS database user
  when: ttr.stat.exists == False
  mysql_user: name={{ app_dbUser }}
              host=localhost
              password="{{ mysql_user_pass.stdout }}"
              priv="{{ app_dbName }}.*:SELECT,INSERT,UPDATE,DELETE"
              state=present

From my point of view USAGE was a combination of these 4 (Which sounds quite logical), but after reading even the ansible documentation more properly it states:

# Revoke all privileges for user 'bob' and password '12345' 
- mysql_user: name=bob password=12345 priv=*.*:USAGE state=present

The MySQL Documentation explains it with the full details:

The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

So it was a mistake of mine, not ansible. Shame on me :/
Nevetheless thanks for the hint.

Kind regards
Dirk