Configuring Audit Logging

Warning

As of the time of this publication, audit logging features are made available and licensed only as part of the MemSQL Advanced Security Option. Before using or implementing this functionality, please consult with your enterprise’s licensing administrator to confirm that your enterprise has purchased the necessary Advanced Security Option license from MemSQL.

Before you can enable audit logging, you must configure each MemSQL node’s memsql.cnf file. There are four configuration variables associated with audit logging:

  • auditlog_level (Required)
  • auditlog_rotation_size (Required if logging is enabled)
  • auditlog_rotation_time (Required if logging is enabled)
  • auditlogsdir (Required if logging is enabled)

Each of these variables have a default setting when MemSQL is first installed. You can show the default settings by executing the following statement:

memsql> SHOW GLOBAL VARIABLES LIKE 'audit%';
+------------------------+-----------------------------------------+
| Variable_name          | Value                                   |
+------------------------+-----------------------------------------+
| auditlog_level         | OFF                                     |
| auditlog_rotation_size | 134217728                               |
| auditlog_rotation_time | 3600                                    |
| auditlogsdir           | /var/lib/memsql/master-3306-1/auditlogs |
+------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

These variables are described in the table below:

Variable Name Value
auditlog_level Specifies the audit logging level. By default, this value is set to OFF. Ten other levels are available — see Logging Levels for more information.
auditlog_rotation_size Specifies the maximum size per log file in bytes. By default, this value is set to 128MB, or 134217728 bytes.
auditlog_rotation_time Specifies the maximum time duration to write to a single log file in seconds. By default, this value is set to 1 hour, or 3600 seconds.
auditlogsdir Specifies the local or network directory to write log files. By default, this value is set to the auditlogs directory in your node’s MemSQL directory. For example: /var/lib/memsql/<node-type>-<port>/auditlogs

Before enabling audit logging, ensure that the default configuration settings are acceptable. Namely, validate the settings for the following system variables:

  • auditlog_rotation_size
  • auditlog_rotation_time
  • auditlogsdir

These settings should be set to acceptable values before enabling logging. While they can be changed later, these settings are loaded only upon MemSQL startup from each node’s memsql.cnf file. Therefore, whenever a change is made to any audit logging setting (including auditlog_level), each node in your cluster must be restarted to apply the new configuration.

How to Enable and Configure Audit Logging

Audit logging variables are set in the memsql.cnf file in each node’s MemSQL path. By default, this path is /var/lib/memsql/<node-type>-<port>, or /var/lib/memsql/master-3306 for a typical master aggregator node.

Info

Always ensure that each node in your cluster has been stopped before making audit logging configuration changes.

  1. Open a new console window with access to the node you want to configure.

  2. Stop any MemSQL processes on the node. There are two common ways to stop MemSQL: a. sudo <memsql-node-path>/service stop; — This command runs the service executable that wraps common node operations. b. pgrep memsql | xargs kill -9 — This command kills all MemSQL processes on the current node.

  3. After the node has been stopped, navigate to the memsql.cnf path for the node and open the file with a text editor.

  4. Add the four required audit logging variables. For example, consider the following sample configuration:

    max-pooled-connections  = 100
    max-connection-threads = 256
    default-partitions-per-leaf = 8
    max_subselect_aggregator_rowcount = 0
    allow_user_functions
    
    auditlog_level = ADMIN-ONLY
    auditlog_rotation_size = 134217728
    auditlog_rotation_time = 3600
    auditlogsdir = /var/lib/memsql/master-3306-1/auditlogs
    
  5. When your configuration is complete, save the memsql.cnf file and exit the text editor.

    Warning

    Repeat the configuration update process for each node in your cluster before continuing.

  6. Restart the node in one of two ways: a. sudo <memsql-node-path>/service start; b. sudo ./<memsql-node-path>/memsqld

  7. Ensure that MemSQL starts successfully. Once started, validate that your settings have been loaded successfully by executing the following command:

    memsql> SHOW GLOBAL VARIABLES LIKE 'audit%'
    +------------------------+-----------------------------------------
    | Variable_name          | Value                                   
    +------------------------+-----------------------------------------
    | auditlog_level         | ADMIN-ONLY                              
    | auditlog_rotation_size | 134217728                               
    | auditlog_rotation_time | 3600                                    
    | auditlogsdir           | /var/lib/memsql/master-3306-1/auditlogs
    +------------------------+-----------------------------------------
    4 rows in set (0.00 sec)
    

Once each node in your cluster has been updated with the new configuration changes, audit logging has been successfully configured and enabled.

Was this article useful?