[BUG] GRANT SELECT not working for only 1 user on 2 specific databases. Works on others

mioux commented 4 years ago

Your setup

Formula commit hash / release tag

Latest (at 2020/04/02 => 8dffbaf)

Versions reports (master & minion)

Minion :

Salt Version:
        Salt: 2019.2.3

Dependency Versions:
        cffi: 1.14.0
    cherrypy: Not Installed
    dateutil: 2.5.3
    docker-py: Not Installed
        gitdb: Not Installed
    gitpython: Not Installed
        ioflo: Not Installed
        Jinja2: 2.9.4
        libgit2: Not Installed
        libnacl: Not Installed
    M2Crypto: Not Installed
        Mako: Not Installed
msgpack-pure: Not Installed
msgpack-python: 0.4.8
mysql-python: 1.3.7
    pycparser: 2.19
    pycrypto: 2.6.1
pycryptodome: Not Installed
        pygit2: Not Installed
        Python: 3.5.3 (default, Sep 27 2018, 17:25:39)
python-gnupg: Not Installed
        PyYAML: 3.12
        PyZMQ: 16.0.2
        RAET: Not Installed
        smmap: Not Installed
        timelib: Not Installed
        Tornado: 4.4.3
            ZMQ: 4.2.1

System Versions:
        dist: debian 9.11
        locale: UTF-8
        machine: x86_64
        release: 4.9.0-11-amd64
        system: Linux
        version: debian 9.11

Master :

Salt Version:
        Salt: 2019.2.0

Dependency Versions:
        cffi: Not Installed
    cherrypy: Not Installed
    dateutil: 2.5.3
    docker-py: Not Installed
        gitdb: Not Installed
    gitpython: Not Installed
        ioflo: Not Installed
        Jinja2: 2.9.4
        libgit2: Not Installed
        libnacl: Not Installed
    M2Crypto: Not Installed
        Mako: Not Installed
msgpack-pure: Not Installed
msgpack-python: 0.4.8
mysql-python: Not Installed
    pycparser: Not Installed
    pycrypto: 2.6.1
pycryptodome: Not Installed
        pygit2: Not Installed
        Python: 3.5.3 (default, Sep 27 2018, 17:25:39)
python-gnupg: Not Installed
        PyYAML: 3.12
        PyZMQ: 16.0.2
        RAET: Not Installed
        smmap: Not Installed
        timelib: Not Installed
        Tornado: 4.4.3
            ZMQ: 4.2.1

System Versions:
        dist: debian 9.11 
        locale: UTF-8
        machine: x86_64
        release: 4.9.0-11-amd64
        system: Linux
        version: debian 9.11

Pillar / config used

mysql:
  user:
    fname:
    password: 'thepassword'
    hosts:
      - '10.1.1.%'
      - '10.4.1.15'
    databases:
        - database: 'db1'
          grants: ['select', 'insert', 'update', 'delete']
        - database: 'db2
          grants: ['select']
        - database: 'db3'
          grants: ['select', 'SHOW VIEW' ]
        - database: 'db4'
          grants: ['select']
        - database: 'db5
          grants: ['select']
        - database: 'db6'
          grants: ['select', 'update']
        - database: 'db7
          grants: ['select']
        - database: 'db7'
          grants: ['SELECT']
        - database: 'db8'
          grants: ['SELECT']
        - database: 'db9'
          grants: ['select']
        - database: 'db10'
          grants: ['select']

Bug details

Describe the bug

Select rights does not applies on some DB. I added thoses manually, by copying the query in the error message, it works

----------
          ID: mysql_user_fname_10.1.1.%
    Function: mysql_user.present
        Name: fname
      Result: True
     Comment: Password for user fname@10.1.1.% has been changed
     Started: 14:55:09.954813
    Duration: 28.947 ms
     Changes:   
              ----------
              fname:
                  Updated
