MySQL Security

June 18, 2012

A deep dive into the code

MySQL security is like an onion, it has many layers. MySQL check the user source location (IP) and password. MySQL can also check a user’s SSL Certificate credentials. MySQL may or may not give a user access to a set of databases, tables, functions or views based on the user’s Access Control List.

I’ve been reviewing ScaleArc’s iDB for some time.  The use of a proxy complicates the authentication process even more.  This has led to me reviewing all of MySQL Security.  I’ll blog about what I’ve found as I dive into the code.

The Login Handshake

In version 4.0 and before:

  • The server sends a random string to the client, in scramble_buff.
  • The client encrypts the scramble_buff value using the hash of a password that the user has entered. This happens in sql/password.c:scramble() function.
  • The client sends the encrypted scramble_buff value to the server.
  • The server encrypts the original random string using a value in the mysql database, mysql.user.Password.
  • The server compares its encrypted random string to what the client sent in scramble_buff.
  • If they are the same, the password is okay.

In this protocol, snooping on the wire doesn’t reveal the password. But note the problem – if the client doesn’t know the password, but knows a hash of it (as stored in mysql.user.Password) it can connect to the server. In other words, the hash of a password is the real password; if one can get the value of mysql.user.Password – he can connect to the server.

In version 4.1 and Later

