How can I check if current user has certain privilege on MySQL/MariaDB?

Question

How can I easily check if my current user has a certain privilege, e.g. if he can run the following statement:

INSERT INTO testdb (id) VALUES ('1');

Of course, I could try it but I need the information before I know what to insert. To put in anything doesn't work since I don't have the DELETE privilege. Using SHOW GRANTS; isn't sufficient for me since it would be very complicated, because I could have a certain privilege on so many ways, e.g. directly, through a role, only for my host, etc..

Is there an easier way to check my privileges?

Thank you very much!

You could `START TRANSACTION;` then try your INSERT and if it succeeds, then `ROLLBACK;` That will avoid adding new rows, but it will have side-effects like causing locks, running triggers, and incrementing the auto-increment primary key if there is one. — Bill Karwin, Sep 19 '19 at 13:24
Ok, thank you. And I guess I need to check the error code, because I could also have an error because of a duplicate key entry. — Lukas, Sep 19 '19 at 14:06
Yes, you should check for error cases as a standard part of making any SQL calls anyway. — Bill Karwin, Sep 19 '19 at 14:10
A bigger side-effect with `START TRANSACTION` would be if the table is not transactional. Then the row will be inserted regardless `ROLLBACK`. For `SHOW GRANTS`, it can indeed be complicated to parse, but examples that you listed shouldn't be a problem. If you have a privilege through a role, it will only work if you have this role active, in which case it will show in `SHOW GRANTS` (not just the role, but the privileges of the role). Same for the host, if you logged in as a user of a different host, you'll have a different set of privileges and they won't be shown in `SHOW GRANTS`. — elenst, Sep 19 '19 at 21:43

EternalHour · Accepted Answer · 2019-09-20T23:51:30.103

2

You could use the INFORMATION_SCHEMA to query for the required privilege based on the action needing to be performed.

SELECT IF(COUNT(*) > 0, TRUE, FALSE) AS Allowed
FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE LIKE '%user%' AND PRIVILEGE_TYPE = 'INSERT'

edited Sep 20 '19 at 23:51

answered Sep 20 '19 at 23:28

EternalHour

8,308
6
38
57

This doesn't work. When privilege_type is USAGE, the statement will return FALSE, but INSERT is allowed. – Georg Richter Sep 21 '19 at 16:46
@GeorgRichter - `USAGE` doesn't allow inserting, see this [question](https://stackoverflow.com/questions/2126225/why-is-a-grant-usage-created-the-first-time-i-grant-a-user-privileges). But on that topic this probably won't work with role based privileges. – EternalHour Sep 21 '19 at 17:13

Georg Richter · Answer 2 · 2019-09-20T23:12:10.467

The begin/rollback method mentioned in the comments could be expensive and will not work for non transactional storage engines.

Both MariaDB and MySQL provide prepared statements, which you just don't execute. During prepare the server already checks the privileges and will return an error.

Example in C:

const char *stmt_str= "INSERT INTO t2 VALUES (?)";
if (mysql_stmt_prepare(stmt, stmt_str, strlen(stmt_str))
{
  printf("Error: %s\n", mysql_stmt_error(stmt));
}

Example in SQL:

mysql> prepare my from "insert into t2 values (?)";
ERROR 1142 (42000): INSERT command denied to user 'foo'@'localhost' for table 't2'

This solution however is limited, since not all SQL statements can be prepared. The list of permitted statements can be found in the MariaDB Documentation.

In case the prepare command suceeded, don't forget to free the prepared statement.

If you only need to check SELECT, INSERT, UPDATE, DELETE privileges you can also use the EXPLAIN command which also checks permissions.

How can I check if current user has certain privilege on MySQL/MariaDB?

2 Answers2