In this article, we will demonstrate how to have a single MySQL database user account that can connect from specific hosts.
We would usually implement it by creating separate user accounts with the same username but different hosts/IPs like <USER>@<HOST1>, <USER>@<HOST2> …. <USER>@<HOSTn>. Then, give those users the same grants(privileges/roles) and settings(password, SSL, etc).
Instead of doing such, we will create a single user (‘’@’%’) that can connect from all hosts and then use the init_connect server parameter to call a procedure that would implement the host restriction for a user. This will make managing user account settings and privileges easier as we only need to apply it to one database user account.
Process
Client hostname and IP:
|
1 2 |
c7-171 192.168.122.171 c7-172 192.168.122.172 |
Create the schema with a table containing the host allow list. The table will be used to check with the output of the USER() function.
|
1 2 3 4 5 6 7 8 |
CREATE DATABASE IF NOT EXISTS logins; USE logins; CREATE TABLE allow_list( user VARCHAR(32) NOT NULL COMMENT 'mysql.user.User', host VARCHAR(255) NOT NULL COMMENT 'mysql.user.Host', remarks VARCHAR(100), PRIMARY KEY (user, host) ); |
Create the stored procedure that would implement the host restriction for users that have a value host = ‘%’ (from CURRENT_USER()) and is found in the allow_list table (base from USER()).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DELIMITER // CREATE PROCEDURE sp_init_connect(IN p_current_user_host VARCHAR(300)) SQL SECURITY DEFINER BEGIN DECLARE v_message_text VARCHAR(128); DECLARE v_exists BOOLEAN DEFAULT 0; DECLARE v_user VARCHAR(32) ; DECLARE v_host VARCHAR(255) ; DECLARE c_host VARCHAR(255) ; SET v_user = SUBSTRING_INDEX(USER(), '@', 1); SET v_host = SUBSTRING_INDEX(USER(), '@', -1); SET c_host = SUBSTRING_INDEX(p_current_user_host, '@', -1); SELECT 1 INTO v_exists FROM allow_list WHERE user = v_user AND host = v_host LIMIT 1; IF c_host = '%' THEN /* Only for users that can connect from any host */ IF NOT v_exists THEN SET v_message_text=CONCAT('User not in ', database(), '.allow_list CURRENT_USER[', p_current_user_host, ']'); /* Set the error message as short as possible so it does not get truncated in the log. */ SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message_text, MYSQL_ERRNO = 1001; END IF; END IF; END; // DELIMITER ; |
Set init_connect server parameter.
|
1 |
SET GLOBAL init_connect = "CALL logins.sp_init_connect(CURRENT_USER())"; |
Add to my.cnf to persist upon restart.
|
1 2 |
[mysqld] init_connect = 'CALL logins.sp_init_connect(CURRENT_USER())' |
Note: We call the function CURRENT_USER() outside the procedure as calling it inside will give the value of the procedure definer and not the currently connected user.
Create database users.
|
1 2 |
CREATE USER 'app1'@'%' IDENTIFIED BY 'Secret1!' ; CREATE USER 'app2'@'192.%' IDENTIFIED BY 'Secret1!' ; |
Grant database-level EXECUTE privilege because recreating(DROP then CREATE) the PROCEDURE will cause the grants given on that specific PROCEDURE to be lost.
|
1 2 |
GRANT EXECUTE ON logins.* TO 'app1'@'%'; GRANT EXECUTE ON logins.* TO 'app2'@'192.%'; |
Add to our mysql server /etc/hosts. If you have a DNS server, you may add the host to it instead.
|
1 |
192.168.122.172 c7-172.example.com |
Note: If you made changes to your host file or DNS for an IP that already has a HOST value in the performance_schema.host_cache table, you must do a FLUSH HOSTS.
Test connection for ‘app1’@’%’.
Since we still don’t have entries in the allow_list table, we should expect our connection to fail.
From c7-171(192.168.122.171).
|
1 2 3 |
[root@c7-171 ~]# mysql -uapp1 -pSecret1! -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query |
From c7-172(192.168.122.172).
|
1 2 3 |
[root@c7-172 ~]# mysql -uapp1 -pSecret1! -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query |
Check the error log. Note that the log below needs a log_error_verbosity value of two or three.
|
1 2 |
2024-06-28T08:40:56.469085Z 12 [Warning] [MY-013130] [Server] Aborted connection 12 to db: 'unconnected' user: 'app1' host: '192.168.122.171' (init_connect command failed; diagnostics area: MY-001001 - User not in logins.allow_list CURRENT_USER[app1@%]) 2024-06-28T08:41:22.064094Z 13 [Warning] [MY-013130] [Server] Aborted connection 13 to db: 'unconnected' user: 'app1' host: 'c7-172.example.com' (init_connect command failed; diagnostics area: MY-001001 - User not in logins.allow_list CURRENT_USER[app1@%]) |
We see that processlist/connection id 12 and 13 got an aborted connection. It was caused by the init_connect parameter failure. It indicates from what host the failed connection happens and the custom error message through the SIGNAL command in the stored procedure.
Let us allow connection from those hosts: c7-171(via IP) and c7-172(via Hostname).
|
1 2 |
INSERT INTO allow_list(user, host) VALUES('app1', '192.168.122.171'); INSERT INTO allow_list(user, host) VALUES('app1', 'c7-172.example.com'); |
Now, the connection from c7-171 succeeds.
|
1 2 3 4 5 6 |
[root@c7-171 ~]# mysql -uapp1 -pSecret1! -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG' mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** USER(): app1@192.168.122.171 CURRENT_USER(): app1@% @@version: 8.0.37 |
Same success with c7-172.
|
1 2 3 4 5 6 |
[root@c7-172 ~]# mysql -uapp1 -pSecret1! -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG' mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** USER(): app1@c7-172.example.com CURRENT_USER(): app1@% @@version: 8.0.37 |
Test connection for second user ‘app2’@’192.%’.
From c7-171(192.168.122.171).
|
1 2 3 4 5 6 |
[root@c7-171 ~]# mysql -uapp2 -pSecret1! -h192.168.122.1 -P3306 -e'SELECT USER(), CURRENT_USER(), @@versionG' mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** USER(): app2@192.168.122.171 CURRENT_USER(): app2@192.% @@version: 8.0.37 |
Authenticated user ‘app2’@’192.%’ did not fail even if ‘app2’@’192.168.122.171’ does not exist in the allow_list table because its authenticated host(’192.%’ ) is not equal to ‘%.’
Considerations
- Init_connect content is not executed for users admin users.
- It will not work as expected if the application connects to a connection pooler like ProxySQL, where the database(backend) connections are reused/shared by the other ProxySQL frontend connections.
- If skip_name_resolve is enabled, be sure to use IP and not hostname in the allow_list table.
- Please consider using ROLE if the init_connect approach is not applicable to your requirements. This will simplify the granting of privileges to a single role and then grant the role to multiple user accounts with a specific host.
- A write lock to the allow_list table (LOCK TABLES allow_list WRITE) will cause the initial login to hang. Please avoid it or do not use allow_list table but rather do some logic in the procedure to use a list of user and host hardcoded in the procedure.
Conclusion
We can create a single database account (@’%’) to help simplify user management, and with the additional help from the init_connect parameter, we can restrict it to specific hosts. The above example might not fit your requirements, so you will have to adjust accordingly and conduct a rigorous test.
MySQL Performance Tuning is an essential eBook covering the critical aspects of MySQL performance optimization.
Download and unlock the full potential of your MySQL database today!
About the Author
TotelSupports MySQL with previous experience in Oracle and PostgreSQL databases. Likes Bash and SQL scripting. Uses Ubuntu for workstation and RHEL derivatives for VM/Container. Hobbies are gardening and pets(dogs, chickens and ducks).
