MySQL Trigger for specific mysql user only

Question

I am trying to find out if any MySQL user is used on our system (and what queries it performs).

So, I thought about writing a trigger that will be triggered at any time when user X executes the request, and he will log the request in the log table.

How can i do this? I know how to write a query for a specific table, but not for a specific user (any table).

thanks

Answer 1

You can split your trigger function into USER () .

Answer 2

The easiest way would always be to trigger the trigger, but only logs if user X.

Answer 3

There are other ways to solve this problem, for example, using MySQL proxy

In the proxy server you can do interesting things - from registration to query conversion, pattern matching (also check this link to find out how to test / develop scripts)

-- set the username local log_user = 'username' function read_query( packet ) if proxy.connection.client.username == log_user and string.byte(packet) == proxy.COM_QUERY then local log_file = '/var/log/mysql-proxy/mysql-' .. log_user .. '.log' local fh = io.open(log_file, "a+") local query = string.sub(packet, 2) fh:write( string.format("%s %6d -- %s \n", os.date('%Y-%m-%d %H:%M:%S'), proxy.connection.server["thread_id"], query)) fh:flush() end end 

The above has been tested and it does what it should (although this is a simple option, it does not make the logging proxy.COM_QUERY logs successful or unsuccessful, see the list of all constants to see what is missing and set up for your needs)

Answer 4

I would look at these options:

A) Write an audit plugin that filters events based on username.

For simplicity, the username can be hardcoded in the plugin itself, or for elegance, it can be configured using the plugin variable if this problem persists.

See http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html

B) Examine the -init-connect server option.

For example, call the stored procedure, check the value of user () / current_user (), and write the trace to the log (insert into the table) if a connection with the user is detected.

See http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect

This is probably the closest to the connection trigger.

C) Use the performance schematic toolkit.

This assumes 5.6.

Use the performance_schema.setup_instrument table to enable hardware only. Use the performance_schema.setup_actors table only for tool sessions for this user.

Then, after the system has been running for a while, look at the activity for this user in the following tables:

• the performance_schema.users table will tell you if there was any activity at all
• table performance_schema.events_statements_history_long will show the last completed queries
• the performance_schema.events_statements_summary_by_user table will show aggregated statistics about each type of statement (SELECT, INSERT, ...) performed by this user.

Assuming you have a user defined as "old_app" @ '%', a likely subsequent question would be to find out where (which host (s)) this old application is still connecting to.

performance_schema.accounts will simply show that: if traffic for this user is visible, it will show each user @hostname the source of the traffic. Statistics are also aggregated by account, look for tables% _by_account%.

See http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

Answer 5

Yes, get out, but use any system that you need to see which user it is (cookie, session) to register only if a specific user (userID, class) matches your credentials.