0

We are using Maria DB (v5.3 I believe) in our production environment.

One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications that connect to the db. We've started making application specific users and replacing those with the dev user.

Once we replace the dev user, the plan is to limit the rights/privileges on the (dev) user to only read only.

I believe we've now replace the majority of the application users from dev to their app specific user. However, I'm not 100% certain that no app is still using the dev user.

My question is, is there sql, or logs, that tell me the users who connected to to the db's w/in the past day/week/month, etc? How can i tell who connected to the db from a historical point of view?

2 Answers 2

1

You can record every connection using the general log.

In fact, you can record that information into a table.

In the mysql schema, there is a table called general_log. Here it is:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.01 sec)

mysql>

Of course, the table uses the CSV storage engine. You can change that to MyISAM and index it. Make a copy of the table beforehand:

use mysql
CREATE TABLE general_log_copy LIKE general_log;
ALTER TABLE general_log ENGINE=MyISAM;
ALTER TABLE general_log ADD INDEX (event_time);

To activate it, do this

1) Configure the general log to record to a table by adding these lines to my.cnf

[mysqld] 
general-log
log-output=TABLE 

2) Restart mysql

3) start mysql and user it for a day or two

4) Look for the dev user by running this query

SELECT * FROM mysql.general_log WHERE user_host LIKE 'dev %';

This will tell you what time and what IP dev is coming in

5) Hunt down the dev user in all your code

6) Truncate the general log

SET @old_log_state = @@global.general_log; 
SET GLOBAL general_log = 'OFF'; 
TRUNCATE TABLE mysql.general_log; 
SET GLOBAL general_log = @old_log_state; 

7) If you did find 'dev' users sneaking in, wait 24 hours and go back to step 4. Otherwise, you're done

CAVEAT

Please disable the general log when you are done by removing the lines in step 1. After that, just run

SET GLOBAL general_log = 'OFF'; 

That's all !!!

Please see my posts about the table version of the general log:

I also did this using the slow log instead : How to do MySQL User Accounting

Give it a Try !!!

0

Alternatively you can install and deploy the Maria DB audit log to enable a trace on the objects and access you require.

http://www.skysql.com/downloads/mariadb-audit-plugin

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.