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. The following privileges can be granted.

• Alter_priv
• Create_priv
• Delete_priv
• Insert_priv
• Select_priv
• Update_priv

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)