saltstack / saltstack/salt

[BUG] Cannot create a grant select on specific columns with salt.states.mysql_grants.present

Open
#63,244 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug needs-triage
Dominant language
Python
Stars
15.7k
Forks
5.6k
Avg merge
2d 44m
Merged PRs (30d)
80

Description

Description
SQL (and by extension, mysql) admits that a grant select may apply a subset of columns of a given table.

The concrete syntax is
GRANT SELECT (abc, def) ON test.table_test TO test_user@127.0.0.1

salt.states.mysql_grants.present does not appear to be able to generate this statement.

Setup
Beforehand (assuming mysql is set up), create an SQL table somewhere:

create table table_test (
  abc int not null primary key,
  def int,
  secret int
);

Now create the appropriate configuration in an .sls file.

test_user:
  mysql_user.present:
    - host: 127.0.0.1
    - password_hash: '*DEADBEEF'
test_user_grant:
  mysql_grants.present:
    - grant: SELECT (abc, def)
    - database: test.table_test
    - user: test_user
    - host: 127.0.0.1

Attempting to highstate results in:

 Failed to execute: "GRANT SELECT (abc, def) ON test.table_test TO test_user@127.0.0.1"
  • on-premises
  • bare-metal Ubuntu 18.04
  • not sure about packaging/install

Steps to Reproduce the behavior

state.highstate results in an error. The relevant debug clearly shows howthings get mangled when the query is tokenised:

[DEBUG   ] _grant_to_tokens entry 'GRANT SELECT (`abc`, `def`) ON `test`.`table_test` TO `test_user`@`127.0.0.1`'
[DEBUG   ] grant to token '`test_user`'::'`127.0.0.1`'::'['SELECT', 'SELECT.`abc`', 'SELECT.`def`']'::'`test`.`table_test`'

Expected behavior

[DEBUG   ] _grant_to_tokens entry 'GRANT SELECT (`abc`, `def`) ON `test`.`table_test` TO `test_user`@`127.0.0.1`'
[DEBUG   ] grant to token '`test_user`'::'`127.0.0.1`'::'['SELECT(`abc`, `def`)']'::'`test`.`table_test`'

(and the restricted column grant is applied)

Versions Report

Salt Version: Salt: 3004

Dependency Versions:
cffi: Not Installed
cherrypy: unknown
dateutil: 2.6.1
docker-py: Not Installed
gitdb: Not Installed
gitpython: Not Installed
Jinja2: 2.10
libgit2: Not Installed
M2Crypto: Not Installed
Mako: Not Installed
msgpack: 0.5.6
msgpack-pure: Not Installed
mysql-python: 1.3.10
pycparser: Not Installed
pycrypto: Not Installed
pycryptodome: 3.4.7
pygit2: Not Installed
Python: 3.6.9 (default, Jun 29 2022, 11:45:57)
python-gnupg: 0.4.1
PyYAML: 3.12
PyZMQ: 17.1.2
smmap: Not Installed
timelib: Not Installed
Tornado: 4.5.3
ZMQ: 4.2.5

System Versions:
dist: ubuntu 18.04 Bionic Beaver
locale: ANSI_X3.4-1968
machine: x86_64
release: 4.15.0-42-generic
system: Linux
version: Ubuntu 18.04 Bionic Beaver

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by locating the _grant_to_tokens entry point mentioned in the debug output and reproduce the issue with the provided .sls configuration and MySQL table. Compare the current tokenization with the expected single SELECT(abc, def) token, then verify with state.highstate that the restricted column grant is applied.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, python
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.