Passwords are stored in the mysql.user table as double sha1 strings. sha1(sha1(‘P@ssw0rd’).  The MySQL sha1 function outputs a hex value.  In the server’s mysql.user table the password is stored with a ‘*’ pre-pended to the string to indicate the password is post version 4.1. In SQL this is:

SELECT CONCAT( ‘*’,sha1(unhex(sha1(“P@ssw0rd”))));

The MySQL server keeps this hash in the mysql.user table.  I’ll use the password ‘P@ssw0rd’ in this example.

Server_Password = SHA1(SHA1('P@ssw0rd')) = gjKhKYpJ9xDb7gszDELuyCXUGQo=

To start the process the user types their password into the MySQL client and connects to the server.

Password = P@ssw0rd
The server check the user IP and makes up a random code (scramble) to send it to the client. (Handshake Initialisation Packet)  The scramble length is 8 bytes in versions before 3.23 and 20 thereafter.

scramble = 1a2b3c4d

When the client receive the handshake initialization packet, it hashes (sha1)the plain text password (stage1).

stage1_hash = SHA1(PW) = Ib0S3Bg/dA7nbye3jrOcitlyp1c=

Client create a token with the scramble and the stage1_hash.

token = SHA1(scramble + SHA1(stage1_hash) xor stage1_hash = 2I4FYp26k+PT6ivw5fCCl82LqSQ=

Client sends the user ID and token to server.  (Client Authentication Packet)

The server reverses the token. With the scramble it sent and its password hash from the mysql.user table, the server recovers the hash of the user’s password.

stage1 Prime = token xor SHA1(scramble + Server_Password) = Ib0S3Bg/dA7nbye3jrOcitlyp1c=

The server re-hashes the recovered stage1_hash just recovered so it can be compared it with the double hash in the mysql.user table.

code = SHA1(stage2_prime) = gjKhKYpJ9xDb7gszDELuyCXUGQo=

and compares it to the server’s stored password.

Does the code            gjKhKYpJ9xDb7gszDELuyCXUGQo=
equal the server_pw   gjKhKYpJ9xDb7gszDELuyCXUGQo=

NOTE:  It is this memory memcmp() in the check_scramble function, that is at the heart of the authentication bypass found by Sergei golubchik and released just days after I discovered the same issue. I believe TCPCOPY may suffer from the same issue.

Can the password be Brute Forced?

As a bird on the wire, can you recover the user’s password? Do you need to?

If you have every possible scrambles and the sha1 of every possible password, you could calculate and create a table of the results.

sha1(scramble(n) + sha1(n)) xor sha1(n)

You could then compare the transmitted token with table to produce the password.

The scramble is eight (8) bytes of “printable” characters (33).  33^8 = 1406408618241 = ≈ 1.4T  Or its twenty (20) bytes of “printable” characters. 33^20 = 2345734188103679287078463273601

The password keyspace for only seven (7) characters is (mixalpha-numeric-symbol32-space#1-7) 70576641626495 ≈ 2 ^ 46.0043  So, given this rainbow table of sha1 hashes you must multiply the combinations.

scramble * sha1 = 1977985201462558877934081  for an 8 byte scramble and scramble * sha1 = 1.6555404114481058399928719625084e+44  for 20.

Using hardware to calculate 400,000,000 sha1 keys per second, it will take 15,680,375 years to create such a table for an eight byte scramble and  13,124,210,516,933,867,960,369,672,458 years for a twenty byte scramble.

But wait! We know the scramble. The server sent it to the client so we only need to calculate, so we only need to calculate:

sha1(scramble + sha1(n)) xor sha1(n)

With this reduced number, n to 70,576,641,626,495.  Again at the rate of 400,000,000 per second,  the eight byte scramble is reduced to 2 days.  The size of the scramble doesn’t matter.

As a bird on the wire, if you get lucky, you might observe the administrator issuing the command “SELECT * from mysql.user;” If this happens you have everything you need to calculate the stage1_hash password and connect without the plain text version.

Size Matters

Passwords work because their entropy (randomness ) and length together create search space.

To brute force every possible eight character password, like “P@ssw0rd”, would take about two (2) years at 400,000,000 per second.  The password “P.@.s.s.w.0.r.d.” would take 3,535,000 years.  Both have the about same entropy but the second creates more space.

Here is a good calculator to demonstrate.

The password still has to be process with sha1.  You could just skip the password all together and just calculate all the possible values for a sha1. (8.02 thousand trillion trillion centuries @ 1Bcps)

It is believed the NSA has a database “Rainbow Table” of ever upper/lower-number-symbol two twelve (12) characters.  With such a table any twelve character or less password (in ASCII) would only be a 8ms lookup.

Man in the Middle

All of the above is based on being a bird on the wire. However, once the authentication is complete, the only thing managing access  is the TCP session.  It is possible a third party could inject commands into this session and issue commands.

A true Man In the Middle (MITM) or proxy, could simply pass the authentication through and then change or modify the message between the client and the server and issue commands of it’s own without the clients or servers knowledge. There is no way for the client to know he has connected to the proxy and not the server.  The server would believe all commands where issued by the client.

What could a MITM do? If the authenticated user has access to the mysql.user table it’s game over.  The MITM only needs see one attempt of a user to access the database to calculate that user’s stage1_hash.  If the client is a replication user a third party could connect and take a bin-log feed and watch all your updates.

But wait there’s more

For my next post I’ll dive into the host authentication (Firewall like function) of MySQL security.  Could there be a compare like authentication like bypass problem there?






posted in Commentary by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

7 Comments to "MySQL Security"

  1. Accutech wrote:

    Great information thanks for the post. Great detail I look forward to the next post

  2. Chip Barrere wrote:

    Great post, Mark! It forced me to rethink my own security practices.

  3. Manuel wrote:

    Hi Mark,
    what do you mean with:

    SHA1(scramble + SHA1(stage1_hash)

    scramble must be added to sha1-function’s return? How?
    Thanks in advance,

  4. admin wrote:

    The scramble (a random code made by the MySQL server) is add (pre-pended) to the SHA1 of the stage1_hash.

    In my example that would be 1a2b3c4d + Ib0S3Bg/dA7nbye3jrOcitlyp1c= or “1a2b3c4dIb0S3Bg/dA7nbye3jrOcitlyp1c=”

  5. Manuel wrote:

    Other question: when i use an online SHA1 Generator always become a 40 size char like dc76e9f0c0006e8f919e0c515c66dbba3982f785. But your examples show chars like
    Are there diferents types of SHA1?

    Thank you.

  6. Manuel wrote:

    Hi again,
    can you say me what is wrong in this algorythm:

    select cast(sha1(
    as char) c;
    assuming 2e59557d363440263c3d5b332e2a2623795b3b56 is the hex representation of then scramble.

    Thank you.

  7. wrote:

    MySQL Security | MySQL Fanboy

Leave Your Comment

You must be logged in to post a comment.


Powered by Wordpress and MySQL. Theme by Shlomi Noach,
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.