Access controls protect MySQL databases so only clients with valid access credentials can access them. It’s important to set them up properly to reduce the attack surface and avoid unintended information disclosures.
With MySQL, it’s possible to limit database and table access to specific privileges (like SELECT to read data or INSERT to add new data) and allow only specific users and hosts to connect to a database or tables.
The first line of defense – physical connectivity
When a client connects to MySQL, it does it through a TCP connection to predefined ports. MySQL has port number 3306 assigned to them, and the newer MySQLX protocol uses port 33060 (MySQL 8.0 and newer). Protecting these ports from physical access is important to reduce the attack surface of MySQL and in general, it’s a good practice to only open ports where needed.
In the previous article about OpenVPN we’ve discussed how to protect MySQL from public access, you can find it here: OpenVPN advanced examples
By default, MySQL only listens on the local IP address (127.0.0.1) so it’s reasonably secure from remote access. This behavior can be controlled by the “bind-address” and “mysqlx_bind-address” (8.0+) configuration options.
You can verify this behavior by running “netstat -nl” to list all the listening connections. For example, having these two options in the MySQL config:
bind-address = 127.0.0.1,192.168.1.1
mysqlx_bind-address = 127.0.0.1opens port 3306 on localhost + 192.168.1.1 and port 33060 on localhost only:
# netstat -ln | grep 3306
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN
tcp 0 0 192.168.1.1:3306 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
In addition to this, it’s a good idea to block all ports on a public interface by default using iptables or specifically block 3306 and 33060 (see that OpenVPN article above). Always verify these findings by trying to connect from other public IP addresses to be sure, you don’t want to end up accidentally opening MySQL to the world.
Securing connections
MySQL is capable of connecting using SSL that protects the privacy of database connectivity. Without this, it’s possible to listen to the connection and steal your access credentials and the results of your DB queries. To secure this, you’ll need SSL certificates by either self-signing them or using https://letsencrypt.org to get proper ones.
Assuming you have your certificate in /etc/mysql and they are called server-cert.pem and server-key.pem, you need to add these two options to mysql.conf to enable SSL:
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pemYou can verify if this works by connecting using “mysql-ssl” and checking connection properties by entering “\s”:
# mysql --ssl
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
Some versions (notably MySQL 8.0) ship with clients where “-ssl” doesn’t work, they enable SSL by default unless the connection is through sockets. In that case, connecting to the host 127.0.0.1 will enable SSL and that can be verified similarly:
# mysql -h 127.0.0.1
...
Server version: 8.0.25 MySQL Community Server - GPL
mysql> \s
...
SSL: Cipher in use is TLS_AES_256_GCM_SHA384If you somehow see this, SSL is not properly configured:
SSL: Not in useYou can debug this by checking SSL options:
mysql> show variables like '%ssl%';To enforce SSL connections, you need to add “REQUIRE SSL” to GRANT statements or update mysql.user and set ssl_cipher to “ANY” for the required users and reloading privileges by doing “FLUSH PRIVILEGES”. More on that later.
Creating users
To set up MySQL access, first, you’ll need to create users. It doesn’t mean that they can access any database yet unless there are public databases, like the default “test”, but it will at least allow them to connect to MySQL.
Creating a user has three required options, the username, the hostname (can be a wildcard to allow all or localhost to allow local only), and the password. This command will create a basic user that connects from IP 1.2.3.4 – you’ll need to connect as root or an admin user to MySQL to do this:
CREATE USER 'techtipbits'@'192.168.1.2' IDENTIFIED BY 'password12';once this is done, connecting as user techtipbits from 1.2.3.4 becomes possible:
$ mysql -h 192.168.1.1 -u techtipbits -p
Enter password:
...
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)
Note: Watch out for MySQL client history (.mysql_history) because it may save the password in the command history and it’s a juicy target for hackers with read access to get access credentials to your databases. Some MySQL versions automatically strip passwords from these files but to be sure, check or delete the file afterward. You can also temporarily set the history file to /dev/null by prepending the “mysql” command with “MYSQL_HISTFILE=/dev/null”.
Assigning privileges to users
To give access to users to your databases a GRANT command will need to be run, specifying the database/tables, the user, and the type of access needed. Common access types are:
- ALL – give all access
- SELECT – read access
- INSERT – insert data
- UPDATE – update data
- DELETE – delete data
You can read more about the grant system here: https://dev.mysql.com/doc/refman/8.0/en/grant.html
To grant all access to our imaginary user above to DB techtipbits, we could use the following GRANT statement:
> grant all on techtipbits.* to 'techtipbits'@'192.168.1.2';It used to be possible to create users on the fly by simply appending the “identified by ‘password12′” statement to the end of grants but it’s now deprecated.
Checking and modifying privileges
To list privileges, you can use the show grant command. You can either append the user whose privileges you’re looking for (requires superuser) or run it without parameters to show your own:
> show grants;
+-----------------------------------------------------+
| Grants for techtipbits@192.168.1.2 |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `techtipbits`@`192.168.1.2` IDENTIFIED BY PASSWORD '*2F4817FE12986402A21D10FBB97BF8069F2AA3DA' |
| GRANT ALL PRIVILEGES ON `techtipbits`.* TO `techtipbits`@`192.168.1.2` |
+-----------------------------------------------------+The first privilege (USAGE) is an alias for no privileges. You can also see that passwords are stored by its hash so it’s at least somewhat difficult to decrypt them. The output of this command may differ from version to version, for example MySQL8 won’t show the password hash because it’s part of the user, not the grant.
To get information on the user (and the complete statement to re-create it), you can use “show create user” – this will work in MySQL 8.0, too:
> show create user techtipbits@192.168.1.2;
+--------------------------------------------+
| CREATE USER for techtipbits@192.168.1.2 |
+--------------------------------------------+
| CREATE USER `techtipbits`@`192.168.1.2` IDENTIFIED BY PASSWORD '*2F4817FE12986402A21D10FBB97BF8069F2AA3DA' |
+--------------------------------------------+
To revoke privileges, you can use the “REVOKE” command, it’s similar to the GRANT one, needs a user@host, db.table and the privilege to be revoked. It’ll need to be run as superuser (or someone with the GRANT privilege)
> revoke delete on techtipbits.* from techtipbits@192.168.1.2;
> show grants for techtipbits@192.168.1.2;
+-------------------------------------------------------------------+
| Grants for techtipbits@192.168.1.2 |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `techtipbits`@`192.168.1.2` IDENTIFIED BY PASSWORD '*2F4817FE12986402A21D10FBB97BF8069F2AA3DA' |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY ON `techtipbits`.* TO `techtipbits`@`192.168.1.2` |
+-------------------------------------------------------------------+We can see that the “ALL” in privileges now became everything but DELETE.
To delete a user, there is a “DELETE USER” command that’s similar to CREATE USER (without the password)
> drop user techtipbits@192.168.1.2;
> show grants for techtipbits@192.168.1.2;
ERROR 1141 (42000): There is no such grant defined for user 'techtipbits' on host '192.168.1.2'
Most of these commands can be emulated by selecting/inserting/deleting data in the “mysql” system database. MySQL saves the users in the mysql.user table and database access privileges in the “db” table. While it’s possible to modify these tables directly and run “FLUSH PRIVILEGES” to re-load them, the structure changes between different versions and you really need to know what you’re doing to adjust privileges this way.
