The internet has changed to a place where most protocols like HTTP etc now use secure connections with TLS by default. While both HTTP and the MySQL Protocol use TLS for secure connections there are still many differences which make it difficult for MySQL to benefit from the same advancements as HTTP has seen in the last so many years. What is TLS? TLS stands for Transport Layer Security and is the successor of SSL (Socket Layer Security). SSL and TLS are often used interchangably, while this isn’t correct strictly speaking. What TLS provides is a standardized way to encrypt in transit traffic and authenticate the other end of the connection. TLS when used together with HTTP is known as HTTPS, for secure HTTP. How TLS works in HTTPS The client (webbrowser) connects to a server on port 443. Then negitiation is done to agree on what encryption method is to be used. The server presents the client with a certificate, which the client then verifies against the system trust store. For ...

Oracle recently announced a new authentication plugin: caching_sha2_password . This was added in 8.0.4 , the second release candidate for MySQL 8.0. The new plugin is also made the default (can be configured by changing default_authentication_plugin . Why? Phasing out SHA1 As Oracle said in the blog post to annouce this change they want to move to a more secure hashing algorithm ( SHA256 ). Which I think is a good reason to do this. Adding salt Adding a salt makes hashes for identical passwords, but different users different. Again a good reason to do this. Performance Their earlier attempt at this resulted in sha256_password . But this resulted in slower authentication. Without using persistent connections this is a serious limitation. So again a good reason. What's wrong? If you don't use SSL/TLS it gives your password away. To protect against sending the password in cleartext over an insecure connection it encrypts the password before sending it. It does this by ...

Backup traffic After securing application-to-database and replication traffic, you should also do the same for backup traffic. If you use Percona XtraBackup with streaming than you should use SSH to send your backup to a secure location. The same is true for MySQL Enterprise Backup. Also both have options to encrypt the backup itself. If you send your backup to a cloud service this is something you should really do, especially if it is not sent via SSH or HTTPS. And mysqldump and mysqlbinlog both support SSL. And you could use GnuPG, OpenSSL, WinZIP or any other tool to encrypt it. Sending credentials You could try to force the client to send credentials elsewhere. This can be done if you can control the parameters to the mysql client. It reads the config from /etc/my.cnf, ~/.my.cnf and ~/.mylogin.conf but if you for example specify a login-path and a hostname.. it connects to that host, but with the password and username from the loginpath from the encrypted ~/.mylogin.cnf file. ...

The mysql_sha256_password doesn't use the nonce system which is used for mysql_new_password , but instead forces the use of RSA or SSL. This is how that works: The client connects The server changes authentication to sha256 password (or default?) The server sends the RSA public key. The client encrypts the password with the RSA public key and sends it to the server. The server decrypts the password with the private key and validates it. The problem is that the client trusts public key of the server. It is possible to use --server-public-key-path=file_name . But then you need to take care of secure public key distribution yourself. So if we put a proxy between the client and the server and then have the proxy sent its own public key... then we can decrypt it and reencode it with the real public key and send it to the server. Also the decrypted password is the password, not a hash. So we then know the real password. And if SSL is used it doesn't do the RSA encryption....

In my previous blogs I told you to enable SSL/TLS and configure it to check the CA. So I followed my advice and did all that. Great! So the --ssl-mode setting was used a few times as a solution. And it has a setting we didn't use yet: VERIFY_IDENTITY . In older MySQL versions you can use --ssl-verify-server-cert . Both turn on hostname verification. The attack Get any certificate which is trusted by the configured CA, this can for example be a certificate from a development machine. And use that with a man-in-the-middle proxy. Then the client: Checks if SSL is uses ( --ssl-mode=REQUIRED ) Verify if the certificate is signed by a trusted CA ( --ssl-mode=VERIFY_CA ) Both checks succeed. But the certificate might be for testhost01.example.com and the database server might be prod-websitedb-123.example.com. Browsers by default verify hostnames, MySQL does not. Turning on hostname validation So use --ssl-mode=VERIFY_IDENTITY and everything should be fine? Well that might ...

In my previous blogs I told you to enable SSL/TLS and force the connection to be secured. So I followed my advice and did forced SSL. Great! So now everything is 100% secure isn't it? No it isn't and I would never claim anything to be 100% secure. There are important differences in the SSL/TLS implementations of browers and the implementation in MySQL. One of these differences is that your browser has a trust store with a large set of trusted certificate authorities. If the website you visit has SSL enabled then your browser will check if the certificate it presents is signed by a trusted CA. MySQL doesn't use a list of trusted CA's, and this makes sense for many setups. The key difference is that a website has clients (browsers) which are not managed by the same organization. And for MySQL connections the set of clients is often much smaller are more or less managed by one organization. Adding a CA for a set of MySQL connections if ok, adding a CA for groups of web...

Intro In my previous blog post I told you to use SSL/TLS to secure your MySQL network connections. So I followed my advice and did enable SSL. Great! So first let's quickly verify that everything is working. So you enabled SSL with mysql_ssl_rsa_setup , used a OpenSSL based build or put ssl-cert , ssl-key and ssl-ca in the mysqld section of your /etc/my.cnf and now show global variables like 'have_SSL'; returns 'YES'. And you have configured the client with --ssl-mode=PREFERRED . Now show global status like 'Ssl_cipher'; indicates the session is indeed secured. You could also dump traffic and it looks 'encrypted' (i.e. not readable)... With SSL enabled everything should be safe isn't it? The handshake which MySQL uses always starts unsecured and is upgraded to secured if both the client and server have the SSL flag set. This is very similar to STARTTLS as used in the SMTP protocol. To attach this we need an active attack; we need ...

Intro In a set of blog posts I will explain to you how different attacks on the network traffic of MySQL look like and what you can do to secure your systems againt these kinds of attacks. How to gain access To gain access to MySQL network traffic you can use tcpdump, dumpcap, snoop or whatever the tool to capture network packets on your OS is. This can be on any device which is part of the connnection: the server, the client, routers, switches, etc. Besides application-to-database traffic this attack can also be done on replication traffic. Results This allows you to extract queries and result sets. The default password hash type mysql_new_password uses a nonce to protect against password sniffing. But when you change a password this will be sent accross the wire by default. Note that MySQL 5.6 and newer has some protection which ensures passwords are not sent to the logfiles, but this feature won't secure your network traffic. In the replication stream however there are...

The issue On 12 September 2016 (three days ago) a MySQL security vulnerability was announced. The CVE id is CVE-2016-6662 . There are 3 claims: By setting malloc-lib in the configuration file access to an OS root shell can be gained. By using the general log a configuration file can be written in any place which is writable for the OS mysql user. By using SELECT...INTO DUMPFILE... it is possible to elevate privileges from a database user with the FILE privilege to any database account including root. How it is supposed to be used Find an SQL Injection in a website or otherwise gain access to a MySQL account. Now create a trigger file (requires FILE privilege) Now in the trigger or otherwise use SET GLOBAL general_log_file etc to create a my.cnf in the datadir with the correct privileges. Directly using SELECT...INTO DUMPFILE...won't work as that would result in the wrong permissions, which would cause mysqld/mysqld_safe to ignore that file. Now wait someone/somethi...

Today we released The CIS Oracle MySQL Community Server 5.6 Benchmark v1.0.0 This is a benchmark which can be used to secure your MySQL installation.

In this blog post I will describe different ways of using SSL with the MySQL database server. What does SSL give you? You might use MySQL replication over the internet or connect to MySQL over the internet. Another posibility is that you connect over an enterprise network to which just too many people have access. This is especially an issue if you use an BYOD network. SSL helps here by encrypting the network traffic to prevent against evesdropping. It also validates that you're talking to the correct server to prevent man-in-the-middle attacks. And you can also use SSL client certificates together with an password as two factor authentication. SSL is not the only option, you could use SSH and many MySQL GUI clients like MySQL Workbench support this. But you can't easily use SSH with a python script or mysqldump. Things that could go wrong Using SSL is almost always better than not using SSL at all. So there is not much you could do wrong. But there are a few things t...

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn't belong... and there is no undelete . First: Don't panic . Often a password is required besides a certificate to connect to the server. So someone with the certificate can't use it without the password. The certificate itself might be protected by a password, but that's really rare. Also access to MySQL and/or your account should be limited to certain IP's. The next step is to revoke the certificate. This is possible since MySQL 5.6.3 by using a Certificate Revocation List (CRL). A CRL is a list of the serials of the revoked certificates and signed by the CA. So this will only work if the certificates have unique serials. To get the serial of a certificate with OpenSSL: $ openssl x509 -in client-cert.pem -noout -text | grep 'Serial Number'  Serial Number: 3 (0x3) To get the serial of a certificate with GnuTLS: $ ce...

It is possible to disallow users from using old_passwords=1. This can be done by adding ' maximum-old_passwords=0 ' to your my.cnf This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier. Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes. mysql> select @@old_passwords; +-----------------+ | @@old_passwords | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> set old_passwords=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect old_passwords value: '1' | +---------+------+----------------------------------------------+ 1 r...

I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL. The benchmark document helps you to ensure you have a secure configuration for MySQL. Center for Internet Security (CIS) is a nonprofit organization. The Call For Participation is now open. If you are already a CIS member then you can download the document here . If you don't have an account you can register for free.

Oracle has released the Critical Patch Update (CPU) for July 2014. The Oracle MySQL Risk Matrix lists 10 security fixes. It took me some time to understand the subcomponent names. So here is the list with the full name of each subcomponent: Subcomponent Full name SRFTS Server: Full Text Search SRINFOSC Server: INFORMATION_SCHEMA SRCHAR Server: Character sets ENARC Engine: Archive SROPTZR Server: Optimizer SRREP Server: Replication SRSP Server: Stored Procecure ENFED Engine: Federated I don't think there is anything really important in the list, but it might be a good trigger to update to the latest release. Upgrading should be easy especially if you're using the APT or YUM repositories from Oracle. If you're upgrading to a new major release (e.g. from 5.5 to 5.6) then you should read the instructions in the reference manual.

Oracle has improved the AES encryption/decryption functions in MySQL 5.6.17. They improved it a lot and posted a blog which explains all the details. If you would like to know more about encryption there are two resources I would recommend: The Code Book by Simon Singh. This is about the history of cryptography, but it also includes a lot of information about crypto which is currently in use. This is also a very entertaining read. Crypto 101 , a free/opensource book which gives a intro to crypto. The webpage also has a video of the talk on which the book is based. And if you're going to use the AES encryption functions in MySQL there are some things you should consider: Connections with a UNIX socket (or shared memory on Windows) should be safe. Local TCP/IP connections are also safe in most circumstances. Remote connections which use the AES encryption should use a protected connection (SSL, VPN, etc). Otherwise someone might be able to sniff the network traffice which...

Oracle has released the Critical Patch Update (CPU) advisory for January 2014 . The affected MySQL products are: Oracle MySQL Enterprise Monitor, versions 2.3, 3.0  Oracle MySQL Server, versions 5.1, 5.5, 5.6 So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the regular download locations . I guess the official repositories are already updated. For MySQL 5.6 you should upgrade to 5.6.15 For MySQL 5.5 you should upgrade to 5.5.35 For MySQL 5.1 you should upgrade to 5.1.73 If you use the MySQL release from your distribution you should ask them to fix the CVE numbers fixed in these releases (if they didn't do that already). The highest CVSS score for MEM is 10 and for MySQL the highest score is 6.8. Update: for more information see this blog post by Dave Stokes.

As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory. For other setups directories like lost+found, .zfs, etc. gave similar issues. In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do this a ignore-db-dir option needs to be specified for each directory. On a running server the list of ignored database direc...

There are many thing changed in MySQL 5.6 which are related to passwords: There is a new password hash algorithm (SHA-256) There is obfuscation for passwords with the .mylogin.cnf file. The option to store slave passwords in a database table. It's now possible to supply a password to START SLAVE. But that's not what this blog post is about. This blog post is a great new feature: Hiding passwords from your log files, automatically. MySQL 5.6 will by default hide passwords from the general log. This is not just obfuscation as only the one-way hash will be put in the log files. By setting log-raw=OFF you can disable password hiding for the general log. The log-raw setting will only influence the general log, so the passwords in the slow query log and the binary logs will still be hidden. With MySQL 5.5 this could be done manually by first storing the hash in a variable, but like many other work-arounds this is not needed anymore. But it doesn't stop there. Accor...

What happens when you use the PASSWORD() function to insert a password hash into a table? The hash will be written to the table The password might be written in clear text to the binlog The password might be written in clear text to the general log The password might be written in clear text to the slow query log The query mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD(' secret_password ')); Query OK, 1 row affected (0.00 sec) The General log 130128 16:04:41 1 Query INSERT INTO testpwd(pwd) VALUES(PASSWORD(' secret_password ')) The Slow query log # Time: 130128 16:04:41 # User@Host: msandbox[msandbox] @ localhost [] # Query_time: 0.004887 Lock_time: 0.001043 Rows_sent: 0 Rows_examined: 0 SET timestamp=1359385481; INSERT INTO testpwd(pwd) VALUES(PASSWORD(' secret_password ')); The binlog: # at 219 #130128 16:04:41 server id 1 end_log_pos 287 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1...