----------
          ID: mysql_user_fname_10.1.1.%_2
    Function: mysql_grants.present
        Name: fname_db2_all
      Result: True
     Comment: Grant select,SHOW VIEW on db2.* to fname@10.1.1.% has been added
     Started: 14:55:10.033443
    Duration: 32.76 ms
     Changes:   
              ----------
              fname_db2_all:
                  Present
----------
          ID: mysql_user_fname_10.1.1.%_7
    Function: mysql_grants.present
        Name: fname_db7_all
      Result: False
     Comment: Failed to execute: "GRANT SELECT ON db7.* TO fname@10.1.1.%"
     Started: 14:55:10.150499
    Duration: 47.112 ms
     Changes:   
----------
          ID: mysql_user_fname_10.1.1.%_8
    Function: mysql_grants.present
        Name: fname_db8_all
      Result: False
     Comment: Failed to execute: "GRANT SELECT ON db8.* TO fname@10.1.1.%"
     Started: 14:55:10.204562
    Duration: 38.451 ms
     Changes:   
----------
          ID: mysql_user_fname_10.4.1.15
    Function: mysql_user.present
        Name: fname
      Result: True
     Comment: Password for user fname@10.4.1.15 has been changed
     Started: 14:55:10.281057
    Duration: 31.76 ms
     Changes:   
              ----------
              fname:
                  Updated
----------
          ID: mysql_user_fname_10.4.1.15_2
    Function: mysql_grants.present
        Name: fname_db2_all
      Result: True
     Comment: Grant select,SHOW VIEW on db2.* to fname@10.4.1.15 has been added
     Started: 14:55:10.361527
    Duration: 49.012 ms
     Changes:   
              ----------
              fname_db2_all:
                  Present
----------
          ID: mysql_user_fname_10.4.1.15_7
    Function: mysql_grants.present
        Name: fname_db7_all
      Result: False
     Comment: Failed to execute: "GRANT SELECT ON db7.* TO fname@10.4.1.15"
     Started: 14:55:10.515102
    Duration: 29.207 ms
     Changes:   
----------
          ID: mysql_user_fname_10.4.1.15_8
    Function: mysql_grants.present
        Name: fname_db8_all
      Result: False
     Comment: Failed to execute: "GRANT SELECT ON db8.* TO fname@10.4.1.15"
     Started: 14:55:10.550981
    Duration: 28.609 ms
     Changes:

Steps to reproduce the bug

Juste do a state.apply

Expected behaviour

Rights are successfully applied

Attempts to fix the bug

Tried to look for non printable char
Tried to remove the erroneous section then re-create it
Tried to remove the erroneous section then re-create it by copy-pasting a working one
Tried to write grants upper case or lower case

Additional context

The user file is separated from the server specific file

We have 3 servers. "1" is the main, "2" and "3" are slaves

pillar/mysql/prdsqlx.sls << this is the common cpnfiguration pillar/mysql/prdsql[num].sls << this is the specific configuration by server (id and ram is different) pillar/users/mysql.sls << this contains only the users

And in pillar/top.sls

prod:
  'prdsql*'
    - mysql.prdsqlx.sls
  'prdsql1:
    - mysql.prdsql[num].sls
    - users.mysql.sls
  'prdsql2:
    - mysql.prdsql2.sls
  'prdsql3:
    - mysql.prdsql3.sls

mioux commented 4 years ago

FYI : the error is only for this user and 2 specific schemas. Some other users have the same configuration, it works

mioux commented 4 years ago

I've found the problem.

The user already have "ALL PRIVILEGES", I want to downgrade it's rights to just SELECT

If I manually remove "ALL PRIVILEGES" and then sate.apply, it works

daks commented 4 years ago

nice.

Maybe documentation could be more explicit about how the formula reacts when a use already exists or when it already exists and we downgrade its permissions.

If you have any idea of how this could be done, and want to prepare a PR or discuss it here, feel free to! :)

mioux commented 4 years ago

You could run "REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'host' and then reapply it for example

saltstack-formulas / mysql-formula