On the MySQL Proxy channel we get questions from time to time if the authentication can be intercepted and replaced data from a external source.

From now on, you can. For example if you want to get data from a external source (like LDAP) or want to implement roles.

Mapping Accounts to “Roles”

There isn’t much needed to implement Roles for MySQL with the help of the MySQL Proxy.

• mysql.user doesn’t contain users, but roles instead
• the proxy maps user-accounts to role-accounts with a script like above

It works like this:

1. login to the proxy

        $ mysql --user=jan --password=secret --port=4040

2. proxy looks up username password, finds a role for him

3. proxy replaces credentials ad hoc

4. mysql-server sees the role-name and role-password and lets the user in

This works nicely as long as you only have 1:n mapping between roles and users. One user has one role, but one role has many users.

All you need is a storage that has user’s password in one of three forms:

• cleartext as in the example below
• SHA1(cleartext) as you may have in LDAP
• SHA1(SHA1(cleartext)) as in mysql.user: SELECT CONCAT("*", SHA1(UNHEX(SHA1("pass")))), PASSWORD("pass");

How the Auth works

The MySQL Proxy has access to all protocol data and exposes it as part of different stages of the authentication to the scripting layer:

---
-- show how to use the mysql.password functions 
function read_auth()
    local c = proxy.connection.client
    local s = proxy.connection.server
    print(("for challenge %q the client sent %q"):format(
            s.scramble_buffer,
            c.scrambled_password
    ))
end

The challenge and response secures the password and is works like this:

SERVER:  scramble_buffer=create_random_string()
         send(scramble_buffer)

CLIENT:  recv(scramble_buffer)
         hash_stage1=sha1("password")
         hash_stage2=sha1(hash_stage1)
         scrambled_password=xor(hash_stage1, sha1(scramble_buffer, hash_stage2)

         send(scrambled_password)
   
SERVER:  recv(scrambled_password)
         hash_stage1=xor(scrambled_password, sha1(scramble_buffer,hash_stage2))
         candidate_hash2=sha1(hash_stage1)
         check(candidate_hash2==hash_stage2)

That’s taken from sql/password.c of the MySQL Server Source. The hash_stage2 is the same as result of the PASSWORD() function without the * and the HEX() and is stored in mysql.user.

Replacing the auth response

Based on the read_auth() from above you can also replace the credentials on the fly:

    ---
    -- map replace:me with root:secret
    --
    if c.username == "replace" and -- the username we want to map
            password.check(
                    s.scramble_buffer,
                    c.scrambled_password,
                    password.hash(password.hash("me")) -- its valid password
            ) then 

            proxy.queries:append(1,
                    proto.to_response_packet({
                            username = "root", 
                            response = password.scramble(s.scramble_buffer, password.hash("secret")),
                            charset  = 8, -- default charset
                            database = c.default_db,
                            max_packet_size = 1 * 1024 * 1024
                    })
            )

            return proxy.PROXY_SEND_QUERY  -- works if you use lp:mysql-proxy r694 or later
    end

At

http://bazaar.launchpad.net/~mysql-proxy-developers/mysql-proxy/trunk/annotate/head%3A/examples/tutorial-scramble.lua

you can see the full version of it.

Let’s see if it works:

$ mysql-proxy --proxy-lua-script=tutorial-scramble.lua
$ mysql --port=4040 --user=replace --password=me
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2702
Server version: 5.1.34-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@127.0.0.1 [(none)]> 

I logged in as the root user.

Fixing the hostcheck

This can also be used to rewrite the username to something that includes his original ip. Like

...,
username = c.username .. "-" .c.src.address,
...

to make the server see something else than the proxy’s client-address if you use the host-field in the mysql.user tables.