Search code examples
mysqlmariadbprivileges

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

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!

Solution

  • 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'