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(

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

SERVER:  scramble_buffer=create_random_string()

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


SERVER:  recv(scrambled_password)
         hash_stage1=xor(scrambled_password, sha1(scramble_buffer,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.hash(password.hash("me")) -- its valid password
            ) then

                            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


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@ [(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.


Enable javascript to load comments.