Managing database access with MariaDB or MySQL

MariaDB [mysql]> select user from mysql.user;
+---------------+
| user          |
+---------------+
| jan@localhost |
| root          |
| jan           |
| johnathandoe  |
| lucilleball   |
| maryalamb     |
| root          |
+---------------+
7 rows in set (0.00 sec)

MariaDB [mysql]>

In the above list, root appears twice because the root account can connect from two hosts. If you use SELECT user, host from mysql.user;, you can see the hosts hosts/IP addresses from which users can connect to databases.

MariaDB [mysql]> SELECT user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jan@localhost | %         |
| root          | 127.0.0.1 |
| jan           | localhost |
| johnathandoe  | localhost |
| lucilleball   | localhost |
| maryalamb     | localhost |
| root          | localhost |
+---------------+-----------+
7 rows in set (0.00 sec)

MariaDB [mysql]>

In the example above, root can connect from 127.0.0.1 and the localhost address, so the entries are essentially the same, giving root access from the system itself.

If you wish to determine what databases an account can access you can select a user from the list above and with the host value query mysql.user as shown below to determine the privileges granted to the user.

MariaDB [(none)]> show grants for 'maryalamb'@'localhost';
+-----------------------------------------------------------------------------------------+
| Grants for maryalamb@localhost                                                          |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maryalamb'@'localhost' IDENTIFIED BY PASSWORD '0fbc2b151852375a' |
| GRANT SELECT ON `shepherd`.* TO 'maryalamb'@'localhost'                                 |
| GRANT SELECT ON `big_bad_wolves`.* TO 'maryalamb'@'localhost'                           |
+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

In the example above the user maryalamb has SELECT access to the shepherd and big_bad_wolves databases. Of course, a user may have more extensive access as shown in the example below for another user.

MariaDB [(none)]> show grants for 'jan'@'localhost';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------+
| Grants for jan@localhost                                                      
                                                                               
                                                                   |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jan'@'localhost' IDENTIFIED BY PASSWORD '566c024c36d444
b8'                                                                             
                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, 
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE RO
UTINE, ALTER ROUTINE ON `sales`.* TO 'jan'@'localhost'        |

If you want to grant all types of access to a database to a user, you can use a GRANT ALL command as shown below.

MariaDB [(none)]> GRANT ALL on mydatabase.* TO maryalamb;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]>

In the above example, the user maryalamb is granted all available privileges to the database named mydatabase. If I then realize I meant to grant access to maryalamb@localhost, I can revoke all privileges for the user maryalamb with the command

REVOKE ALL
PRIVILEGES, GRANT OPTION FROM maryalamb;

which drops all global, database, table, column, and routine privileges for the named user, maryalamb. The command would affect other databases as well as the mydatabase one. But, if I list the users in mysql.user after I issued the command, I see the following:

MariaDB [(none)]> select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jan@localhost | %         |
| maryalamb     | %         |
| root          | 127.0.0.1 |
| jan           | localhost |
| johnathandoe  | localhost |
| lucilleball   | localhost |
| maryalamb     | localhost |
| root          | localhost |
+---------------+-----------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

MariaDB [(none)]> DROP USER maryalamb;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jan@localhost | %         |
| root          | 127.0.0.1 |
| jan           | localhost |
| johnathandoe  | localhost |
| lucilleball   | localhost |
| maryalamb     | localhost |
| root          | localhost |
+---------------+-----------+
7 rows in set (0.00 sec)

MariaDB [(none)]>

If I then want to grant specific privileges to the user maryalamb@localhost on the database mydatabase, I can use the following GRANT command:

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE on mydatabase.* TO 'maryalamb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'maryalamb'@'localhost';
+------------------------------------------------------------------------------------------+
| Grants for maryalamb@localhost                                                           |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maryalamb'@'localhost' IDENTIFIED BY PASSWORD '0fbc2b151852375a'  |
| GRANT SELECT ON `shepherd`.* TO 'maryalamb'@'localhost'                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mydatabase`.* TO 'maryalamb'@'localhost' |
| GRANT SELECT ON `big_bad_wolves`.* TO 'maryalamb'@'localhost'                            |
+------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>