Percona’s MySQL Audit Log Plugin - An Enterprise Feature at a Community Price -... - JOYK Joy of Geek, Geek News, Link all geek

An Enterprise Feature at a Community Price

Percona’s MySQL Audit Log Plugin The need to audit who is using a database and their activities is crucial in many environments. This functionality is available in the free, open source Audit Plugin that is included in Percona Server for MySQL.

The prerequisite for installing Percona’s Audit Log Plugin is to first install Percona Server for MySQL. Percona Server for MySQL is a drop-in replacement for MySQL.

The Audit Log Plugin itself is easy to install requiring only a shared object library to be loaded from a MySQL client program with INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;.

Shell

stoker@testbox:~/Downloads$ mysql -u root -p mysql

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 8.0.30-22 Percona Server (GPL), Release '22', Revision '7e301439b65'

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Query OK, 0 rows affected (0.06 sec)

mysql>

If you desire to double-check the installation, you need only query the Information Schema’s Plugins table.

Shell

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G

*************************** 1. row ***************************

PLUGIN_NAME: audit_log

PLUGIN_VERSION: 0.2

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: AUDIT

PLUGIN_TYPE_VERSION: 4.1

PLUGIN_LIBRARY: audit_log.so

PLUGIN_LIBRARY_VERSION: 1.11

PLUGIN_AUTHOR: Percona LLC and/or its affiliates.

PLUGIN_DESCRIPTION: Audit log

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row in set (0.01 sec)

mysql>

The Audit Log will collect information on connections, queries, shutdowns, deletions, and other activities in JSON, CSV, or XML formats (your choice). The logging itself is highly configurable with settings to include or exclude specific users, data, or commands. And the audit trail can be sent to a file or SYSLOG.

If you have never used audit logs before please be advised that the output at first seems verbose. And the first record you will see in this log is the loading of the audit log shared object itself.

Shell

stoker@testbox:$ sudo cat /var/lib/mysql/audit.log

<?xml version="1.0" encoding="UTF-8"?>

<AUDIT>

<AUDIT_RECORD

NAME="Audit"

RECORD="1_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:39:45Z"

MYSQL_VERSION="8.0.30-22"

STARTUP_OPTIONS=""

OS_VERSION="x86_64-Linux"

<AUDIT_RECORD

NAME="Query"

RECORD="2_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:39:45Z"

COMMAND_CLASS="install_plugin"

CONNECTION_ID="9"

STATUS="0"

SQLTEXT="INSTALL PLUGIN audit_log SONAME 'audit_log.so'"

USER="root[root] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB=""

Example

To demonstrate how the Audit Plugin functions, consider the following where two accounts are created and then included in the variable audit_log_include_accounts.

Shell

mysql> CREATE USER 'test1'@'%' IDENTIFIED BY '1Test1';

Query OK, 0 rows affected (0.13 sec)

mysql> CREATE USER 'test2'@'%' IDENTIFIED BY '2Test2';

Query OK, 0 rows affected (0.14 sec)

mysql> SET GLOBAL audit_log_include_accounts ='test1@%,test2@%';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@audit_log_include_accounts;

+------------------------------+

| @@audit_log_include_accounts |

+------------------------------+

| test1@%,test2@% |

+------------------------------+

1 row in set (0.00 sec)

mysql>

What is recorded?

What is recorded is the activity on the server and this is configurable to include or exclude accounts, schemas, or actions. Connecting to the server with one of the new accounts generates two records – a Connect and a Query as can be seen below. Using MySQL Shell generates more entries as the shell requests more data at connection time to populate variables.

Shell

<AUDIT_RECORD

NAME="Connect"

RECORD="11_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:51:48Z"

CONNECTION_ID="10"

STATUS="0"

USER="test1"

PRIV_USER="test1"

OS_LOGIN=""

PROXY_USER=""

HOST="localhost"

IP=""

DB=""

<AUDIT_RECORD

NAME="Query"

RECORD="12_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:51:48Z"

COMMAND_CLASS="select"

CONNECTION_ID="10"

STATUS="0"

SQLTEXT="select @@version_comment limit 1"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB=""

Maybe there are some commands that you do not care to audit. For instance, while you care deeply about tables being deleted, suppose that you do not want to audit table creation, SELECTS, or changes to a different database. You can pick the commands to audit with a command similar to SET GLOBAL audit_log_include_commands= ‘set_option,create_db’;. So how do you get a list of the commands that can be audited?

Shell

mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;

And you see your server’s equivalent to the following:

Shell

+------------------------------------------+

| name |

+------------------------------------------+

| statement/sql/alter_db |

| statement/sql/alter_db_upgrade |

| statement/sql/alter_event |

| statement/sql/alter_function |

| statement/sql/alter_procedure |

| statement/sql/alter_server |

| statement/sql/alter_table |

| statement/sql/alter_tablespace |

| statement/sql/alter_user |

| statement/sql/analyze |

| statement/sql/assign_to_keycache |

| statement/sql/begin |

| statement/sql/binlog |

| statement/sql/call_procedure |

| statement/sql/change_db |

| statement/sql/change_master |

| statement/sql/xa_rollback |

| statement/sql/xa_start |

+------------------------------------------+

145 rows in set (0.00 sec)

What to look for

Learning to read the audit log will not take a lot of time. A good place to start is to look at cases of people trying to log in but being unsuccessful. A bad login, where the password was ‘fat fingered’ (on purpose below), generates a slightly different pattern where a Quit is generated, not a connection.

