So I found myself explaining the differences with MySQL users and how they authenticate. First of all this information is not new but can be found here:
I will just show some real world examples to explain this.
MySQL uses the username as well as the login when it evaluates the permissions of a user. This is why a best practice is to remove the anonymous users.
For this example I will start off with the following users
MariaDB [(none)]> select user , host from mysql.user;+---------+-----------+| user | host |+---------+-----------+| root | 127.0.0.1 || root | ::1 || | centos64 || root | centos64 || | localhost || root | localhost |+---------+-----------+
I can log into the server even thought I do not have a username because it defaults down to anonymous@localhost.
# mysql -u nobody
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost |
+----------------+
MariaDB [(none)]> show grants;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
While I cannot do much I still got into the database.
Now when I pass a known user:
# mysql -u root -p
MariaDB [(none)]> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8CD56861FDADF7A264741F27D502D1A8DAE0A8F7' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
Currently the root user has 4 different accounts.
MariaDB [(none)]> select user , host from mysql.user where user = 'root';+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || root | ::1 || root | centos64 || root | localhost |+------+-----------+
These are all different accounts. I only need one of them though. I only use the root account via local host connections.
MariaDB [(none)]> DROP USER 'root'@'127.0.0.1';Query OK, 0 rows affected (0.33 sec)
MariaDB [(none)]> DROP USER 'root'@'centos64';Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> DROP USER 'root'@'::1';Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user , host from mysql.user where user = 'root';+------+-----------+| user | host |+------+-----------+| root | localhost |+------+-----------+
So what did I mean by 4 different accounts? They all the root user account. No.. They are root and whatever host. So each account (user + host) could have different permissions.
So we will make an example next to show the difference.
So the server in this example has two network interfaces. So I will create a user per all access points each with different permissions to show the differences. I will set the same password but those could be different as well.
MariaDB [(none)]> GRANT SELECT ON *.* TO 'anothermysqldba'@'10.0.2.15' IDENTIFIED BY '<cleartext password>';Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE , DROP, RELOAD, SHUTDOWN, PROCESS, FILE, INDEX, ALTER ON *.* TO 'anothermysqldba'@'192.168.0.%' IDENTIFIED BY '<cleartext password>';Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'anothermysqldba'@'localhost' IDENTIFIED BY '<cleartext password>' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user , host from mysql.user where user = 'anothermysqldba';
+-----------------+-------------+
| user | host |
+-----------------+-------------+
| anothermysqldba | 10.0.2.15 |
| anothermysqldba | 192.168.0.% |
| anothermysqldba | localhost |
+-----------------+-------------+
So what does this mean? Even though the usernames are the same because MySQL uses the host as well to validate a user every user access point as different permissions.
While you might trust some of your developers, this is often a good idea to limit some of their access if they are working remotely or via their scripts. If they need to do something that does not involve you and they have to ssh to the server itself and login to perform other tasks. This is of course dependent on the relationship and work flow of your business and/or applications.
Using the 10.0.2.15 host the user only has select access.
# mysql -h 10.0.2.15 -u anothermysqldba -p
MariaDB [(none)]> select current_user();
+---------------------------+
| current_user() |
+---------------------------+
| anothermysqldba@10.0.2.15 |
+---------------------------+
MariaDB [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@10.0.2.15 |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'anothermysqldba'@'10.0.2.15' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' |
+-------------------------------------------------------------------------------------------------------------------------+
Using the 192.168.0.26 host the user has more access. This account is also set to access across anything under the 192.168.0/255 (%) subnet.
# mysql -h 192.168.0.26 -u anothermysqldba -p
MariaDB [(none)]> select current_user();
+-----------------------------+
| current_user() |
+-----------------------------+
| anothermysqldba@192.168.0.% |
+-----------------------------+
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@192.168.0.% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, INDEX, ALTER ON *.* TO 'anothermysqldba'@'192.168.0.%' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
So as you will assume the localhost user has the full access account.
# mysql -u anothermysqldba -p
MariaDB [(none)]> select current_user();
+---------------------------+
| current_user() |
+---------------------------+
| anothermysqldba@localhost |
+---------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for anothermysqldba@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'anothermysqldba'@'localhost' IDENTIFIED BY PASSWORD '*31360D7EE84BE965C0E759179FC61B6943BCA64F' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
Now as I said before it is best practice to remove the anonymous users. Using the mysql_secure_installation for installation will help with his as well.
Why does it matter?
# mysql -u anothremysqldba -p
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.00
The simple example... is above and just avoid simple mistakes. A typo allowed access to the database, it should not allow this.
So remove then and test again.
MariaDB [(none)]> DROP USER ''@'localhost';Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> DROP USER ''@'centos64';Query OK, 0 rows affected (0.00 sec)
# mysql -u anothremysqldba -p
Enter password:
ERROR 1045 (28000): Access denied for user 'anothremysqldba'@'localhost' (using password: YES
So what does it mean if your database is created with mostly with % as the hostname or strict ip addresses and no wildcards ?
This would be a security and business decision on your part. Personally neither is a valid option in my opinion. Nothing but a wildcard (%) allows a robust access point but if the firewall has a failure it also opens the access to remote out of network users. A strict ip address per user is also rather strict in my opinion but it depends on the user. For example even with an account that has been created for replication I prefer to still use the ip address with a % ie: 192.168.0.% . IP addresses do change (even static changes for remote users the DBA is often not told) and it often results in people coming to the DBA with complaints over access failure.
I once worked with a person that limited every user access locked to a static ip address. This person had to constantly alter grant statements. People have to be able to work easily and effectively while you can ensure security on your end.
Now of course this is a simple example. MySQL does also offer more authentication options
