MariaDB Database Enable " server_audit " plugin and Tracking the Audit Logs for Queries

MariaDB Database Enable " server_audit " plugin and Tracking the Audit Logs for Queries

Information about audit login parameters in MariaDB Database

QUERY_DDL :- 
-------------

Similar to QUERY, but filters only DDL-type queries (CREATE, ALTER, DROP, RENAME and TRUNCATE statements - except CREATE/DROP [PROCEDURE / FUNCTION / USER] and RENAME USER (they're not DDL).


QUERY_DML :- 
--------------

Similar to QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, SELECT, UPDATE, HANDLER and REPLACE statements)

server_audit_events Description :- 
---------------------------------

If set, then this restricts audit logging to certain event types. If not set, then every event type is logged to the audit log. 

server_audit_excl_users Description :-
--------------------------------------- 

If not empty, it contains the list of users whose activity will NOT be logged. For example: SET GLOBAL server_audit_excl_users='user1, user2'. CONNECT records aren't affected by this variable - they are always logged. The user is still logged if it's specified in server_audit_incl_users.

server_audit_file_path Description :- 
-------------------------------------

When server_audit_output_type=file, sets the path and the filename to the log file. If the specified path exists as a directory, then the log will be created inside that directory with the name 'server_audit.log'. Otherwise the value is treated as a filename. The default value is 'server_audit.log', which means this file will be created in the database directory.

server_audit_file_rotate_now Description :-
-------------------------------------------- 

When server_audit_output_type=file, the user can force the log file rotation by setting this variable to ON or 1.

server_audit_file_rotate_size Description :-
--------------------------------------------- 

When server_audit_output_type=file, it limits the size of the log file to the given amount of bytes. Reaching that limit turns on the rotation - the current log file is renamed as 'file_path.1'. The empty log file is created as 'file_path' to log into it. The default value is 1000000.

server_audit_file_rotations Description :- 
---------------------------------------------

When server_audit_output_type=file', this specifies the number of rotations to save. If set to 0 then the log never rotates. The default value is 9.
if we change to 30 ..
To disable audit log rotation, simply set the server_audit_file_rotations to 0. The log rotation will happen automatically after it reaches the specified threshold and will keep the last 30 logs, which means the last 30 days' worth of audit logging.

server_audit_incl_users Description :- 
----------------------------------------

If not empty, it contains a comma-delimited list of users whose activity will be logged. 
For example: SET GLOBAL server_audit_incl_users='user_foo, user_bar'. CONNECT records aren't affected by this variable - they are always logged. This setting has higher priority than server_audit_excl_users. So if the same user is specified both in incl_ and excl_ lists, they will still be logged.

server_audit_logging Description :-
------------------------------------

Enables/disables the logging. Expected values are ON/OFF. For example: SET GLOBAL server_audit_logging=on .If the server_audit_output_type is FILE, this will actually create/open the logfile so the server_audit_file_path should be properly specified beforehand. Same about the SYSLOG-related parameters. The logging is turned off by default.

server_audit_mode Description :- 
---------------------------------

This variable doesn't have any distinctive meaning for a user. Its value mostly reflects the server version with which the plugin was started and is intended to be used by developers for testing.

server_audit_output_type Description :- 
----------------------------------------

Specifies the desired output type. Can be SYSLOG or FILE. For example: SET GLOBAL server_audit_output_type=file file: log records will be saved into the rotating log file. The name of the file set by server_audit_file_path variable. syslog: log records will be sent to the local syslogd daemon with the standard <syslog.h> API. The default value is 'file'.

server_audit_query_log_limit Description :- 
---------------------------------------------
Limit on the length of the query string in a record.

server_audit_syslog_facility Description :- 
------------------------------------------------

SYSLOG-mode variable. It defines the 'facility' of the records that will be sent to the syslog. Later the log can be filtered by this parameter.

server_audit_syslog_ident Description :- 
---------------------------------------------

SYSLOG-mode variable. String value for the 'ident' part of each syslog record. Default value is 'mysql-server_auditing'. New value becomes effective only after restarting the logging.

server_audit_syslog_info Description :- 
----------------------------------------------

SYSLOG-mode variable. The 'info' string to be added to the syslog records. Can be changed any time.

server_audit_syslog_priority Description :- 
--------------------------------------------

SYSLOG-mode variable. Defines the priority of the log records for the syslogd.

server_audit Description :- 
---------------------------------

Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the mysql.plugins table.
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.

server_audit_events :-  
-----------------------

Specifies the events that you want to record.By default, the value is empty, which means that all events are recorded. 
The options are CONNECTION, QUERY, and TABLE.
Also Specifies the events you wish to have in the log. 
The options are: CONNECTION (users connecting and disconnecting and failed connects—including the error code), 
QUERY (queries and their result), and TABLE (which tables are affected by the queries).

Audit Log installation and setup for specific DDL / Specific Users 
--------------------------------------------------------------------

grep -i global /var/lib/mysql/server_audit.log
grep mariadb-audit /var/log/syslog


Implementation :-
-------------------------
Audit login installation :-
------------------------------

[root@ip-172-31-22-44 ~]# mysql -u root -p
Enter password: mariapassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 10.Server version: 10.3.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like '%plugin%';
+-----------------+------------------------------+
| Variable_name   | Value                        |
+-----------------+------------------------------+
| plugin_dir      | /usr/local/mysql/lib/plugin/ |
| plugin_maturity | gamma                        |
+-----------------+------------------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| SQL_SEQUENCE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL    | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL    | BSD     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+
53 rows in set (0.001 sec)

MariaDB [(none)]> show variables like '%audit%';
Empty set (0.001 sec)

MariaDB [(none)]> show variables like '%server%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| character_set_server            | utf8mb4                |
| collation_server                | utf8mb4_unicode_520_ci |
| innodb_ft_server_stopword_table |                        |
| server_id                       | 1                      |
+---------------------------------+------------------------+
4 rows in set (0.001 sec)

[root@ip-172-31-22-44 ~]# cd /usr/local/mysql/lib/plugin/

[root@ip-172-31-22-44 plugin]# pwd
/usr/local/mysql/lib/plugin

[root@ip-172-31-22-44 plugin]# ls
adt_null.so   caching_sha2_password.so   file_key_management.so  ha_mroonga.so JdbcInterface.jar  qa_auth_server.so
auth_0x0100.so  client_ed25519.so  ha_archive.so      handlersocket.so libdaemon_example.so  query_cache_info.so
auth_ed25519.so  daemon_example.ini   ha_blackhole.so        ha_oqgraph.so   locales.so  query_response_time.so auth_gssapi_client.so  debug_key_management.so    ha_cassandra.so    ha_sphinx.so  metadata_lock_info.so   server_audit.so
auth_gssapi.so  dialog_examples.so  ha_connect.so          ha_spider.so  mypluglib.so  simple_password_check.so  auth_pam.so    dialog.so  ha_example.so  ha_test_sql_discovery.so  mysql_clear_password.so  sql_errlog.so  auth_socket.so        disks.so  ha_federated.so ha_tokudb.so  qa_auth_client.so        test_versioning.so auth_test_plugin.so example_key_management.so  ha_federatedx.so JavaWrappers.jar  qa_auth_interface.so    wsrep_info.so

[root@ip-172-31-22-44 plugin]#

[root@ip-172-31-22-44 ~]# mysql -u root -p
Enter password:mariapassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 11.Server version: 10.3.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> INSTALL SONAME 'server_audit';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> SHOW PLUGINS;
+-------------------------------+----------+--------------------+----
| Name                          | Status   | Type               | Library         | License |
+-------------------------------+----------+--------------------+----
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| SQL_SEQUENCE                  | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL            | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL            | BSD     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL            | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
| SERVER_AUDIT                  | ACTIVE   | AUDIT              | server_audit.so | GPL     |
+-------------------------------+----------+--------------------+----
54 rows in set (0.001 sec)

MariaDB [(none)]> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
15 rows in set (0.001 sec)

MariaDB [(none)]>

Uninstall server_audit plugin :-
---------------------------------------

MariaDB [(none)]> UNINSTALL SONAME 'server_audit';
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> show variables like '%audit%';
Empty set (0.001 sec)

Once Enabled :- ..
-----------------------
MariaDB [(none)]> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /home/maria/data/ |
+---------------+-------------------+
1 row in set (0.001 sec)
MariaDB [(none)]> exit
Bye

[root@ip-172-31-22-44 ~]# cd /home/maria/data/

[root@ip-172-31-22-44 data]# pwd
/home/maria/data

[root@ip-172-31-22-44 data]# ls -ltr
drwx------. 2 mysql root       4096 Jul 29 12:18 mysql
drwx------. 2 mysql mysql        20 Jul 29 12:18 performance_schema
-rw-rw----. 1 mysql mysql 536870912 Jul 29 12:20 ib_logfile1
-rw-rw----. 1 mysql mysql     24576 Jul 29 12:20 tc.log
-rw-rw----. 1 mysql mysql         0 Jul 29 12:20 multi-master.info
drwx------. 2 mysql mysql        20 Jul 29 13:01 replication
-rw-rw----. 1 mysql mysql       919 Jul 29 13:01 ON.000001
-rw-rw----. 1 mysql mysql       335 Jul 29 13:04 ON.000002
drwx------. 2 mysql mysql        20 Jul 29 13:08 replication_gtid
-rw-rw----. 1 mysql mysql       981 Jul 29 17:20 ON.000003
-rw-rw----. 1 mysql mysql       374 Jul 29 17:40 ON.000004
-rw-rw----. 1 mysql mysql       374 Jul 30 16:12 ON.000005
-rw-rw----. 1 mysql mysql       374 Aug  1 17:26 ON.000006
-rw-rw----. 1 mysql mysql       374 Aug  2 06:53 ON.000007
-rw-rw----. 1 mysql mysql       374 Aug  2 18:01 ON.000008
-rw-rw----. 1 mysql mysql       374 Aug  3 16:45 ON.000009
-rw-rw----. 1 mysql mysql       374 Aug  4 16:49 ON.000010
-rw-rw----. 1 mysql mysql       374 Aug  5 15:18 ON.000011
-rw-rw----. 1 mysql mysql       374 Aug  6 08:15 ON.000012
-rw-rw----. 1 mysql mysql       374 Aug  6 13:09 ON.000013
-rw-rw----. 1 mysql mysql       374 Aug  7 11:33 ON.000014
-rw-rw----. 1 mysql mysql       374 Aug  9 05:24 ON.000015
-rw-rw----. 1 mysql mysql       976 Aug  9 05:24 ib_buffer_pool
-rw-rw----. 1 mysql mysql  12582912 Aug  9 05:24 ibdata1
-rw-rw----. 1 mysql mysql        52 Aug  9 05:24 aria_log_control
-rw-rw----. 1 mysql mysql     16384 Aug  9 05:24 aria_log.00000001
-rw-rw----. 1 mysql mysql       351 Aug 11 03:29 ON.000016
-rw-rw----. 1 mysql mysql       192 Aug 11 03:29 ON.index
srwxrwxrwx. 1 mysql mysql         0 Aug 11 03:29 mysql.sock
-rw-rw----. 1 mysql mysql         5 Aug 11 03:29 ip-172-31-22-44.ec2.internal.pid
-rw-rw----. 1 mysql mysql 536870912 Aug 11 03:29 ib_logfile0
-rw-rw----. 1 mysql mysql  12582912 Aug 11 03:29 ibtmp1
-rw-rw----. 1 mysql mysql     84847 Aug 11 03:35 mysqld.log

[root@ip-172-31-22-44 data]#

Here we have to add the below parameters in my.cnf and restart the DB [ permanently ]
Or 
Add the below parameters in my.cnf and SET GLOBAL commands ...[ In this case no need restart the DB service ]
SET GLOBAL server_audit_logging=ON;

I.e 

[root@ip-172-31-22-44 ~]# grep server_audit /etc/my.cnf =========> in my.cnf level 
server_audit_logging = ON

[root@ip-172-31-22-44 ~]#
MariaDB [(none)]> SET GLOBAL server_audit_logging=ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | ON                    |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
15 rows in set (0.001 sec)

MariaDB [(none)]> SELECT * FROM information_schema.plugins WHERE plugin_name='server_audit'\G
*************************** 1. row ***************************
           PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 1.4
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.13
         PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
    PLUGIN_DESCRIPTION: Audit the server activity
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.4.4
1 row in set (0.001 sec)

MariaDB [(none)]>

Just restarted the service and checked whether server_audit log is enabled or not ...

[root@ip-172-31-22-44 ~]# service mysql stop
Stopping mysql (via systemctl):                            [  OK  ]

[root@ip-172-31-22-44 ~]# ps -ef | grep -i mysql
root        3124    2395  0 05:14 pts/1    00:00:00 grep --color=auto -i mysql

[root@ip-172-31-22-44 ~]# service mysql start
Starting mysql (via systemctl):                            [  OK  ]

[root@ip-172-31-22-44 ~]# mysql -u root -p
Enter password: mariapassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 10.Server version: 10.3.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | ON                    |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
15 rows in set (0.001 sec)

MariaDB [(none)]> SELECT * FROM information_schema.plugins WHERE plugin_name='server_audit'\G
*************************** 1. row ***************************
           PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 1.4
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.13
         PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
    PLUGIN_DESCRIPTION: Audit the server activity
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.4.4
1 row in set (0.001 sec)
MariaDB [(none)]>

From server_audit.log file :-
-------------------------------------------

[root@ip-172-31-22-44 ~]# cd /home/maria/data

[root@ip-172-31-22-44 data]# pwd
/home/maria/data

[root@ip-172-31-22-44 data]# ls -ltr
drwx------. 2 mysql root       4096 Jul 29 12:18 mysql
drwx------. 2 mysql mysql        20 Jul 29 12:18 performance_schema
-rw-rw----. 1 mysql mysql 536870912 Jul 29 12:20 ib_logfile1
-rw-rw----. 1 mysql mysql     24576 Jul 29 12:20 tc.log
-rw-rw----. 1 mysql mysql         0 Jul 29 12:20 multi-master.info
drwx------. 2 mysql mysql        20 Jul 29 13:01 replication
-rw-rw----. 1 mysql mysql       919 Jul 29 13:01 ON.000001
-rw-rw----. 1 mysql mysql       335 Jul 29 13:04 ON.000002
drwx------. 2 mysql mysql        20 Jul 29 13:08 replication_gtid
-rw-rw----. 1 mysql mysql       981 Jul 29 17:20 ON.000003
-rw-rw----. 1 mysql mysql       374 Jul 29 17:40 ON.000004
-rw-rw----. 1 mysql mysql       374 Jul 30 16:12 ON.000005
-rw-rw----. 1 mysql mysql       374 Aug  1 17:26 ON.000006
-rw-rw----. 1 mysql mysql       374 Aug  2 06:53 ON.000007
-rw-rw----. 1 mysql mysql       374 Aug  2 18:01 ON.000008
-rw-rw----. 1 mysql mysql       374 Aug  3 16:45 ON.000009
-rw-rw----. 1 mysql mysql       374 Aug  4 16:49 ON.000010
-rw-rw----. 1 mysql mysql       374 Aug  5 15:18 ON.000011
-rw-rw----. 1 mysql mysql       374 Aug  6 08:15 ON.000012
-rw-rw----. 1 mysql mysql       374 Aug  6 13:09 ON.000013
-rw-rw----. 1 mysql mysql       374 Aug  7 11:33 ON.000014
-rw-rw----. 1 mysql mysql       374 Aug  9 05:24 ON.000015
-rw-rw----. 1 mysql mysql       374 Aug 11 04:50 ON.000016
-rw-rw----. 1 mysql mysql       976 Aug 11 04:50 ib_buffer_pool
-rw-rw----. 1 mysql mysql  12582912 Aug 11 04:50 ibdata1
-rw-rw----. 1 mysql mysql        52 Aug 11 04:50 aria_log_control
-rw-rw----. 1 mysql mysql     16384 Aug 11 04:50 aria_log.00000001
-rw-rw----. 1 mysql mysql       351 Aug 11 04:50 ON.000017
-rw-rw----. 1 mysql mysql       351 Aug 11 05:14 ON.000018
-rw-rw----. 1 mysql mysql       216 Aug 11 05:14 ON.index
srwxrwxrwx. 1 mysql mysql         0 Aug 11 05:14 mysql.sock
-rw-rw----. 1 mysql mysql         5 Aug 11 05:14 ip-172-31-22-44.ec2.internal.pid
-rw-rw----. 1 mysql mysql    115074 Aug 11 05:14 mysqld.log
-rw-rw----. 1 mysql mysql  12582912 Aug 11 05:14 ibtmp1
-rw-rw----. 1 mysql mysql 536870912 Aug 11 05:14 ib_logfile0
-rw-rw----. 1 mysql mysql      1811 Aug 11 05:15 server_audit.log

[root@ip-172-31-22-44 data]# cat server_audit.log
20210811 04:20:12,ip-172-31-22-44.ec2.internal,root,localhost,12,19,QUERY,,'SET GLOBAL server_audit_logging=ON',0
20210811 04:20:16,ip-172-31-22-44.ec2.internal,root,localhost,12,20,QUERY,,'show variables like \'%audit%\'',0
20210811 04:20:19,ip-172-31-22-44.ec2.internal,root,localhost,12,0,DISCONNECT,,,0
20210811 04:20:22,ip-172-31-22-44.ec2.internal,,80.82.70.228,13,0,FAILED_CONNECT,,,1159
20210811 04:20:22,ip-172-31-22-44.ec2.internal,,80.82.70.228,13,0,DISCONNECT,,,0
20210811 04:20:24,ip-172-31-22-44.ec2.internal,root,localhost,14,0,FAILED_CONNECT,,,1045
20210811 04:20:24,ip-172-31-22-44.ec2.internal,root,localhost,14,0,DISCONNECT,,,0
20210811 04:58:31,ip-172-31-22-44.ec2.internal,root,localhost,12,5,QUERY,,'SET GLOBAL server_audit_logging=ON',0
20210811 04:58:35,ip-172-31-22-44.ec2.internal,root,localhost,12,6,QUERY,,'show variables like \'%audit%\'',0
20210811 04:58:55,ip-172-31-22-44.ec2.internal,root,localhost,12,7,QUERY,,'SELECT * FROM information_schema.plugins WHERE plugin_name=\'server_audit\'',0
20210811 05:13:56,ip-172-31-22-44.ec2.internal,root,localhost,12,0,DISCONNECT,,,0
20210811 05:14:22,ip-172-31-22-44.ec2.internal,root,localhost,9,0,FAILED_CONNECT,,,1045
20210811 05:14:22,ip-172-31-22-44.ec2.internal,root,localhost,9,0,DISCONNECT,,,0
20210811 05:14:28,ip-172-31-22-44.ec2.internal,root,localhost,10,0,CONNECT,,,0
20210811 05:14:28,ip-172-31-22-44.ec2.internal,root,localhost,10,1,QUERY,,'select @@version_comment limit 1',0
20210811 05:14:31,ip-172-31-22-44.ec2.internal,root,localhost,10,2,QUERY,,'show variables like \'%audit%\'',0
20210811 05:14:33,ip-172-31-22-44.ec2.internal,root,localhost,10,3,QUERY,,'SELECT * FROM information_schema.plugins WHERE plugin_name=\'server_audit\'',0
20210811 05:15:36,ip-172-31-22-44.ec2.internal,root,localhost,10,0,DISCONNECT,,,0
[root@ip-172-31-22-44 data]#
I just created one user and saw those details in server_audit.log file ...
---------------------------------------------------------------------

[root@ip-172-31-22-44 data]# mysql -u root -p
Enter password: mariapassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 11.Server version: 10.3.12-MariaDB-log MariaDB Server .Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | password                                  |
+-------------+-----------+-------------------------------------------+
| root        | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm         | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
+-------------+-----------+-------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]> CREATE USER 'app_1'@'%' IDENTIFIED BY 'app_1';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> exit
Bye

