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.userdoesn't contain users, but roles instead- the proxy maps user-accounts to role-accounts with a script like above
It works like this:
-
login to the proxy
$ mysql --user=jan --password=secret --port=4040
-
proxy looks up username password, finds a role for him
- proxy replaces credentials ad hoc
- 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
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.
Comments