0

SETUP:

MySQL DB in google cloud 5.6.3

Microsoft Excel 2013

Power Query Plug in.

MySQL user ODBC

Privileges = GRANT SELECT on SchemaName.TableName TO ODBC@'%'

ODBC connection made to the MySQL server by using Power Query > From Other Sources > From ODBC > Select Cloud MySQL server from DSN list.

Problem:

In the next screen "Table Navigator". The window not only lists all tables in the Schema, but also allows me to load each and every table successfully.

Connecting through the exact same steps but instead using the standard excel data menu option > from other source I am restricted to only the TableName as expected.

Trying to connect to any other table with windows, ODBC manager, also yields a denied connection as expected.

Question:

Is this some kind of security bypassing bug that is allowing this Power Query plugin to some how bypass my MySQL user Grant privileges statement?

Is my expectation of User Grant as a "Security" mechanism falsely placed.

Or am I just doing something wrong, and need to do privileges a different way in MySQL to have it actually secure???

Improve this question

1 Answer 1

Reset to default
0

Power Query doesn't do anything to bypass security mechanisms, so it is likely a configuration issue on the server.

If you have the latest version of Power Query (2.33 or higher), you can right-click on a step and choose "View Native Query" to see what query is being sent. This can help you reproduce the issue outside of Power Query.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

No it appears Power Query is definitely handling something different then what it appears. My ODBC user is locked down to 1 table, and read only. Connecting Windows ODBC and Excel or Access to that data source yields exactly that (Even signing in with MySQL Workbench yields the same restriction) Yet when I connect to the ODBC Source with Power pivot, I can read every table in the schema and then some.
What credentials are you using to access the ODBC source in Power Query? Are you using that user's credentials, or your own (Default will use your Windows credentials)?
I'm using the ODBC user credentials that I setup in the DB. The Windows user is not setup as a valid user in the DB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.