Amol Pandey: MySQL: User Activity Tracking (Bandwidth)

Recently I was presented a question whether it would be possible on MySQL to track the user activity in terms of queries and the amount of data downloaded by the individual user from the database.

Thus in the quest I came across a brilliant tool named “MySQL Proxy”. This tool provides a very strong functionality for MySQL database, which includes multiple MySQL server load balancing, query injection, and much more. This tool provided a very robust solution for the request with the usage of Lua scripting. Rather me extending more benefits about the tool in the post, I provide all the references below for the readers to explore more benefits of the tool themselves and here I would like to present my solution to record user activity in MySQL using MySQL Proxy with the following Lua script.

Note: This script also provides a section where user can be prohibited to download more data than the specified limit.

-- measures bandwidth by user

proxy.global.bandwidth = proxy.global.bandwidth or {}

local session_user 
local sqlQuery
local before_bytes_sent = 0
local after_bytes_sent  = 0
local before_bytes_recevied = 0
local after_bytes_recevied = 0

function read_auth()
    session_user = proxy.connection.client.username
    proxy.global.bandwidth[session_user] = 
        proxy.global.bandwidth[session_user] or 0
end

function read_query (packet )
    -- just to show how we can block a user query
    -- when the quota has been exceeded
    if proxy.global.bandwidth[session_user] > 10000 
       and session_user ~= 'root'
    then
        return error_result('you have exceeded your query quota')  
    end
    sqlQuery = string.sub(packet, 2)
    proxy.global.bandwidth[session_user ] = 
        proxy.global.bandwidth[session_user] + packet:len()

    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )
    proxy.queries:append(2, packet,{resultset_is_needed = true})
    proxy.queries:append(3, string.char(proxy.COM_QUERY) .. "SHOW SESSION STATUS LIKE '%Bytes%'", {resultset_is_needed = true} )

    return proxy.PROXY_SEND_QUERY
end

function read_query_result(inj)
 
 if (inj.id == 4) then
     return proxy.PROXY_IGNORE_RESULT
 end
        
        if (inj.id == 1) or (inj.id == 3) then
                for row in inj.resultset.rows do
                        if (row[1] == "Bytes_sent") and (inj.id == 1) then
        before_bytes_sent = row[2]
   end 
   if (row[1] == "Bytes_sent") and (inj.id == 3) then
        after_bytes_sent = row[2]
   end
   if (row[1] == "Bytes_received") and (inj.id == 1) then
        before_bytes_recevied = row[2]
   end 
   if (row[1] == "Bytes_received") and (inj.id == 3) then
        after_bytes_recevied = row[2]
   end   
                end
                
             if (inj.id == 3) then
     print("Bytes sent before: " .. before_bytes_sent)
     print("Bytes sent after: " .. after_bytes_sent)
     print("Bytes received before: " .. before_bytes_recevied)
     print("Bytes received after: " .. after_bytes_recevied)
     print("Net Bytes sent: " .. (after_bytes_sent - before_bytes_sent))
     print("Net Bytes received: " .. (after_bytes_recevied - before_bytes_recevied))
     print("Username: " .. session_user)
     print("Query: " .. sqlQuery) 
     print("DateTime: " .. os.date("%Y-%m-%d %H:%M:%S"))
     insert_log_query(session_user, os.date("%Y-%m-%d %H:%M:%S"), sqlQuery, (after_bytes_sent - before_bytes_sent), (after_bytes_recevied - before_bytes_recevied))
          end
          
                return proxy.PROXY_IGNORE_RESULT
        end
end

function insert_log_query(username, date_time, query , net_bytes_sent, net_bytes_recived)
      print(username, date_time, query , net_bytes_sent, net_bytes_recived)
      proxy.queries:append(4, string.char(proxy.COM_QUERY) .. "INSERT INTO `employees`.`user_log` (`username`, `date_time`, `query`, `bytes_sent`, `bytes_recived`) VALUES ('" ..
      username .. "','" .. date_time .. "',\"" .. query .. "\"," ..  net_bytes_sent .. "," .. net_bytes_recived .. ");", {resultset_is_needed = true})
      return proxy.PROXY_SEND_QUERY 
end

This script uses the "SHOW SESSION STATUS" results for the recording of bytes sent and recevied. This also illustrates the power of MySQL Proxy which enables use to inject addtional queries to the database and process their results to our needs without any affects visible to the end user.

Download: