Salt: mysql.grant is wrapping * in backticks making it a string literal not a table or database wildcard

Created on 11 Apr 2018 · 4Comments · Source: saltstack/salt

Description of Issue/Question

As stated in the title mysql.grants.present will wrap all grants in backticks making the table name of * a literal *. Hence you can not access any tables.

Setup

Grant Pillar:

mariadb-grants:
  webuser-process:
    user: webuser
    grant: "PROCESS" -- this does not work, but not part of this ticket.
    database: '*.*'
    host: "localhost"
    grant_option: False
  webuser:
    user: webuser
    grant: "ALTER, INSERT, DELETE, CREATE, SELECT, DROP, UPDATE, INDEX"
    database: 'MainDB.*'
    host: "localhost"
    grant_option: False
  flyway:
    user: flyway
    grant: "ALTER, INSERT, DELETE, CREATE, SELECT, DROP, UPDATE, INDEX"
    host: "localhost"
    database: 'MainDB.*'
    grant_option: False
    wp_access: False
  justin:
    user: justin
    grant: "all privileges"
    database: '*.*'
    host: "172.16.%"
    grant_option: False
    wp_access: False
  jeff:
    user: jeff
    grant: "all privileges"
    database: '*.*'
    host: "172.16.%"
    grant_option: False
    wp_access: False
  carl:
    user: carl
    grant: "all privileges"
    host: "172.16.%"
    database: '*.*'
    grant_option: False
    wp_access: False
  brian:
    user: brian
    grant: "all privileges"
    database: '*.*'
    host: "172.16.%"
    grant_option: False
    wp_access: False
  matt:
    user: matt
    grant: "all privileges"
    database: '*.*'
    host: "172.16.%"
    grant_option: False
    wp_access: False
  ethan:
    user: ethan
    grant: "all privileges"
    database: '*.*'
    host: "172.16.%"
    grant_option: False
    wp_access: False

Grant State

include:
  - mariadb-users
{% for user, args in pillar.get('mariadb-grants', {}).items() %}
{% if (grains['id'] == 'blogserver' and args['wp_access'] == True) or (grains['id'] != 'blogserver') %}
mariadb_{{ user }}_grant:
  mysql_grants.present:
    - grant: "{{ args['grant'] }}"
    - user: "{{ args['user'] }}"
    - database: "{{ args['database'] }}"
    - host: "{{ args['host'] }}"
    - grant_option: "{{ args['grant_option'] }}"
{% endif %}
{% endfor %}

Steps to Reproduce Issue

mysql -u root -e "CREATE DATABASE MainDB"
-- create other tables as desired, this is not a table level issue its a syntax issue, so i'll keep it shorter

sudo salt minion state.sls mariadb-grants

mysql.log output

 27 Query   SHOW GRANTS FOR 'matt'@'172.16.%'
28 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'matt'@'172.16.%'
31 Query    SHOW GRANTS FOR 'matt'@'172.16.%'
34 Query    SHOW GRANTS FOR 'justin'@'172.16.%'
35 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'justin'@'172.16.%'
38 Query    SHOW GRANTS FOR 'justin'@'172.16.%'
41 Query    SHOW GRANTS FOR 'jeff'@'172.16.%'
42 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'jeff'@'172.16.%'
45 Query    SHOW GRANTS FOR 'jeff'@'172.16.%'
48 Query    SHOW GRANTS FOR 'webuser'@'localhost'
49 Query    GRANT PROCESS ON `*`.`*` TO 'webuser'@'localhost'
52 Query    SHOW GRANTS FOR 'flyway'@'localhost'
53 Query    GRANT ALTER, INSERT, DELETE, CREATE, SELECT, DROP, UPDATE, INDEX ON `MainDB`.`*` TO 'flyway'@'localhost'
56 Query    SHOW GRANTS FOR 'flyway'@'localhost'
59 Query    SHOW GRANTS FOR 'brian'@'172.16.%'
60 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'brian'@'172.16.%'
63 Query    SHOW GRANTS FOR 'brian'@'172.16.%'
66 Query    SHOW GRANTS FOR 'ethan'@'172.16.%'
67 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'ethan'@'172.16.%'
70 Query    SHOW GRANTS FOR 'ethan'@'172.16.%'
73 Query    SHOW GRANTS FOR 'carl'@'172.16.%'
74 Query    GRANT ALL PRIVILEGES ON `*`.`*` TO 'carl'@'172.16.%'
77 Query    SHOW GRANTS FOR 'carl'@'172.16.%'
80 Query    SHOW GRANTS FOR 'webuser'@'localhost'
81 Query    GRANT ALTER, INSERT, DELETE, CREATE, SELECT, DROP, UPDATE, INDEX ON `MainDB`.`*` TO 'webuser'@'localhost'

