{"id":3397,"date":"2022-12-08T14:44:26","date_gmt":"2022-12-08T13:44:26","guid":{"rendered":"https:\/\/kudzia.eu\/b\/?p=3397"},"modified":"2024-03-31T19:12:45","modified_gmt":"2024-03-31T18:12:45","slug":"mysql-mariadb-login-audit","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2022\/12\/mysql-mariadb-login-audit\/","title":{"rendered":"MySQL \/ MariaDB login audit"},"content":{"rendered":"\n<p>once in a while i need to review which database accounts are used, from what IPs connections arrive. MySQL \/ MariaDB does not have built-in mechanism [ unless you want to allow full query log ], but there&#8217;s quite easy way to get the data.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><\/p>\n\n\n\n<p>it&#8217;s done by:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>creating a stored procedure writing basic info [ which user, from which IP, when logged in ] to a separate table,<\/li>\n\n\n\n<li>calling that procedure on each login [ applies only to not-administrators ]<\/li>\n<\/ul>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nCREATE DATABASE mysql_audit;\nUSE mysql_audit;\nCREATE TABLE `login_audit` (\n  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),\n  `who` varchar(128) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\nDELIMITER \/\/\nCREATE OR REPLACE PROCEDURE login_audit_trigger()\nSQL SECURITY DEFINER\nBEGIN\n  INSERT INTO mysql_audit.login_audit(ts,who) VALUES (now(),user());\nEND;\n\/\/\n\nDELIMITER ;\n\n-- clean up old entries after 3 months\nCREATE OR REPLACE EVENT cleanup_login_audit ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO DELETE FROM mysql_audit.login_audit WHERE ts&amp;lt;DATE_SUB(NOW(), INTERVAL 3 MONTH) ;\n\n-- make sure to have events enabled - event_scheduler=ON in &#x5B;mysqld] section of db&#039;s config\n\n-- allow each existing user to call that procedure\nSELECT User,Host FROM mysql.user;\n-- repeat for each user\nGRANT EXECUTE ON PROCEDURE mysql_audit.login_audit_trigger TO &#039;app&#039;@&#039;%&#039;;\n-- ....\nFLUSH PRIVILEGES;\n\nSET GLOBAL init_connect=&#039;call mysql_audit.login_audit_trigger()&#039;;\n\n-- once confirmed it works &#x5B; log in as non-admin user, run some query, check if mysql_audit.login_audit has new rows]\n-- add this to my.cnf in mysqld section: \n-- init_connect=&quot;call mysql_audit.login_audit_trigger()&quot;\n-- and restart mysql\n<\/pre><\/div>\n\n\n<p><\/p>\n\n\n\n<p>important &#8211; for as long as audit is enabled &#8211; we have to expicitly run GRANT EXECUTE as above for all database users created in the future; there&#8217;s no way to do it in a future-proof way.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>based on https:\/\/fromdual.com\/last-login-off-mysql-database-users and https:\/\/stackoverflow.com\/questions\/47034690\/mysql-user-last-login-access-date-time<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>there&#8217;s also audit plugin for MySQL \/ PerconaDB &#8211; https:\/\/docs.percona.com\/percona-server\/8.0\/management\/audit_log_plugin.html<\/p>\n","protected":false},"excerpt":{"rendered":"<p>once in a while i need to review which database accounts are used, from what IPs connections arrive. MySQL \/ MariaDB does not have built-in mechanism [ unless you want to allow full query log ], but there&#8217;s quite easy way to get the data.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[12],"class_list":["post-3397","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-mysql"],"_links":{"self":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/comments?post=3397"}],"version-history":[{"count":6,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3397\/revisions"}],"predecessor-version":[{"id":3614,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3397\/revisions\/3614"}],"wp:attachment":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/media?parent=3397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/categories?post=3397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/tags?post=3397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}