mySQL / MariaDB - Update a users privileges using the GRANT statement

by
Jeremy Canfield |
Updated: November 05 2021
| mySQL / MariaDB articles
This assume you are able to log into your MariaDB or mySQL server or you have configured passwordless authentication and will be using the -e command line option. The SELECT statement can be used to view a users privileges.
In this example, root can SELECT, INSERT and UPDATE, but John cannot.
MariaDB [(none)]> select user,host,Select_priv,Insert_priv,Update_priv from mysql.user;
+------------------------------------------------------------+
| user | host | Select_priv | Insert_priv | Update_priv |
+------------------------------------------------------------+
| root | 127.0.0.1 | Y | Y | Y |
| root | ::1 | Y | Y | Y |
| root | localhost | Y | Y | Y |
| John | localhost | N | N | N |
| John | % | N | N | N |
+------------------------------------------------------------+
5 rows in set (0.00 sec)
Give John select, insert and update privileges.
- 'localhost' are the local grants
- '%' are the remote grants (e.g. connecting to the database from a remote system)
MariaDB [(none)]> grant select, insert, update on *.* to 'John'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant select, insert, update on *.* to 'John'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
Did you find this article helpful?
If so, consider buying me a coffee over at