[root@ip-172-31-22-44 data]# cat server_audit.log
20210811 04:20:12,ip-172-31-22-44.ec2.internal,root,localhost,12,19,QUERY,,'SET GLOBAL server_audit_logging=ON',0
20210811 04:20:16,ip-172-31-22-44.ec2.internal,root,localhost,12,20,QUERY,,'show variables like \'%audit%\'',0
20210811 04:20:19,ip-172-31-22-44.ec2.internal,root,localhost,12,0,DISCONNECT,,,0
20210811 04:20:22,ip-172-31-22-44.ec2.internal,,80.82.70.228,13,0,FAILED_CONNECT,,,1159
20210811 04:20:22,ip-172-31-22-44.ec2.internal,,80.82.70.228,13,0,DISCONNECT,,,0
20210811 04:20:24,ip-172-31-22-44.ec2.internal,root,localhost,14,0,FAILED_CONNECT,,,1045
20210811 04:20:24,ip-172-31-22-44.ec2.internal,root,localhost,14,0,DISCONNECT,,,0
20210811 04:58:31,ip-172-31-22-44.ec2.internal,root,localhost,12,5,QUERY,,'SET GLOBAL server_audit_logging=ON',0
20210811 04:58:35,ip-172-31-22-44.ec2.internal,root,localhost,12,6,QUERY,,'show variables like \'%audit%\'',0
20210811 04:58:55,ip-172-31-22-44.ec2.internal,root,localhost,12,7,QUERY,,'SELECT * FROM information_schema.plugins WHERE plugin_name=\'server_audit\'',0
20210811 05:13:56,ip-172-31-22-44.ec2.internal,root,localhost,12,0,DISCONNECT,,,0
20210811 05:14:22,ip-172-31-22-44.ec2.internal,root,localhost,9,0,FAILED_CONNECT,,,1045
20210811 05:14:22,ip-172-31-22-44.ec2.internal,root,localhost,9,0,DISCONNECT,,,0
20210811 05:14:28,ip-172-31-22-44.ec2.internal,root,localhost,10,0,CONNECT,,,0
20210811 05:14:28,ip-172-31-22-44.ec2.internal,root,localhost,10,1,QUERY,,'select @@version_comment limit 1',0
20210811 05:14:31,ip-172-31-22-44.ec2.internal,root,localhost,10,2,QUERY,,'show variables like \'%audit%\'',0
20210811 05:14:33,ip-172-31-22-44.ec2.internal,root,localhost,10,3,QUERY,,'SELECT * FROM information_schema.plugins WHERE plugin_name=\'server_audit\'',0
20210811 05:15:36,ip-172-31-22-44.ec2.internal,root,localhost,10,0,DISCONNECT,,,0
20210811 05:18:32,ip-172-31-22-44.ec2.internal,root,localhost,11,0,CONNECT,,,0
20210811 05:18:32,ip-172-31-22-44.ec2.internal,root,localhost,11,5,QUERY,,'select @@version_comment limit 1',0
20210811 05:18:54,ip-172-31-22-44.ec2.internal,root,localhost,11,6,READ,mysql,user,
20210811 05:18:54,ip-172-31-22-44.ec2.internal,root,localhost,11,6,QUERY,mysql,'select user,host,password from mysql.user',0
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,user,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,db,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,tables_priv,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,columns_priv,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,procs_priv,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,proxies_priv,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,WRITE,mysql,roles_mapping,
20210811 05:19:19,ip-172-31-22-44.ec2.internal,root,localhost,11,7,QUERY,mysql,'CREATE USER \'app_1\' IDENTIFIED BY *****',0
20210811 05:19:22,ip-172-31-22-44.ec2.internal,root,localhost,11,0,DISCONNECT,,,0
[root@ip-172-31-22-44 data]#

