One of the most interesting features introduced in MySQL 8.0 is roles or the ability of defining a set of privileges as a named role and then granting that set to one or more users. The main benefits are more clarity of privileges and ease of administration. Using roles we can assign the same set of privileges to several users, and eventually modify or revoke all privileges at once.

Roles in a nutshell

Looking at the manual, we see that using roles is a matter of several steps.

(1) Create a role. The statement is similar to CREATE USER though the effects are slightly different (we will see it in more detail later on.)

mysql …

In the last post I shared the simple set of steps to configure a Group Replication setup using SQL commands, and a few in the configuration file too. Indeed, it can be simple.  But then there are times where there are more requirements and configurations need more attention. Maybe the OS environment we use for MySQL setups has never impacted us… Read More »

MySQL 3.20 to 4.0 In the good old days, when 32MB of RAM justified the name my-huge.cnf, when nobody knew Google and Facebook didn’t even exist, security was… how do I put it… kind of cute. Computer viruses didn’t steal millions and didn’t disrupt elections — they played Yankee Doodle or told you not to […]

The post Who are you? The history of MySQL and MariaDB authentication protocols from 1997 to 2017 appeared first on MariaDB.org.

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 …

One of the services our database engineers provide is adding users to MySQL. We have some nice Chef recipes, so all I have to do is update a few files, including adding in the MySQL password hash.

Now, when I added myself, I just logged into MySQL and generated a password hash. But when my SRE (systems reliability engineer) colleague needed to generate a password, he did not have a MySQL system he could login to.

The good news is it’s easy to generate a MySQL password hash. The MySQL password hash is simply a SHA1 hash of a SHA1 hash, with * at the beginning. Which means you do not need a MySQL database to create a MySQL password hash – all you need is a programming language that has a SHA1 function (well, and a concatenate function).

And I found it, of course, on this post at StackExchange (http://unix.stackexchange.com/a/234592/217471). So you don’t have to click through, here is what it says – and I have …

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 …

In this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Access Privileges Issues webinar.

First, I want to thank everybody for attending the February 23 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: Should the root@localhost user be given ALL privileges or Super privileges? Does All include Super privileges also?

A: Yes, you should have a user with all privileges. Better if this …

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 …

In this blog post, we’ll look at how “operator error” can cause serious problems (like the one we saw last week with AWS), and how to avoid them in MySQL using

--i-am-a-dummy

.

Recently, AWS had some serious downtime in their East region, which they explained as the consequence of a bad deployment. It seems like most of the Internet was affected in one way or another. Some on Twitter dubbed it “S3 Dependency Awareness Day.”

Since the outage, many companies (especially Amazon!) are reviewing their production access and deployment procedures. It would be a lie if I claimed I’ve never made a mistake in production. In fact, I would be afraid of working with someone who claims to have never made a mistake in a production environment.

Making a mistake or two is how you learn to have a full sense …

We’ll show you, how to secure LEMP stack. LEMP, it stands for Linux, (EngineX) NGINX, MariaDB (or MySQL) and PHP. Due to its flexibility and simplicity, NGINX slowly takes over the Internet. In this tutorial, we will attempt, through examples of bad and good practices, to go through the steps of properly securing your Linux web server. […]