Shulou(Shulou.com)05/31 Report--

How to achieve database authentication, authorization and auditing through MySQL-Proxy, for this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

0x00 Preface

The number of accounts in the database would increase, and account authorization was also a painful job. In particular, there will be the problem of "mysql from deleting the database to running," and it will be very troublesome for employees to delete accounts after leaving their jobs. For wealthy chaebol, this problem is easy to solve. It is good to buy a set of equipment. However, for start-up companies, they should strive to achieve "zero yuan party", otherwise they cannot fully reflect their own value.

0x01 Research

The initial idea was to redevelop the proxy tool for open source mysql, so I started collecting information about similar tools. I accidentally found that mysql-proxy actually reserved 6 hooks to allow users to call them through Lua scripts, which means that we can write Lua scripts ourselves to control the "fate of users."

connect_server() This function is called when the proxy server receives a client connection request.

read_handshake() is called when the mysql server returns a handshake response

read_auth() is called when the client sends authentication information

read_auth_result(aut) Called when mysql returns authentication results

read_query(packet) is called when a client submits a sql statement

read_query_result(inj) is called when mysql returns the query result

Obviously, with the read_auth and read_query hooks mentioned above, we can achieve authentication, authorization and auditing of mysql databases.

0x02 Design

Our goal is authentication, authorization, and auditing, so read_auth implements authentication and authorization, and read_query implements auditing. read_query is relatively easy to implement, just need to get to the sql statement sent by the user to write to the message queue, I will simply write it to the redis list here.

The read_auth function is relatively complex. It not only needs to verify the one-time password submitted by the user, but also needs to read the authorization information, so that when the user logs in to mysql, it will become the identity specified by us.

1. When a user accesses Openrest, the lua script on the backend calls the messaging interface of the im software used internally by the company to send the generated one-time password to the user. At the same time, the password is written to redis.

2. The user uses the mysql client to connect to the specified mysql-proxy. At this time, enter the read_auth hook function to confirm the password submitted by the user first. Then I will go to redis to request the authorization list of the three roles corresponding to developer, master and owner in the current database, check whether the three lists contain the current user, and if so, jump the user to the database with his corresponding role.

3. After the authentication authorization is successfully completed, the user accesses the backend mysql through the role authorized in the previous step, and all sql statements executed will enter the read_query hook function and be recorded in the redis queue.

0x03 CODE local password =assert(require("mysql.password"))

local proto =assert(require("mysql.proto"))

assert(require("redis"))

--String cutting

function string:split(sep)

local sep, fields = sep or ":", {}

local pattern = string.format("([^%s]+)", sep)

self:gsub(pattern, function (c) fields[#fields + 1] = c end)

return fields

end

function read_query( packet )

if packet:byte() == proxy.COM_QUERY then

local con = proxy.connection

local redis = Redis.connect('your_redis_ip',6379)

--Get IP domain name

redis:select('3')

local domain = redis:get(con.server.dst.name:split(':')[1])

--Put executed sql statements in redis queue

redis:select('2')

redis:lpush('mysql_command_queue',os.date("%Y-%m-%d%H:%M:%S",os.time())

.. " " .. con.client.src.address .. "" .. con.client.username .. " " ..

domain .. " [" .. packet:sub(2) .. "]")

if packet:sub(2) == "SELECT 1" then

proxy.queries:append(1, packet)

end

end

end

function read_auth()

local names = {}

--developer, master, owner three roles permissions increased step by step

local roles = {[1] = 'developer',[2] = 'master',[3] = 'owner'}

local con = proxy.connection

local s = con.server

local role = ''

--Certification

local redis = Redis.connect('your_redis_ip', 6379)

local pass = redis:get(con.client.username)

ifpassword.scramble(s.scramble_buffer, password.hash(pass)) ~=con.client.scrambled_password then

--authentication failure returns error message

proxy.response.type = proxy.MYSQLD_PACKET_ERR

proxy.response.errmsg ="Password error! "

return proxy.PROXY_SEND_RESULT

end

redis:del(con.client.username)

--Get IP domain name

redis:select('3')

local domain = redis:get(con.server.dst.name:split(':')[1])

redis:select('1')

--Get the user's role for the current database

for i,v inipairs(roles) do

--Query "domain:role" to return the corresponding list and cut the list into tables

names = redis:get(domain .. ":" .. v):split(',')

for k,name in ipairs(names) do

if name == con.client.username then

role = v

break

end

end

end

--No authorization information returned error message

if role == '' then

proxy.response.type = proxy.MYSQLD_PACKET_ERR

proxy.response.errmsg = "Unauthorized access! "

return proxy.PROXY_SEND_RESULT

end

--New properties added to mysql-proxy

local protocol_41_default_capabilities = 8 + 512 + 32768

proxy.queries:append(1,

proto.to_response_packet({

username = role,

response = password.scramble(s.scramble_buffer, password.hash("your_role_password")),

--New properties added to mysql-proxy

server_capabilities=protocol_41_default_capabilities

})

)

return proxy.PROXY_SEND_QUERY

end0x04 EFFECT [root@ip-172-31-24-123 ~]# mysql -u test -h your_mysql-proxy_ip -P your_mysql-proxy_port-pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 30341Server version: 5.7.12 MySQL CommunityServer (GPL)Copyright (c) 2000, 2018, Oracle and/or itsaffiliates. All rights reserved.Oracle is a registered trademark of OracleCorporation and/or itsaffiliates. Other names may be trademarksof their respectiveowners.Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.mysql> select user();+-------------------------+| user() |+-------------------------+| developer@your_ip|+-------------------------+1 row in set (0.01 sec)

Obviously, logging in to mysql-proxy with the username test, and eventually jumping to mysql, the user has changed to developer.

0x05 Summary

Used for connecting databases in non-business scenarios. For example, developers and OPS personnel connect databases in the company.

The admin script needs to monitor the status of each mysql-proxy process, responsible for starting and stopping them, and storing their domain names as ip in redis.

The authorization script reads a yaml file and synchronizes the authorization rules in the file to redis.

In each database, only three new accounts, developer, master and owner, need to be created. The content in the yaml configuration file determines which role the user uses to log in to mysql.

mysql-proxy needs to be compiled and installed using source code.

The command to start mysql-proxy is:

mysql-proxy/bin/mysql-proxy--proxy-address=mysql-proxy_ip:mysql-proxy_port --proxy-backend-addresses=mysql_ip:mysql_port--max-open-files=1024 --user=root --log-file=/var/log/mysql-proxy--log-level=debug --proxy-lua-script=your_lua_file & About how to achieve database authentication, authorization and auditing through MySQL-Proxy The answer is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts, you can pay attention to the industry information channel for more relevant knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.