login as the webuser and select from a table

mysql -u webuser -p
use MainDB;
select * from foo where id = 1;
**SELECT command denied to user 'webuser'@'localhost' for table 'foo'**
show grants for webuser@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------+
| Grants for webuser@localhost                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'localhost' IDENTIFIED BY PASSWORD '*AAAAAAAA040388C855C054A814D193C7TTTTTTTT' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `MainDB`.`*` TO 'webuser'@'localhost'    |
+-----------------------------------------------------------------------------------------------------------------+

Versions Report

(Provided by running salt --versions-report. Please also mention any differences in master/minion versions.)

salt-minion --versions-report
Salt Version:
           Salt: 2018.3.0

Dependency Versions:
           cffi: Not Installed
       cherrypy: Not Installed
       dateutil: 2.4.2
      docker-py: Not Installed
          gitdb: Not Installed
      gitpython: Not Installed
          ioflo: Not Installed
         Jinja2: 2.8
        libgit2: Not Installed
        libnacl: Not Installed
       M2Crypto: Not Installed
           Mako: 1.0.3
   msgpack-pure: Not Installed
 msgpack-python: 0.4.6
   mysql-python: Not Installed
      pycparser: Not Installed
       pycrypto: 2.6.1
   pycryptodome: Not Installed
         pygit2: Not Installed
         Python: 2.7.12 (default, Dec  4 2017, 14:50:18)
   python-gnupg: Not Installed
         PyYAML: 3.11
          PyZMQ: 15.2.0
           RAET: Not Installed
          smmap: Not Installed
        timelib: Not Installed
        Tornado: 4.2.1
            ZMQ: 4.1.4

System Versions:
           dist: Ubuntu 16.04 xenial
         locale: UTF-8
        machine: x86_64
        release: 4.4.0-1054-aws
         system: Linux
        version: Ubuntu 16.04 xenial

Salt Master

Salt Version:
           Salt: 2018.3.0

Dependency Versions:
           cffi: Not Installed
       cherrypy: Not Installed
       dateutil: 2.4.2
      docker-py: Not Installed
          gitdb: 0.6.4
      gitpython: 1.0.1
          ioflo: Not Installed
         Jinja2: 2.8
        libgit2: Not Installed
        libnacl: Not Installed
       M2Crypto: Not Installed
           Mako: 1.0.3
   msgpack-pure: Not Installed
 msgpack-python: 0.4.6
   mysql-python: Not Installed
      pycparser: Not Installed
       pycrypto: 2.6.1
   pycryptodome: Not Installed
         pygit2: Not Installed
         Python: 2.7.12 (default, Dec  4 2017, 14:50:18)
   python-gnupg: Not Installed
         PyYAML: 3.11
          PyZMQ: 15.2.0
           RAET: Not Installed
          smmap: 0.9.0
        timelib: Not Installed
        Tornado: 4.2.1
            ZMQ: 4.1.4

System Versions:
           dist: Ubuntu 16.04 xenial
         locale: UTF-8
        machine: x86_64
        release: 4.4.0-1054-aws
         system: Linux
        version: Ubuntu 16.04 xenial

Bug Duplicate

Source

jeffdyke

Most helpful comment

I have marked the pr https://github.com/saltstack/salt/pull/46919 to be backported for 2018.3.1

gtmanfred on 11 Apr 2018

👍2

All 4 comments

IMHO this is completely broken, and think it used to work just fine. I know i have another ticket about revokes, but i'm seeing them in the codebase now, but have not tested them. Putting backticks around around every * is completely broken, as a LAN user I can't see the database. I'd still like to contribute, perhaps this is where i start. I'd love for this module/state to be solid.

jeffdyke on 11 Apr 2018

This has been fixed here #46917