Enable General Query Log per Connection in MariaDB

The General Query Log in MariaDB is a Log (file or table) that logs every statement sent from the Application to the MariaDB Database Server from the Connect Command to the Quit Command. A simple example you can find here:

bin/mysqld, Version: 10.4.6-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysql.sock
Time                Id Command  Argument
190709 21:27:30   3872 Connect  root@localhost as anonymous on test
                  3872 Query    INSERT INTO test VALUES (NULL, CONCAT('Test data insert from laptop5 on ', @@hostname), CURRENT_TIMESTAMP())
                  3872 Quit

The General Query Log can be enabled dynamically with the following command:

SQL> SET GLOBAL general_log = 1;

and is written either to a file (defined with general_log_file) or a table (mysql.general_log, defined with log_output=TABLE).

But unfortunately the General Query Log logs all Commands from all Connections. So it can grow quickly and you loose overview over what happens...

With the MariaDB Server System Variables sql_log_off you can configure to NOT log a Connection to the General Query Log. This is the opposite of what we want to have. But with a clever combination of these variables we can manage how to log only specific Connections to the General Query Log.

Preparation for Connection Logging to the General Query Log

First of all we have to enable the General Query Log but disable it for all Connections so nothing is written to the General Query Log:

SQL> SET GLOBAL sql_log_off = on;   -- 1 = no logging
SQL> SET SESSION sql_log_off = on;  -- also for my current session!
SQL> SET GLOBAL general_log = 1;    -- 1 = enable general query log file

Then we need a place where to specify and control which user from which host should be logged. So we can control General Query Log logging dynamically. For this purpose we create a table called general_log_settings in the sys Schema of the MariaDB sys Schema:

SQL> CREATE SCHEMA IF NOT EXISTS `sys`;
SQL> use `sys`;

SQL> DROP TABLE IF EXISTS `general_log_settings`;
SQL> CREATE TABLE `general_log_settings` (
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '%',
  `USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '%',
  `ENABLED` enum('YES','NO') NOT NULL DEFAULT 'YES'
) ENGINE=Aria DEFAULT CHARSET=utf8;

SQL> INSERT INTO `general_log_settings` (`HOST`, `USER`, `ENABLED`)
VALUES ('%', '%', 'NO');

Now we need a mechanism to enable General Query Log logging for every user. For this we use a Stored Procedure:

SQL> use `sys`;
SQL> DROP PROCEDURE IF EXISTS `enable_general_log`;

SQL> DELIMITER //

SQL> CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `enable_general_log`()
DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER
BEGIN
  DECLARE user VARCHAR(32);
  DECLARE host VARCHAR(60);
  DECLARE cnt SMALLINT;

  SELECT SUBSTRING_INDEX(SESSION_USER(), '@', 1) INTO user;
  SELECT SUBSTRING_INDEX(SESSION_USER(), '@', -1) INTO host;

  SELECT COUNT(*) INTO cnt
    FROM `general_log_settings`
   WHERE `ENABLED` = 'YES'
     AND (`HOST` = host OR `HOST` = '%')
     AND (`USER`= user OR `USER`= '%')
  ;

  IF cnt > 0 THEN
    SET SESSION sql_log_off = off;
    DO 'Connect';
  END IF;
END;
//

SQL> DELIMITER ;

Then we have to grant to ALL! users without the SUPER privilege the privilege to execute this procedure. These users can be found with the following query:

SQL> SELECT user, host, super_priv FROM mysql.user WHERE super_priv != 'Y';
+------+-----------+------------+
| User | Host      | Super_priv |
+------+-----------+------------+
| oli  | localhost | N          |
| app  | %         | N          |
+------+-----------+------------+

To grant the privilege to execute the procedure you must run the queries as follows:

SQL> GRANT EXECUTE ON PROCEDURE `sys`.`enable_general_log` TO 'oli'@'localhost';
SQL> GRANT EXECUTE ON PROCEDURE `sys`.`enable_general_log` TO 'app'@'%';

Caution: If you do NOT grant the execute privilege to all users their new connections will fail as soon as you have enabled the logon trigger!

Then you have to add the users you want to log to the General Query Log to the general_log_settings table:

SQL> INSERT INTO `sys`.`general_log_settings` VALUES ('%', 'app', 'YES');
SQL> SELECT * FROM `sys`.`general_log_settings`;
+------+------+---------+
| HOST | USER | ENABLED |
+------+------+---------+
| %    | %    | NO      |
| %    | app  | YES     |
+------+------+---------+

You can test the logging to the General Query Log in your Connection with the following command now:

SQL> CALL `sys`.`enable_general_log`();

If everything is done correctly you should see some entries in your General Query Log as follows:

190709 23:51:21   6695 Query    DO 'Connect'
                  6695 Query    select count(*) from test.test
                  6695 Quit

And you should notice that the Connect Command is missing (see above). This is because enabling the General Query Log happens after the Connect. To give an equivalent entry tag in the General Query Log we have added the DO 'Connect' tag in the Stored Procedure.

Up to this point here all actions should not have any impact (neigther operational nor performance wise) to your MariaDB database system yet.

Activating General Query Log per Connection

Now we want to activate General Query Log logging for every new Connection to your MariaDB database server. This step can have an impact on your MariaDB database system and you should know how an be prepared to revert this step:

SQL> SET GLOBAL init_connect = 'CALL `sys`.`enable_general_log`();';

The init_connect Server System Variable specifies a command or a list of commands that will be executed for each new client connection. We also call this a Logon Trigger. These statements are only executed for users without the SUPER privilege. So you cannot track admin accounts with this method.

Further readings

• Logging users to the MySQL error log
• Last login of MySQL database users
• MySQL logon and logoff trigger for auditing

Please let us know if you think we should add this tool to the MariaDB sys Schema or not.