MySQL User connections

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:

• http://dev.mysql.com/doc/refman/5.6/en/connection-access.html

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

• http://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html
• http://dev.mysql.com/doc/refman/5.6/en/pam-authentication-plugin.html
• https://mariadb.com/blog/new-feature-pam-plugin-mariadb
• https://mariadb.com/kb/en/mariadb/documentation/mariadb-plugins/pam-authentication-plugin/

MySQL Users :: Grants :: mysql_config_editor :: Security

Secure access to the database is likely priority number one for any database administrator. If it is not then you need to seriously look into why it is not.

General guidelines via the manual are already available:

• http://dev.mysql.com/doc/refman/5.6/en/password-security.html
• http://dev.mysql.com/doc/refman/5.6/en/password-security-user.html
• http://dev.mysql.com/doc/refman/5.6/en/create-user.html
• http://dev.mysql.com/doc/refman/5.6/en/grant.html
• http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

One of the primary issues with security in MySQL is of course the permissions that you give users.
These are a few simply guidelines.

First keep "super user" or "root" accounts to a minimum. A user with full access or "GRANT ALL" will still have access when you have reached your max connections. So the last thing you would want is a program to be executing commands with a user with full access.

Keep in mind what types of accounts you are creating. You can limit a user to MAX QUERIES, MAX UPDATES, MAX CONNECTIONS and MAX USER CONNECTIONS per HOUR.

Keep in mind of the network environment your users are connecting from. If users are going to be using DHCP network addresses within the same subnet you would just be creating more work for yourself if you limited them to a single IP. You can still limit them to the subnet though with a wildcard. For example '192.168.0.2' versus '192.168.0.%'

Stay away from entire wildcard access for host and users.



> CREATE USER ''@'192.168.0.56' ;
Query OK, 0 rows affected (0.02 sec)

> show grants for ''@'192.168.0.56';
+-----------------------------------------+
| Grants for @192.168.0.56                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO ''@'192.168.0.56' |
+-----------------------------------------+



This would leave you wide open for anyone from 192.168.0.56 and is not a smart secure thing to do.
It could also violate other accounts from 192.168.0.56 because MySQL checks host first and username second.


> GRANT SELECT ON test.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY 'somepassword';

> show grants for 'exampleuser'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@192.168.0.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT ON `test`.* TO 'exampleuser'@'192.168.0.%'                                                              |
+----------------------------------------------------------------------------------------------------------------------+



This will allow selects only for exampleuser from '192.168.0.%'. You must also keep in mind that if exampleuser is connecting from LOCAL HOST that the system will likely user localhost first before the 192.168.0.% subnet address unless the user used the subnet address the host to connect to.

This means that you can create one user and password with different privileges per host.



> SHOW GRANTS FOR 'exampleuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'localhost' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'exampleuser'@'localhost'                                              |
+--------------------------------------------------------------------------------------------------------------------+



Try your best to not use the --password=<password> option via the mysql client. You can use -p to prompt for a password.

You also have the option with MySQL 5.6 to use the MySQL Configuration Utility.


# mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:







# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost





# mysql 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql  --login-path=local

Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';

You do have options to name different paths like local or remote and etc as well. So you can encrypt more than one access user account in your ~/.mylogin.cnf file that is created once you use the set command.

If you have shell scripts that use the mysql client and likely then have passwords in the scripts updating them to use the "--login-path=" is a more secure way to go.


Of course when you no longer need a user... Drop the user.


> DROP USER 'exampleuser'@'localhost';