Shell

<AUDIT_RECORD

NAME="Quit"

RECORD="43_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:57:48Z"

CONNECTION_ID="16"

STATUS="0"

USER="test1"

PRIV_USER="test1"

OS_LOGIN=""

PROXY_USER=""

HOST="localhost"

IP=""

DB="davetest"

<AUDIT_RECORD

NAME="Connect"

RECORD="44_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:57:54Z"

CONNECTION_ID="17"

STATUS="1045"

USER="test1"

PRIV_USER="test1"

OS_LOGIN=""

PROXY_USER=""

HOST="localhost"

IP=""

DB=""

A good login using mysql -u test1 -p davetest generated a Connect

Shell

<AUDIT_RECORD

NAME="Connect"

RECORD="45_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:58:34Z"

CONNECTION_ID="18"

STATUS="0"

USER="test1"

PRIV_USER="test1"

OS_LOGIN=""

PROXY_USER=""

HOST="localhost"

IP=""

DB="davetest"

<AUDIT_RECORD

NAME="Query"

RECORD="46_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:58:34Z"

COMMAND_CLASS="show_databases"

CONNECTION_ID="18"

STATUS="0"

SQLTEXT="show databases"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB="davetest"

<AUDIT_RECORD

NAME="Query"

RECORD="47_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:58:34Z"

COMMAND_CLASS="show_tables"

CONNECTION_ID="18"

STATUS="0"

SQLTEXT="show tables"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB="davetest"

<AUDIT_RECORD

NAME="Query"

RECORD="48_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T18:58:34Z"

COMMAND_CLASS="select"

CONNECTION_ID="18"

STATUS="0"

SQLTEXT="select @@version_comment limit 1"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB="davetest"

I recommend starting with one type of action and surveying the log for all the occurrences. Maybe your instance is having a lot of ALTER TABLE activity or bad logins. Look for patterns that may need your attention.

Example

The recording of the creation of a simple table and a row of data likewise generate audit log entries.

Shell

mysql> create table t1 (id int unsigned not null auto_increment primary key, c1 int, extra JSON);

Query OK, 0 rows affected (0.63 sec)

mysql> insert into t1 (c1,extra) values (101,'{ "Name": "Dave"}');

Query OK, 1 row affected (0.10 sec)

mysql>

Here we can see the exact query executed, timestamp data, connection information, and the command status.

Shell

<AUDIT_RECORD

NAME="Query"

RECORD="50_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T19:04:09Z"

COMMAND_CLASS="create_table"

CONNECTION_ID="18"

STATUS="0"

SQLTEXT="create table t1 (id int unsigned not null auto_increment primary key, c1 int, extra JSON)"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB="davetest"

<AUDIT_RECORD

NAME="Query"

RECORD="51_2022-11-21T18:39:45"

TIMESTAMP="2022-11-21T19:05:20Z"

COMMAND_CLASS="insert"

CONNECTION_ID="18"

STATUS="0"

SQLTEXT="insert into t1 (c1,extra) values (101,'{ "Name": "Dave"}')"

USER="test1[test1] @ localhost []"

HOST="localhost"

OS_USER=""

IP=""

DB="davetest"

The Percona Audit Plugin is highly configurable. You can include accounts by issuing a SET GLOBAL audit_log_include_accounts = ‘user1@host,root@host’; or exclude with SET GLOBAL audit_log_exclude_accounts = ‘user1@host,root@host’;. Likewise, you can include or exclude databases with SET GLOBAL audit_log_include_databases = ‘test,mysql,db1’;

You can also set the overall audit_log_policy too. This variable is used to specify which events should be logged, such as ALL – all events will be logged, LOGINS – only, Logins will be logged, QUERIES – only queries will be logged, and NONE – no events will be logged. Combined with the options listed above to include or exclude, it should be easy enough to concentrate down to the most critical events and resources that need to be monitored.

To save you from running out of disk space you also have control of when to rotate the audit log file itself when the file reaches the size you deem appropriate.

Conclusion

The Audit Log Plugin is an enterprise-level feature that comes included with the free, open source Percona Server for MySQL. If you wanted this feature for Oracle’s MySQL then you would need to purchase an Enterprise License. The settings for Percona’s Audit Login Plugin can be as encompassing or as granular as you need when you need that extra bit of security on your instance.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Download Percona Distribution for MySQL Today

Percona’s MySQL Audit Log Plugin - An Enterprise Feature at a Community Price -...

Example

What is recorded?

What to look for

Example

Conclusion

Recommend

《萌探探探案》免费完整在线观看-真人秀-雷神影院

《中国好声音2022》全集免费观看高清播放-真人秀-雷神影院

《令人心动的offer第四季》免费观看完整版最新一期-真人秀-雷神影院

《巴顿将军》在线观看完整版全集-热播电视剧-雷神影院

《动态漫画·铁姬钢兵第三季》免费在线全集播放-新番动漫-雷神影院

《脱口秀大会第五季》完整版全集免费在线观看-真人秀-雷神影院

《月歌行》在线观看全集免费-热播电视剧-雷神影院

《舒克贝塔第四季》未删减在线免费观看-新番动漫-雷神影院

Twitter’s Developing a New ‘Human’ Test for Accounts to Combat Bots in the App

34年换5个技术方向, 金山办公不想停止"折腾" | 卓越技术团队访谈录 | 程序...

About Joyk