patternsqlMinor
Simplest way to check if current user has permission to a MySQL table (from PHP / mysqli) for some desired operation?
Viewed 0 times
fromsimplestuserpermissionwayphpdesiredoperationhasmysql
Problem
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
My research so far has only found the ridiculously complex solution of manually parsing and resolving the depressingly messy raw-string output of the
Things I have tried (and failed) so far are the following:
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 M
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 = 1My 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
SELECTquery (issued e.g. with themysqli_query()API) simply returnsFalseif 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 themysqli_error()API). The problem with this is thatFalsewill 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_NAMEcommand of Microsoft SQL Server, which I always come up empty-handed with though, consistently just being politely herded back by Google to the extremely clumsySHOW GRANTScommand 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 M
Solution
Perhaps this can lead to some
For the 'ana_index':
These tables were populated:
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.Code Snippets
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'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)Context
StackExchange Database Administrators Q#148719, answer score: 3
Revisions (0)
No revisions yet.