How to grant mysql user super and definer priveleges

In this tutorial will show how to grant priveleges commonly requested by users for Magento and other CMS which use triggers and definers.

These are hard to adjust after the site is moved if the database user changes.

For the sake of this guide we are going to state the linux username of the account is “someuser” the database name is “someuser_database” and the database username is “someuser_dbuser“.

So in these examples copy out the commands and find and replace with your desired username as applicable.

First we are going to login to the server as root. If you are on most OS like cPanel or another where passwordless mysql root is setup this command should work out of the box. This will show the current grants if any exist. Check this first so you can see how it looks.

mysql -e "SHOW GRANTS for [email protected];"

This is how it looks. Note that it surrounds the username with ” if you specify those when asking to see the grants it will be confusing and be like nope… So it shows them when queried like ‘someuser_dbuser’@’localhost’ but do not query it like that unless your server needs them as it might give you an error like it would if none exist.

[[email protected]:/]$ mysql -e "SHOW GRANTS for [email protected];"
| Grants for [email protected]                                                                                   |
| GRANT SUPER ON *.* TO 'someuser_dbuser'@'localhost' IDENTIFIED BY PASSWORD '*randomcharstringhere' |
| GRANT ALL PRIVILEGES ON `someuser\_database`.* TO 'someuser_dbuser'@'localhost'                                            |
| GRANT ALL PRIVILEGES ON `someuser_database`.* TO 'someuser_dbuser'@'localhost'                                             |
| GRANT SELECT ON `mysql`.`proc` TO 'someuser_dbuser'@'localhost'                                                        |
[[email protected]:/]$ 

Now that you know how to check for the grants let go and add the grants and privileges as desired.

To Grant DEFINER privileges. Login to mysql as root and execute this query. Unfortunately I have not been able to get this to work in the way the above show grants works.

GRANT SELECT ON mysql.proc to 'someuser_dbuser'@'localhost';

The above corresponds to this the grant:

GRANT SELECT ON `mysql`.`proc` TO 'someuser_dbuser'@'localhost'

If you also need to grant SUPER privileges this can also be done like the below. Please note this grants the user SUPER privileges on all tables and DB and is not something one should do on a shared or multi-user server.

GRANT SUPER ON *.* TO [email protected]'localhost';

To grant all privileges for a single user on a single database the below can used.

GRANT ALL PRIVILEGES ON someuser_database.* TO 'someuser_dbuser'@'localhost';

You should now be able to run the mysql grants check and see if what you added worked.

mysql -e "SHOW GRANTS for [email protected];"

If you need to test if the user has access to the triggers/definers the below command can be used with the database user’s password. This will dump the database including the routines procedures triggers etc.

mysqldump --routines -u someuser_dbuser -p someuser_database > someuser_database_with_routines_$(date +%F_%H-%M).sql

Frequently what I see is that users after switching hosts or migrating between a dev account and main the definers are mismatched with the new user. This can cause weird errors and prevent them being modified by the user. To fix this one typically needs to dump the database and use sed to find and replace all the incorrect DEFINER usernames from the old to new.

Identifying the DEFINER’s is important so you know what needs replaced. They only show when the database is dumped with the routines.

[email protected] [~]# grep 'DEFINER' someuser_database_with_routines_2020-03-17_19-34.sql 
/*!50003 CREATE*/ /*!50017 DEFINER=`someuser_dbuser`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert AFTER INSERT ON catalog_category_entity FOR EACH ROW
/*!50003 CREATE*/ /*!50017 DEFINER=`someotheruser_dbuser`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_update AFTER UPDATE ON catalog_category_entity FOR EACH ROW

If there are any that are mismatched with the current user you will need to use sed to find and replace only the DEFINER lines with the wrong username.

sed -i 's/oldusername/newusername/g' $DB.sql