Now we are going to set these parameters in my.cnf and GLOBALLY ... If we are doing like this , no need to restart the DB service.
---------------------------------------------------------------------

Let us consider , we have 4 app users like this ....

MariaDB [(none)]> CREATE DATABASE app_1_db;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> CREATE DATABASE app_2_db;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> CREATE DATABASE app_3_db;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> CREATE DATABASE app_4_db;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| app_1_db           |
| app_2_db           |
| app_3_db           |
| app_4_db           |
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| replication_gtid   |
+--------------------+
9 rows in set (0.003 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | password                                  |
+-------------+-----------+-------------------------------------------+
| root        | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm         | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1       | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
+-------------+-----------+-------------------------------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]>  CREATE USER 'app_2'@'%' IDENTIFIED BY 'app_2';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]>  CREATE USER 'app_3'@'%' IDENTIFIED BY 'app_3';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]>  CREATE USER 'app_4'@'%' IDENTIFIED BY 'app_4';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | password                                  |
+-------------+-----------+-------------------------------------------+
| root        | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm         | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1       | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
| app_2       | %         | *E82921ACFFAEC0F64BB68C8DE86238E16A216C62 |
| app_3       | %         | *949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6 |
| app_4       | %         | *7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF |
+-------------+-----------+-------------------------------------------+
9 rows in set (0.000 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON app_1_db.* to 'app_1'@'%';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON app_2_db.* to 'app_2'@'%';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON app_3_db.* to 'app_3'@'%';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON app_4_db.* to 'app_4'@'%';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show grants for 'app_1'@'%';
+--------------------------------------------------------------------
| Grants for app_1@%                                                
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'app_1'@'%' IDENTIFIED BY PASSWORD '*4CB5EB44F4964AA055F0C83C5B77875DCC0366AB' |
| GRANT ALL PRIVILEGES ON `app_1_db`.* TO 'app_1'@'%'                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'app_2'@'%';
+--------------------------------------------------------------------
| Grants for app_2@%                                                 
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'app_2'@'%' IDENTIFIED BY PASSWORD '*E82921ACFFAEC0F64BB68C8DE86238E16A216C62' |
| GRANT ALL PRIVILEGES ON `app_2_db`.* TO 'app_2'@'%'                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'app_3'@'%';
+--------------------------------------------------------------------
| Grants for app_3@%                                                 
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'app_3'@'%' IDENTIFIED BY PASSWORD '*949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6' |
| GRANT ALL PRIVILEGES ON `app_3_db`.* TO 'app_3'@'%'                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'app_4'@'%';
+--------------------------------------------------------------------
| Grants for app_4@%                                                 
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'app_4'@'%' IDENTIFIED BY PASSWORD '*7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF' |
| GRANT ALL PRIVILEGES ON `app_4_db`.* TO 'app_4'@'%'                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

[root@ip-172-31-22-44 ~]# vi /etc/my.cnf            =======>  In my.cnf file 

[root@ip-172-31-22-44 ~]# grep server_audit /etc/my.cnf
server_audit_events             = 'CONNECT,QUERY,DELETE,DROP'
server_audit_excl_users         = 'root,pmm,replication'
server_audit_file_rotate_now    = ON
server_audit_file_rotations     = 30
server_audit_incl_users         = 'app_1,app_2,app_3,app_4'
server_audit_logging            = ON
server_audit                    = FORCE_PLUS_PERMANENT  # do not allow users to uninstall plugin

[root@ip-172-31-22-44 ~]#

[root@ip-172-31-22-44 ~]# mysql -u root -p
Enter password: mariapassword 
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 13.Server version: 10.3.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL server_audit_events = 'CONNECT,QUERY,QUERY_DDL';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL server_audit_excl_users = 'root,pmm,replication';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL server_audit_file_rotate_now = ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL server_audit_file_rotations = 30;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL server_audit_incl_users = 'app_1,app_2,app_3,app_4';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SET GLOBAL server_audit_logging = ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show variables like '%server_audit%';
+-------------------------------+-------------------------+
| Variable_name                 | Value                   |
+-------------------------------+-------------------------+
| server_audit_events           | CONNECT,QUERY,QUERY_DDL |
| server_audit_excl_users       | root,pmm,replication    |
| server_audit_file_path        | server_audit.log        |
| server_audit_file_rotate_now  | OFF                     |
| server_audit_file_rotate_size | 1000000                 |
| server_audit_file_rotations   | 30                      |
| server_audit_incl_users       | app_1,app_2,app_3,app_4 |
| server_audit_logging          | ON                      |
| server_audit_mode             | 0                       |
| server_audit_output_type      | file                    |
| server_audit_query_log_limit  | 1024                    |
| server_audit_syslog_facility  | LOG_USER                |
| server_audit_syslog_ident     | mysql-server_auditing   |
| server_audit_syslog_info      |                         |
| server_audit_syslog_priority  | LOG_INFO                |
+-------------------------------+-------------------------+
15 rows in set (0.001 sec)


Now verify the server_audit plugin :-
----------------------------------------------

Before doing this activity i have created one super user in DB side like this...

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+------------------------------------------
| user        | host      | password                                  |
+-------------+-----------+------------------------------------------
| root        | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm         | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1       | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
| app_2       | %         | *E82921ACFFAEC0F64BB68C8DE86238E16A216C62 |
| app_3       | %         | *949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6 |
| app_4       | %         | *7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF |
+-------------+-----------+------------------------------------------
9 rows in set (0.000 sec)

MariaDB [(none)]> CREATE USER 'super_user_app'@'%' IDENTIFIED BY 'super_user_app';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'super_user_app' WITH GRANT OPTION;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+----------------+-----------+-------------------------------------------+
| user           | host      | password                                  |
+----------------+-----------+-------------------------------------------+
| root           | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root           | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root           | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm            | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication    | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1          | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
| app_2          | %         | *E82921ACFFAEC0F64BB68C8DE86238E16A216C62 |
| app_3          | %         | *949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6 |
| app_4          | %         | *7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF |
| super_user_app | %         | *D88BBEDB8BB804B1F1895B34C1EAE3653068CC82 |
+----------------+-----------+-------------------------------------------+
10 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'super_user_app'@'%';
+------------------------------------------------------------------------------------
| Grants for super_user_app@%                                                                                                              |
+------------------------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'super_user_app'@'%' IDENTIFIED BY PASSWORD '*D88BBEDB8BB804B1F1895B34C1EAE3653068CC82' WITH GRANT OPTION |
+------------------------------------------------------------------------------------
1 row in set (0.000 sec)

And i am providing one of the app_2 user WITH GRANT option to drop the users..
---------------------------------------------------------------------

MariaDB [(none)]> show grants for 'app_2'@'%';
+--------------------------------------------------------------------
| Grants for app_2@%                                                 
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'app_2'@'%' IDENTIFIED BY PASSWORD '*E82921ACFFAEC0F64BB68C8DE86238E16A216C62' |
| GRANT ALL PRIVILEGES ON `app_2_db`.* TO 'app_2'@'%'                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'app_2'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show grants for 'app_2'@'%';
+--------------------------------------------------------------------
| Grants for app_2@%                                                                 
+--------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'app_2'@'%' IDENTIFIED BY PASSWORD '*E82921ACFFAEC0F64BB68C8DE86238E16A216C62' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `app_2_db`.* TO 'app_2'@'%'                                
+--------------------------------------------------------------------
2 rows in set (0.000 sec)

So now app_user has WITH GRANT OPTION for all Databases...

Open another session :-
--------------------------

Now connect to the MariaDB Database from local client with app_2 user and do this activity ..

Here MariaDB server is running in Linux with 44.196.150.226 [ Public Ipaddress ] and 172.31.22.44 [ Private Ip address ]

Here Client is my Windows ..

Here my ipaddress from local browser is :-  116.75.124.246

Now i am trying to connecting from Windows client to MariaDB database in Linux 

C:\Program Files\MariaDB 10.4\bin>mysql -u app_2 -p -h 44.196.150.226
Enter password: *****
Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 19.erver version: 10.3.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| app_1_db           |
| app_2_db           |
| app_3_db           |
| app_4_db           |
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| replication_gtid   |
+--------------------+
9 rows in set (0.321 sec)

MariaDB [(none)]> select user(), database();
+----------------------+------------+
| user()               | database() |
+----------------------+------------+
| app_2@116.75.124.246 | NULL       |
+----------------------+------------+
1 row in set (0.246 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+----------------+-----------+---------------------------------------
| user           | host      | password                                  |
+----------------+-----------+---------------------------------------
| root           | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root           | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root           | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm            | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication    | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1          | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
| app_2          | %         | *E82921ACFFAEC0F64BB68C8DE86238E16A216C62 |
| app_3          | %         | *949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6 |
| app_4          | %         | *7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF |
| super_user_app | %         | *D88BBEDB8BB804B1F1895B34C1EAE3653068CC82 |
+----------------+-----------+---------------------------------------
10 rows in set (0.231 sec)

MariaDB [(none)]> DROP USER 'super_user_app'@'%';
Query OK, 0 rows affected (0.217 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+-------------+-----------+------------------------------------------
| user        | host      | password                                  |
+-------------+-----------+------------------------------------------
| root        | localhost | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | 127.0.0.1 | *84A39D41433A3044C57427018E7C05C80B363C48 |
| root        | ::1       | *84A39D41433A3044C57427018E7C05C80B363C48 |
| pmm         | %         | *E5AE1591B8643BFA224135CDDFD6EB8B303DC33E |
| replication | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| app_1       | %         | *4CB5EB44F4964AA055F0C83C5B77875DCC0366AB |
| app_2       | %         | *E82921ACFFAEC0F64BB68C8DE86238E16A216C62 |
| app_3       | %         | *949A2D33F53FE81DD0AA3B0DF1152F24FAB1FAC6 |
| app_4       | %         | *7B983A8C9430BF21CFB2760384D97FBEDFF5C0CF |
+-------------+-----------+------------------------------------------
9 rows in set (0.285 sec)

MariaDB [(none)]>

In server_audit.log file  :-
-----------------------------------------

[root@ip-172-31-22-44 ~]# cd /home/maria/data/

[root@ip-172-31-22-44 data]# pwd
/home/maria/data

[root@ip-172-31-22-44 data]# tail -f server_audit.log
20210811 06:28:22,ip-172-31-22-44.ec2.internal,app_2,localhost,16,67,QUERY,,'show databases',0
20210811 06:28:37,ip-172-31-22-44.ec2.internal,app_2,localhost,16,68,QUERY,mysql,'select user,host,password from mysql.user',0
20210811 06:28:53,ip-172-31-22-44.ec2.internal,app_2,localhost,16,69,QUERY,mysql,'DROP USER \'super_user_app\'@\'%\'',0
20210811 06:29:03,ip-172-31-22-44.ec2.internal,app_2,localhost,16,70,QUERY,mysql,'select user,host,password from mysql.user',0
20210811 06:30:55,ip-172-31-22-44.ec2.internal,app_2,localhost,16,0,DISCONNECT,,,0
20210811 06:37:46,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,17,0,CONNECT,,,0
20210811 06:37:46,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,17,72,QUERY,,'select @@version_comment limit 1',0
20210811 06:37:55,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,17,0,DISCONNECT,,,0
20210811 06:38:08,ip-172-31-22-44.ec2.internal,root,localhost,18,0,CONNECT,,,0
20210811 06:40:48,ip-172-31-22-44.ec2.internal,root,localhost,18,0,DISCONNECT,,,0


20210811 06:41:17,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,0,CONNECT,,,0
20210811 06:41:18,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,79,QUERY,,'select @@version_comment limit 1',0
20210811 06:41:22,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,80,QUERY,,'system date',1064
20210811 06:41:35,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,81,QUERY,,'show databases',0
20210811 06:41:47,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,82,QUERY,,'select user(), database()',0
20210811 06:42:02,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,83,QUERY,mysql,'select user,host,password from mysql.user',0
20210811 06:42:23,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,84,QUERY,mysql,'DROP USER \'super_user_app\'@\'%\'',0
20210811 06:42:31,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,85,QUERY,mysql,'select user,host,password from mysql.user',0
20210811 06:43:18,ip-172-31-22-44.ec2.internal,app_2,116.75.124.246,19,0,DISCONNECT,,,0

Observation :- Now we can see 116.75.124.246 ipaddress from server_audit.log file ....

Additional information :-
----------------------------------------

[root@ip-172-31-22-44 data]# ls -ltr
drwx------. 2 mysql root       4096 Jul 29 12:18 mysql
drwx------. 2 mysql mysql        20 Jul 29 12:18 performance_schema
-rw-rw----. 1 mysql mysql 536870912 Jul 29 12:20 ib_logfile1
-rw-rw----. 1 mysql mysql     24576 Jul 29 12:20 tc.log
-rw-rw----. 1 mysql mysql         0 Jul 29 12:20 multi-master.info
drwx------. 2 mysql mysql        20 Jul 29 13:01 replication
-rw-rw----. 1 mysql mysql       919 Jul 29 13:01 ON.000001
-rw-rw----. 1 mysql mysql       335 Jul 29 13:04 ON.000002
drwx------. 2 mysql mysql        20 Jul 29 13:08 replication_gtid
-rw-rw----. 1 mysql mysql       981 Jul 29 17:20 ON.000003
-rw-rw----. 1 mysql mysql       374 Jul 29 17:40 ON.000004
-rw-rw----. 1 mysql mysql       374 Jul 30 16:12 ON.000005
-rw-rw----. 1 mysql mysql       374 Aug  1 17:26 ON.000006
-rw-rw----. 1 mysql mysql       374 Aug  2 06:53 ON.000007
-rw-rw----. 1 mysql mysql       374 Aug  2 18:01 ON.000008
-rw-rw----. 1 mysql mysql       374 Aug  3 16:45 ON.000009
-rw-rw----. 1 mysql mysql       374 Aug  4 16:49 ON.000010
-rw-rw----. 1 mysql mysql       374 Aug  5 15:18 ON.000011
-rw-rw----. 1 mysql mysql       374 Aug  6 08:15 ON.000012
-rw-rw----. 1 mysql mysql       374 Aug  6 13:09 ON.000013
-rw-rw----. 1 mysql mysql       374 Aug  7 11:33 ON.000014
-rw-rw----. 1 mysql mysql       374 Aug  9 05:24 ON.000015
-rw-rw----. 1 mysql mysql       374 Aug 11 04:50 ON.000016
-rw-rw----. 1 mysql mysql       976 Aug 11 04:50 ib_buffer_pool
-rw-rw----. 1 mysql mysql  12582912 Aug 11 04:50 ibdata1
-rw-rw----. 1 mysql mysql        52 Aug 11 04:50 aria_log_control
-rw-rw----. 1 mysql mysql     16384 Aug 11 04:50 aria_log.00000001
-rw-rw----. 1 mysql mysql       351 Aug 11 04:50 ON.000017
-rw-rw----. 1 mysql mysql       216 Aug 11 05:14 ON.index
srwxrwxrwx. 1 mysql mysql         0 Aug 11 05:14 mysql.sock
-rw-rw----. 1 mysql mysql         5 Aug 11 05:14 ip-172-31-22-44.ec2.internal.pid
-rw-rw----. 1 mysql mysql  12582912 Aug 11 05:14 ibtmp1
-rw-rw----. 1 mysql mysql 536870912 Aug 11 05:14 ib_logfile0
drwx------. 2 mysql mysql        20 Aug 11 06:04 app_1_db
drwx------. 2 mysql mysql        20 Aug 11 06:04 app_2_db
drwx------. 2 mysql mysql        20 Aug 11 06:04 app_3_db
drwx------. 2 mysql mysql        20 Aug 11 06:04 app_4_db
-rw-rw----. 1 mysql mysql      9382 Aug 11 06:11 server_audit.log.1
-rw-rw----. 1 mysql mysql      4080 Aug 11 06:42 ON.000018
-rw-rw----. 1 mysql mysql      6715 Aug 11 06:43 server_audit.log
-rw-rw----. 1 mysql mysql    117784 Aug 11 06:43 mysqld.log

[root@ip-172-31-22-44 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /home/maria/data/mysql.sock
[mysqld]
server_id = 1
log_bin = ON
datadir=/home/maria/data
socket=/home/maria/data/mysql.sock
user=mysql
bind-address = 0.0.0.0
innodb_file_per_table=1
default_storage_engine=innodb
# enforce_innodb_engine=Innodb ##remove this parameter to avoid the error "ERROR 1286 (42000): Unknown storage engine 'partition'" when creating index
max_connections = 200
innodb_log_file_size=512M
innodb_buffer_pool_size = 256M
sync_binlog = 1
log_slave_updates=1
query_cache_type = 0
query_cache_size = 0
lower_case_table_names = 1
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_520_ci
innodb_flush_log_at_trx_commit=1
gtid-domain-id=22

server_audit_events             = 'CONNECT,QUERY,DELETE,DROP'
server_audit_excl_users         = 'root,pmm,replication'
server_audit_file_rotate_now    = ON
server_audit_file_rotations     = 30
server_audit_incl_users         = 'app_1,app_2,app_3'
server_audit_logging            = ON
server_audit                    = FORCE_PLUS_PERMANENT  # do not allow users to uninstall plugin
innodb_monitor_enable = all
performance_schema = ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/home/maria/data/mysqld.log
pid-file=/home/maria/data/mysqld.pid



Reference Link :-
------------------
https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

 PostgreSQL to MySQL  ------------------------ This Document will give details about PostgreSQL to MySQL Database . Environment Details :- --------------------------- Server 1 == Source Database == PostgreSQL Database Version == 13.4 Server 2 == Target Database == MySQL Database Version == 8.0.20 Server 3 == Jump host == Where you will install Sqline and connect to Source DB and Target DB. I have consider 3 EC2 Machines from AWS [ RHEL 8.x ]  postgres-source-server == 172.31.30.141 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 mysql-target-server    == 172.31.17.136 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 jump-host              == 172.31.27.241 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 Step - 1  Install PostgreSQL Database Version in Source Server  Step - 2  Install MySQL Database Verion in Target Server  Step - 3  Install / Setup sqline s/w in Jump host server  Step - 4  Loading ...
Read more

MariaDB Database Multi-instance implementation in single machine

--------------------------------------------------------------------- MariaDB Database Multi-instance implementation in single machine --------------------------------------------------------------------- Step - 1 : Install MaariaDB [ First ] instance by using YUM or Repo installation  Step - 2 : Setup for other port numbers [ 3307 , 3308 and 3309 ] Instances for MariaDB  Step - 3 : Start the MariaDB service with new DB instances [ 3307 , 3308 and 3309 ]  Step - 4 : Stop / Shutdown of MariaDB all Instances [ 3306 , 3307 ,3308 and 3309 ] Step - 5 : Connection for all MariaDB instances [ 3306 , 3307 ,3308 and 3309 ] Step - 6 : Setting the password for DB super user for all MariaDB instances [ 3306 , 3307 ,3308 and 3309 ] Step - 7 : MariaDB services creation for all instances [ 3306 , 3307 ,3308 and 3309 ] Step - 8 : Stop / Start / Status / Restart for all MariaDB instances by uising mariadb instance service files -------------------------------------------------------------...
Read more