3

While using an already established mysqli connection to a MySQL database from PHP, I'm looking for a simple way to check if this connection (i.e. the authenticated user for this connection, if any) has some desired access to a certain table (or at least any access to the table whatsoever).

If it matters, the table in question is in another database than the currently USED one, and I'm trying to access it by the following kind of query:

SELECT * FROM some_other_database_on_the_same_server_as_the_currently_used_database.some_table WHERE id = 1

My research so far has only found the ridiculously complex solution of manually parsing and resolving the depressingly messy raw-string output of the SHOW GRANTS command, which I just cannot accept being the only way of doing this!?

Things I have tried (and failed) so far are the following:

1. To simply perform the desired operation in question against the table in question, and evaluate the error codes if it fails. To my big surprise, a SELECT query (issued e.g. with the mysqli_query() API) simply returns False if I don't have the appropriate permissions for the table in question, without any error code or error message being reported at all (inspected both manually in the debugger on the connection object and by means of the mysqli_error() API). The problem with this is that False will also be returned if e.g. the queried table doesn't exist at all, so I cannot know from this if it failed because of permission problems or because of something completely different.

2. To search for some kind of equivalent of the HAS_PERMS_BY_NAME command of Microsoft SQL Server, which I always come up empty-handed with though, consistently just being politely herded back by Google to the extremely clumsy SHOW GRANTS command of MySQL.

So my question is, again:

How can I, with no holds (or hacks) barred, check if my mysqli connection has some desired access (or at least read access if nothing else) to a certain MySQL table?

There really must be some easy way of doing this, right?

3

Perhaps this can lead to some SELECTs to solve your problem?

For the 'ana_index':

mysql> SHOW GRANTS FOR ana_index@localhost;
| GRANT USAGE ON *.* TO 'ana_index'@'localhost' IDENTIFIED BY PASSWORD '*...'
| GRANT ALL PRIVILEGES ON `test`.* TO 'ana_index'@'localhost'

These tables were populated:

mysql> SELECT * FROM mysql.user WHERE user = 'ana_index'\G
*************************** 1. row ***************************
              Host: localhost
              User: ana_index
          Password: *1E62D0DB2482ED4455102B56D823285C02BAAF8F
       Select_priv: N
       Insert_priv: N
       Update_priv: N
       Delete_priv: N
       Create_priv: N
(etc)

mysql> SELECT * FROM mysql.db WHERE user = 'ana_index'\G
*************************** 1. row ***************************
             Host: localhost
               Db: test
             User: ana_index
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: N
(etc)

mysql.user says what permissions, if any, the user has for all databases.

mysql.db says what permissions the user has for particular database(s), test in this example.

  • Thanks a lot for your answer! Do you know of any similar technique for extracting permissions on the table level though (which the question was about more specifically)? – QuestionOverflow Sep 3 '16 at 21:33
  • mysql.table... – Rick James Sep 3 '16 at 21:56
  • 1
    After experimenting some with this, it seems like only the root user can access these tables you mention (or at least not my normal user anyway), which in that case seemingly counteracts the entire purpose of the question (i.e. asking for a method to check if my current mysqli connection has some desired access, or at least read access if nothing else, to a certain MySQL table). Or am I missing something? More specifically, the call mysqli_query($db_conn_handle, 'SELECT * FROM mysql.tables_priv') only returns false when I attempt it from my PHP program (same happens for mysql.db etc)? – QuestionOverflow Sep 7 '16 at 13:48
  • mysqli_query does not return a resultset, it returns a handle with which you can fetch the rows in various ways. The docs might be a quick way to see what to do, then study the PHP docs. – Rick James Sep 7 '16 at 15:15
  • Thanks, I'm quite aware of how mysqli_query works, including that when it returns false (instead of such a handle) the query failed (contrary to just not returning any result). This, ironically enough, brings us back to the exact dilemma behind my question to begin with - how can I know if such a failure is due to a permission problem or something else (e.g. the table not existing to begin with)? Since the same query succeeds under the root user though, I can only assume that a low-privilege user is not permitted to access the tables you mention, in turn invalidating this as a solution? – QuestionOverflow Sep 7 '16 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.