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

Updated:   |  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 Buy Me A Coffee



Comments


Add a Comment


Please enter 83150b in the box below so that we can be sure you are a human.