1.. _audit_log_plugin: 2 3================== 4 Audit Log Plugin 5================== 6 7Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server. Information about the activity will be stored in the XML log file where each event will have its ``NAME`` field, its own unique ``RECORD_ID`` field and a ``TIMESTAMP`` field. This implementation is alternative to the `MySQL Enterprise Audit Log Plugin <dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html>`_ 8 9Audit Log plugin produces the log of following events: 10 11* **Audit** - Audit event indicates that audit logging started or finished. ``NAME`` field will be ``Audit`` when logging started and ``NoAudit`` when logging finished. Audit record also includes server version and command-line arguments. 12 13Example of the Audit event: :: 14 15 <AUDIT_RECORD 16 "NAME"="Audit" 17 "RECORD"="1_2014-04-29T09:29:40" 18 "TIMESTAMP"="2014-04-29T09:29:40 UTC" 19 "MYSQL_VERSION"="5.6.17-65.0-655.trusty" 20 "STARTUP_OPTIONS"="--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306" 21 "OS_VERSION"="x86_64-debian-linux-gnu", 22 /> 23 24* **Connect**/**Disconnect** - Connect record event will have ``NAME`` field ``Connect`` when user logged in or login failed, or ``Quit`` when connection is closed. Additional fields for this event are ``CONNECTION_ID``, ``STATUS``, ``USER``, ``PRIV_USER``, ``OS_LOGIN``, ``PROXY_USER``, ``HOST``, and ``IP``. ``STATUS`` will be ``0`` for successful logins and non-zero for failed logins. 25 26Example of the Disconnect event: :: 27 28 <AUDIT_RECORD 29 "NAME"="Quit" 30 "RECORD"="24_2014-04-29T09:29:40" 31 "TIMESTAMP"="2014-04-29T10:20:13 UTC" 32 "CONNECTION_ID"="49" 33 "STATUS"="0" 34 "USER"="" 35 "PRIV_USER"="" 36 "OS_LOGIN"="" 37 "PROXY_USER"="" 38 "HOST"="" 39 "IP"="" 40 "DB"="" 41 /> 42 43* **Query** - Additional fields for this event are: ``COMMAND_CLASS`` (values come from the ``com_status_vars`` array in the :file:`sql/mysqld.cc`` file in a MySQL source distribution. Examples are ``select``, ``alter_table``, ``create_table``, etc.), ``CONNECTION_ID``, ``STATUS`` (indicates error when non-zero), ``SQLTEXT`` (text of SQL-statement), ``USER``, ``HOST``, ``OS_USER``, ``IP``. Possible values for the ``NAME`` name field for this event are ``Query``, ``Prepare``, ``Execute``, ``Change user``, etc. 44 45Example of the Query event: :: 46 47 <AUDIT_RECORD 48 "NAME"="Query" 49 "RECORD"="23_2014-04-29T09:29:40" 50 "TIMESTAMP"="2014-04-29T10:20:10 UTC" 51 "COMMAND_CLASS"="select" 52 "CONNECTION_ID"="49" 53 "STATUS"="0" 54 "SQLTEXT"="SELECT * from mysql.user" 55 "USER"="root[root] @ localhost []" 56 "HOST"="localhost" 57 "OS_USER"="" 58 "IP"="" 59 /> 60 61Installation 62============ 63 64Audit Log plugin is shipped with |Percona Server|, but it is not installed by default. To enable the plugin you must run the following command: 65 66.. code-block:: mysql 67 68 INSTALL PLUGIN audit_log SONAME 'audit_log.so'; 69 70You can check if the plugin is loaded correctly by running: 71 72.. code-block:: mysql 73 74 SHOW PLUGINS; 75 76Audit log should be listed in the output: 77 78.. code-block:: mysql 79 80 +--------------------------------+----------+--------------------+--------------+---------+ 81 | Name | Status | Type | Library | License | 82 +--------------------------------+----------+--------------------+--------------+---------+ 83 ... 84 | audit_log | ACTIVE | AUDIT | audit_log.so | GPL | 85 +--------------------------------+----------+--------------------+--------------+---------+ 86 87Log Format 88========== 89 90The audit log plugin supports four log formats: ``OLD``, ``NEW``, ``JSON``, and ``CSV``. ``OLD`` and ``NEW`` formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats. The log format choice is controlled by :variable:`audit_log_format` variable. 91 92Example of the ``OLD`` format: :: 93 94 <AUDIT_RECORD 95 "NAME"="Query" 96 "RECORD"="2_2014-04-28T09:29:40" 97 "TIMESTAMP"="2014-04-28T09:29:40 UTC" 98 "COMMAND_CLASS"="install_plugin" 99 "CONNECTION_ID"="47" 100 "STATUS"="0" 101 "SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'" 102 "USER"="root[root] @ localhost []" 103 "HOST"="localhost" 104 "OS_USER"="" 105 "IP"="" 106 /> 107 108Example of the ``NEW`` format: :: 109 110 <AUDIT_RECORD> 111 <NAME>Quit</NAME> 112 <RECORD>10902_2014-04-28T11:02:54</RECORD> 113 <TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP> 114 <CONNECTION_ID>36</CONNECTION_ID> 115 <STATUS>0</STATUS> 116 <USER></USER> 117 <PRIV_USER></PRIV_USER> 118 <OS_LOGIN></OS_LOGIN> 119 <PROXY_USER></PROXY_USER> 120 <HOST></HOST> 121 <IP></IP> 122 <DB></DB> 123 </AUDIT_RECORD> 124 125Example of the ``JSON`` format: :: 126 127 {"audit_record":{"name":"Query","record":"4707_2014-08-27T10:43:52","timestamp":"2014-08-27T10:44:19 UTC","command_class":"show_databases","connection_id":"37","status":0,"sqltext":"show databases","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":""}} 128 129Example of the ``CSV`` format: :: 130 131 "Query","49284_2014-08-27T10:47:11","2014-08-27T10:47:23 UTC","show_databases","37",0,"show databases","root[root] @ localhost []","localhost","","" 132 133.. _streaming_to_syslog: 134 135Streaming the audit log to syslog 136================================= 137 138Ability to stream the audit log to `syslog <http://www.syslog.org/>`_ was implemented in |Percona Server| :rn:`5.6.20-68.0`. To stream the audit log to syslog you'll need to set :variable:`audit_log_handler` variable to ``SYSLOG``. To control the syslog file handler, the following variables can be used: :variable:`audit_log_syslog_ident`, :variable:`audit_log_syslog_facility`, and :variable:`audit_log_syslog_priority` These variables have the same meaning as appropriate parameters described in the `syslog(3) manual <http://linux.die.net/man/3/syslog>`_. 139 140.. note:: 141 142 Variables: :variable:`audit_log_strategy`, :variable:`audit_log_buffer_size`, :variable:`audit_log_rotate_on_size`, :variable:`audit_log_rotations` have effect only with ``FILE`` handler. 143 144.. _filtering_by_user: 145 146Filtering by user 147================= 148 149In :rn:`5.6.32-78.0` |Percona Server| has implemented filtering by user. This 150was implemented by adding two new global variables: 151:variable:`audit_log_include_accounts` and 152:variable:`audit_log_exclude_accounts` to specify which user accounts should be 153included or excluded from audit logging. 154 155.. warning:: 156 157 Only one of these variables can contain a list of users to be either 158 included or excluded, while the other needs to be ``NULL``. If one of the 159 variables is set to be not ``NULL`` (contains a list of users), the attempt 160 to set another one will fail. Empty string means an empty list. 161 162.. note:: 163 164 Changes of :variable:`audit_log_include_accounts` and 165 :variable:`audit_log_exclude_accounts` do not apply to existing server 166 connections. 167 168Example 169------- 170 171Following example shows adding users who will be monitored: 172 173.. code-block:: mysql 174 175 mysql> SET GLOBAL audit_log_include_accounts = 'user1@localhost,root@localhost'; 176 Query OK, 0 rows affected (0.00 sec) 177 178If you you try to add users to both include and exclude lists server will show 179you the following error: 180 181.. code-block:: mysql 182 183 mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost'; 184 ERROR 1231 (42000): Variable 'audit_log_exclude_accounts' can't be set to the value of 'user1@localhost,root@localhost' 185 186To switch from filtering by included user list to the excluded one or back, 187first set the currently active filtering variable to ``NULL``: 188 189.. code-block:: mysql 190 191 mysql> SET GLOBAL audit_log_include_accounts = NULL; 192 Query OK, 0 rows affected (0.00 sec) 193 194 mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost'; 195 Query OK, 0 rows affected (0.00 sec) 196 197 mysql> SET GLOBAL audit_log_exclude_accounts = "'user'@'host'"; 198 Query OK, 0 rows affected (0.00 sec) 199 200 mysql> SET GLOBAL audit_log_exclude_accounts = '''user''@''host'''; 201 Query OK, 0 rows affected (0.00 sec) 202 203 mysql> SET GLOBAL audit_log_exclude_accounts = '\'user\'@\'host\''; 204 Query OK, 0 rows affected (0.00 sec) 205 206To see what users are currently in the on the list you can run: 207 208.. code-block:: mysql 209 210 mysql> SELECT @@audit_log_exclude_accounts; 211 +------------------------------+ 212 | @@audit_log_exclude_accounts | 213 +------------------------------+ 214 | 'user'@'host' | 215 +------------------------------+ 216 1 row in set (0.00 sec) 217 218Account names are the ones that are logged in the audit log. For example when 219you create a user: 220 221.. code-block:: mysql 222 223 mysql> CREATE USER 'user1'@'%' IDENTIFIED BY '111'; 224 Query OK, 0 rows affected (0.00 sec) 225 226This is what you'll see when ``user1`` connected from ``localhost``: 227 228.. code-block:: none 229 230 <AUDIT_RECORD 231 NAME="Connect" 232 RECORD="21_2016-06-30T09:29:51" 233 TIMESTAMP="2016-06-30T09:35:04 UTC" 234 CONNECTION_ID="6" 235 STATUS="0" 236 USER="user1" ;; this is a 'user' part of account in 5.6 237 PRIV_USER="user1" 238 OS_LOGIN="" 239 PROXY_USER="" 240 HOST="localhost" ;; this is a 'host' part of account in 5.6 241 IP="" 242 DB="" 243 /> 244 245To exclude ``user1`` from logging in |Percona Server| 5.6 you must set: 246 247.. code-block:: mysql 248 249 SET GLOBAL audit_log_exclude_accounts = 'user1@localhost'; 250 251The value can be ``NULL`` or comma separated list of accounts in form 252``user@host`` or ``'user'@'host'`` (if user or host contains comma). 253 254.. _filtering_by_sql_command_type: 255 256Filtering by SQL command type 257============================= 258 259In :rn:`5.6.32-78.0` |Percona Server| has implemented filtering by SQL command 260type. This was implemented by adding two new global variables: 261:variable:`audit_log_include_commands` and 262:variable:`audit_log_exclude_commands` to specify which command types should be 263included or excluded from audit logging. 264 265.. warning:: 266 267 Only one of these variables can contain a list of command types to be 268 either included or excluded, while the other needs to be ``NULL``. If one of 269 the variables is set to be not ``NULL`` (contains a list of command types), 270 the attempt to set another one will fail. Empty string means an empty list. 271 272.. note:: 273 274 If both :variable:`audit_log_exclude_commands` and 275 :variable:`audit_log_include_commands` are ``NULL`` all commands will be 276 logged. 277 278Example 279------- 280 281The available command types can be listed by running: 282 283.. code-block:: mysql 284 285 mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name; 286 +------------------------------------------+ 287 | name | 288 +------------------------------------------+ 289 | statement/sql/alter_db | 290 | statement/sql/alter_db_upgrade | 291 | statement/sql/alter_event | 292 | statement/sql/alter_function | 293 | statement/sql/alter_procedure | 294 | statement/sql/alter_server | 295 | statement/sql/alter_table | 296 | statement/sql/alter_tablespace | 297 | statement/sql/alter_user | 298 | statement/sql/analyze | 299 | statement/sql/assign_to_keycache | 300 | statement/sql/begin | 301 | statement/sql/binlog | 302 | statement/sql/call_procedure | 303 | statement/sql/change_db | 304 | statement/sql/change_master | 305 ... 306 | statement/sql/xa_rollback | 307 | statement/sql/xa_start | 308 +------------------------------------------+ 309 145 rows in set (0.00 sec) 310 311You can add commands to the include filter by running: 312 313.. code-block:: mysql 314 315 mysql> SET GLOBAL audit_log_include_commands= 'set_option,create_db'; 316 317If you now create a database: 318 319.. code-block:: mysql 320 321 mysql> CREATE DATABASE world; 322 323You'll see it the audit log: 324 325.. code-block:: none 326 327 <AUDIT_RECORD 328 NAME="Query" 329 RECORD="10724_2016-08-18T12:34:22" 330 TIMESTAMP="2016-08-18T15:10:47 UTC" 331 COMMAND_CLASS="create_db" 332 CONNECTION_ID="61" 333 STATUS="0" 334 SQLTEXT="create database world" 335 USER="root[root] @ localhost []" 336 HOST="localhost" 337 OS_USER="" 338 IP="" 339 DB="" 340 /> 341 342To switch command type filtering type from included type list to excluded one 343or back, first reset the currently-active list to ``NULL``: 344 345.. code-block:: mysql 346 347 mysql> SET GLOBAL audit_log_include_commands = NULL; 348 Query OK, 0 rows affected (0.00 sec) 349 350 mysql> SET GLOBAL audit_log_exclude_commands= 'set_option,create_db'; 351 Query OK, 0 rows affected (0.00 sec) 352 353.. note:: 354 355 Invocation of stored procedures have command type ``call_procedure``, and all 356 the statements executed within the procedure have the same type 357 ``call_procedure`` as well. 358 359System Variables 360================ 361 362.. variable:: audit_log_strategy 363 364 :version 5.6.17-65.0: Implemented 365 :cli: Yes 366 :scope: Global 367 :dyn: No 368 :vartype: String 369 :default: ASYNCHRONOUS 370 :allowed values: ``ASYNCHRONOUS``, ``PERFORMANCE``, ``SEMISYNCHRONOUS``, ``SYNCHRONOUS`` 371 372This variable is used to specify the audit log strategy, possible values are: 373 374* ``ASYNCHRONOUS`` - (default) log using memory buffer, do not drop messages if buffer is full 375* ``PERFORMANCE`` - log using memory buffer, drop messages if buffer is full 376* ``SEMISYNCHRONOUS`` - log directly to file, do not flush and sync every event 377* ``SYNCHRONOUS`` - log directly to file, flush and sync every event 378 379This variable has effect only when :variable:`audit_log_handler` is set to ``FILE``. 380 381.. variable:: audit_log_file 382 383 :version 5.6.17-65.0: Implemented 384 :cli: Yes 385 :scope: Global 386 :dyn: No 387 :vartype: String 388 :default: audit.log 389 390This variable is used to specify the filename that's going to store the audit log. It can contain the path relative to the datadir or absolute path. 391 392.. variable:: audit_log_flush 393 394 :version 5.6.17-65.0: Implemented 395 :cli: Yes 396 :scope: Global 397 :dyn: Yes 398 :vartype: String 399 :default: OFF 400 401When this variable is set to ``ON`` log file will be closed and reopened. This can be used for manual log rotation. 402 403.. variable:: audit_log_buffer_size 404 405 :version 5.6.17-65.0: Implemented 406 :cli: Yes 407 :scope: Global 408 :dyn: No 409 :vartype: Numeric 410 :default: 1 Mb 411 412This variable can be used to specify the size of memory buffer used for logging, used when :variable:`audit_log_strategy` variable is set to ``ASYNCHRONOUS`` or ``PERFORMANCE`` values. This variable has effect only when :variable:`audit_log_handler` is set to ``FILE``. 413 414.. variable:: audit_log_exclude_accounts 415 416 :version 5.6.32-78.0: Implemented 417 :cli: Yes 418 :scope: Global 419 :dyn: Yes 420 :vartype: String 421 422This variable is used to specify the list of users for which 423:ref:`filtering_by_user` is applied. The value can be ``NULL`` or comma 424separated list of accounts in form ``user@host`` or ``'user'@'host'`` (if user 425or host contains comma). If this variable is set, then 426:variable:`audit_log_include_accounts` must be unset, and vice versa. 427 428.. variable:: audit_log_exclude_commands 429 430 :version 5.6.32-78.0: Implemented 431 :cli: Yes 432 :scope: Global 433 :dyn: Yes 434 :vartype: String 435 436This variable is used to specify the list of commands for which 437:ref:`filtering_by_sql_command_type` is applied. The value can be ``NULL`` or 438comma separated list of commands. If this variable is set, then 439:variable:`audit_log_include_commands` must be unset, and vice versa. 440 441.. variable:: audit_log_format 442 443 :version 5.6.17-65.0: Original implementation 444 :version 5.6.20-68.0: Implemented support for ``CSV`` and ``JSON`` log formats 445 :cli: Yes 446 :scope: Global 447 :dyn: No 448 :vartype: String 449 :default: OLD 450 :allowed values: ``OLD``, ``NEW``, ``CSV``, ``JSON`` 451 452This variable is used to specify the audit log format. The audit log plugin supports four log formats: ``OLD``, ``NEW``, ``JSON``, and ``CSV``. ``OLD`` and ``NEW`` formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats. 453 454.. variable:: audit_log_include_accounts 455 456 :version 5.6.32-78.0: Implemented 457 :cli: Yes 458 :scope: Global 459 :dyn: Yes 460 :vartype: String 461 462This variable is used to specify the list of users for which 463:ref:`filtering_by_user` is applied. The value can be ``NULL`` or comma 464separated list of accounts in form ``user@host`` or ``'user'@'host'`` (if user 465or host contains comma). If this variable is set, then 466:variable:`audit_log_exclude_accounts` must be unset, and vice versa. 467 468.. variable:: audit_log_include_commands 469 470 :version 5.6.32-78.0: Implemented 471 :cli: Yes 472 :scope: Global 473 :dyn: Yes 474 :vartype: String 475 476This variable is used to specify the list of commands for which 477:ref:`filtering_by_sql_command_type` is applied. The value can be ``NULL`` or 478comma separated list of commands. If this variable is set, then 479:variable:`audit_log_exclude_commands` must be unset, and vice versa. 480 481.. variable:: audit_log_policy 482 483 :version 5.6.17-65.0: Implemented 484 :cli: Yes 485 :scope: Global 486 :dyn: Yes 487 :vartype: String 488 :default: ALL 489 :allowed values: ``ALL``, ``LOGINS``, ``QUERIES``, ``NONE`` 490 491This variable is used to specify which events should be logged. Possible values are: 492 493* ``ALL`` - all events will be logged 494* ``LOGINS`` - only logins will be logged 495* ``QUERIES`` - only queries will be logged 496* ``NONE`` - no events will be logged 497 498.. variable:: audit_log_rotate_on_size 499 500 :version 5.6.17-65.0: Implemented 501 :cli: Yes 502 :scope: Global 503 :dyn: No 504 :vartype: Numeric 505 :default: 0 (don't rotate the log file) 506 507This variable specifies the maximum size of the audit log file. Upon reaching 508this size, the audit log will be rotated. The rotated log files are present in 509the same directory as the current log file. The sequence number is appended to 510the log file name upon rotation. For this variable to take effect, set the 511:variable:`audit_log_handler` variable to ``FILE`` and the 512:variable:`audit_log_rotations` variable to a value greater than zero. 513 514.. variable:: audit_log_rotations 515 516 :version 5.6.17-65.0: Implemented 517 :cli: Yes 518 :scope: Global 519 :dyn: No 520 :vartype: Numeric 521 :default: 0 522 523This variable is used to specify how many log files should be kept when :variable:`audit_log_rotate_on_size` variable is set to non-zero value. This variable has effect only when :variable:`audit_log_handler` is set to ``FILE``. 524 525.. variable:: audit_log_handler 526 527 :version 5.6.20-68.0: Implemented 528 :cli: Yes 529 :scope: Global 530 :dyn: No 531 :vartype: String 532 :default: FILE 533 :allowed values: ``FILE``, ``SYSLOG`` 534 535This variable is used to configure where the audit log will be written. If it is set to ``FILE``, the log will be written into a file specified by :variable:`audit_log_file` variable. If it is set to ``SYSLOG``, the audit log will be written to syslog. 536 537.. variable:: audit_log_syslog_ident 538 539 :version 5.6.20-68.0: Implemented 540 :cli: Yes 541 :scope: Global 542 :dyn: No 543 :vartype: String 544 :default: percona-audit 545 546This variable is used to specify the ``ident`` value for syslog. This variable has the same meaning as the appropriate parameter described in the `syslog(3) manual <http://linux.die.net/man/3/syslog>`_. 547 548.. variable:: audit_log_syslog_facility 549 550 :version 5.6.20-68.0: Implemented 551 :cli: Yes 552 :scope: Global 553 :dyn: No 554 :vartype: String 555 :default: LOG_USER 556 557This variable is used to specify the ``facility`` value for syslog. This variable has the same meaning as the appropriate parameter described in the `syslog(3) manual <http://linux.die.net/man/3/syslog>`_. 558 559.. variable:: audit_log_syslog_priority 560 561 :version 5.6.20-68.0: Implemented 562 :cli: Yes 563 :scope: Global 564 :dyn: No 565 :vartype: String 566 :default: LOG_INFO 567 568This variable is used to specify the ``priority`` value for syslog. This variable has the same meaning as the appropriate parameter described in the `syslog(3) manual <http://linux.die.net/man/3/syslog>`_. 569 570Status Variables 571================ 572 573.. variable:: Audit_log_buffer_size_overflow 574 575 :vartype: Numeric 576 :scope: Global 577 578The number of times an audit log entry was either 579dropped or written directly to the file due to its size being bigger 580than :variable:`audit_log_buffer_size` variable. 581 582Version Specific Information 583============================ 584 585 * :rn:`5.6.17-65.0` 586 Audit Log plugin has been implemented in |Percona Server|. 587 * :rn:`5.6.20-68.0` 588 |Percona Server| :ref:`audit_log_plugin` now supports ``JSON`` and ``CSV`` log formats. 589 * :rn:`5.6.20-68.0` 590 |Percona Server| :ref:`audit_log_plugin` now supports :ref:`streaming the audit log to syslog <streaming_to_syslog>`. 591 * :rn:`5.6.32-78.0` 592 |Percona Server| :ref:`audit_log_plugin` now supports filtering by 593 :ref:`user <filtering_by_user>` and 594 :ref:`sql_command <filtering_by_sql_command_type>`. 595 * :rn:`5.6.44-85.0` 596 :variable:`Audit_log_buffer_size_overflow` variable implemented 597