24DatabaseAuditing

Database Auditing

Outline

Concepts
Auditing Categories
Database Auditing in MySQL

Concepts

Database auditing involves observing a database so as to be aware of the actions of database users
Database administrators and consultants often set up auditing for security purposes, for example, to ensure that those without the permission to access information do not access it
Database Activity Monitoring (DAM) is a database security technology for monitoring and analyzing database activity that operates independently of the database management system (DBMS) and does not rely on any form of native (DBMS-resident) auditing or native logs such as trace or transaction logs.

Concepts

Database Activity Monitoring is typically performed continuously and in real-time
An audit trail also called audit log is an outcome of Database Activity Monitoring
An Audit trail a security-relevant chronological sequence of records, that provide documentary evidence of the sequence of activities, that have affected at any time a specific operation, procedure or event

Database Auditing

Auditing Categories

Audit logon/logoff into a database
- Record two events: an event for sign-on and an event for sign-off
- Save login name, timestamps, TCP/IP address of client, and a program used to initiate connection
- Record failed login attempts
Audit sources of database usage
- It includes which network node a user is connected to (IP address and host name)
- Which application is being used to access a database

The next group of auditing actions, includes the database activities outside normal operating hours. Activities performed outside normal operating hours are usually suspected. We may suspect, that such activities could be the result of unauthorised access to modify data. Audit records of off-hours activities, include logons logoffs and all S Q L activities. Audit off-hours activities do not need to include the activities, that are always scheduled to run: off-hours, for example, extract, transform and load data warehousing activities.

The next group of auditing actions, includes auditing of data definition activities like: processing of data definition statements: CREATE, DROP and ALTER. Data definition statement of S Q L are potentially the most damaging statements, that exist. Auditing data definition activities, is also done to eliminate errors of developers and database administrators.

Auditing database schemas changes, can be done in several different ways. The first one, uses the database native audit features. The next one, uses the external auditing system. The next one, uses the comparison of schema snapshots. Finally the last, allows for using database triggers.

Auditing Categories

Audit database activities outside normal operation hours
- Activities performed outside normal operating hours are usually suspect and maybe the result of unauthorized access to modify data
- Audit off-hours activities include logons/logoffs and all SQL activities
- Audit off-hours activities do not need to include the activities that are always scheduled to run off-hours. for example Extract, Transform, Load (ETL) data warehousing activities
Audit DDL activities
- DDL commands of SQL are potentially the most damaging commands that exists
- Auditing DDL activities is also done to eliminate errors of developers and database administrators
- Auditing database schemas changes can be done by: (1) using database audit features, (2) using external auditing system, (3) comparison of schema snapshots, (4) some system allow for using database triggers

The next group of auditing actions, includes the audits of database errors. Auditing database errors is important, because in many cases hackers use a technique of "trial-and error, to investigate a structure of a database. On the other hand, a well written and well tested database application does not return the errors. Failed logins, is a good example of errors that must be monitored. Audit of database errors may also lead to identification of the weak points in the database applications.

The next group of auditing actions, includes the audits of changes to the source code of stored procedures and triggers. Changes to the sources of already developed software, may mean the attempts to incorporate malicious code. The first option, is to compare source code developed earlier with the present one, for example, using: diff program. The second option, is to use an external database security and auditing system, that alerts on any create or alter statement used by a database user. The third option, is to use built in database features, for example ability of a system to trace the “recompile” events to track changes to stored procedure and triggers.

Auditing Categories

Audit database errors
- Auditing database errors is important because in many cases hackers use a technique of “trial-and error” to investigate a structure of a database, a well written and tested database application does not return errors
- Failed logins is a good example of errors that must be monitored
- Audit database errors may also lead to identification of weak points in database applications
Audit changes to sources of stored procedures and triggers
- Change to sources of already developed software may mean the attempts to incorporate malicious code
- Audit can be implemented by comparison of source code developed earlier with the present one, for example using diff program
- The second option is to use an external database security and auditing system, which alerts on any modify or create command used by a database user
- The third option is to use built in database features, for example ability of a system to trace “recompile” events to track changes to stored procedure and triggers

Auditing Categories

Audit changes to privileges, user logins and other security attributes
- Audit deletion and addition of users, logins and role
- Audit changes to the mapping between logins and users/roles
- Audit privilege changes over user and roles
- Audit password changes
- Audit changes to security attributes at a server, database, statement, or object level

The next group of actions, that should be audited, includes the creation, changes, usage of database links and replication in distributed database systems. A simple solution is to record the daily differences. We compare everyday snapshots and we also use the internal database mechanisms to compare data.

The next group of actions, that should be audited includes: the changes to the sensitive data, like for example audit trails, that enforce accuracy in financial data. It includes fully recording “old” and “new” values for each DML activity. However, due to the large number of data manipulations, the audits must be done very selectively. It is why we have to very carefully choose data objects to be audited. To create the audit trails we can use the database system capabilities, external audit systems or database triggers.

Auditing Categories

Audit creation, changes and usage of database links and replication
- Simple implementations using daily differences
- Comparing snapshots
- Using database internal mechanisms
Audit changes to sensitive data (data change audit trails to enforce accuracy in financial data)
- Fully recording “old” and “new” values for each DML activity
- Due to the large number of DML operations the audits must be done very selectively
- Carefully chose data objects to be audited
- Use database system capabilities, external audit systems, or database triggers

The next group of actions, that should be audited, includes the SELECT statements accessing privacy sets, for example, medical records. The group includes recording information where the SELECT statements come from. And the names of user who retrieved the data. And what data was actually retrieved.

First, we try to identify so called privacy sets. A privacy set determines, which associations of data are really important. Implementation of such audit, uses database traces and external auditing systems.

We also must audit any changes made to the definitions of what to audit. It includes audit changes made to the definitions of audit trails and changes to the audit trails themselves. To implement it, we use built-in database features and external auditing systems.

Auditing Categories

Audit SELECT statements for privacy sets
- Record where SELECT statements come from
- Who (username) retrieved the data
- What data was actually retrieved
- Identify so called privacy sets, for example which associations of data are really important
- Implementation uses database traces and external auditing systems
Audit any changes made to the definitions of what to audit
- Audit changes made to the definitions of audit trails and changes to the audit trails themselves
- Use built-in database features and external auditing systems

Database Auditing

Outline

Concepts
Auditing Categories
Database Auditing in MySQL

Database Auditing in MySQL

MySQL Enterprise Audit (plugin not available within Community Edition)
- Dynamically enable/disable audit stream
- Implement policies that log all or selected login or query activities
- Automatically rotate audit log files based on size
- Integrate XML-based audit log stream with MySQL, Oracle and other third party solutions
Using stored procedures
Using database triggers
External audit plugin libraries
Analysis of database logs

Now, we explain the ways how to use the database logs, for the audits of database activities.

Every database system records in the logs a lot of information about the details of data processing. For example, MySQL Server has several logs, that contain information about the user activities. The contents of such log provides a lot of audit related information and it is information obtained completely "free" without increasing a database workload. This is because a database system must record information about the details of data processing because of many other reasons.

MySQL writes to the following logs information, that can be used for the audit purposes.

The error log, contains information about the problems encountered while starting, running, or stopping MySQL server.

The general query log, contains information about the established client connections and the statements received from the clients.

The binary log, contains information about the statements, that change data and that are also used for replication.

The relay log contains information about the data changes received from a replication master server.

The slow query log, contains information about the queries, that took more than a given period of time to process.

The DDL log also called: metadata log, contains information about the metadata operations, performed by the data definition language statements.

Database Auditing in MySQL

Using database logs
- MySQL Server has several logs that contain information about the user activities
- Error log: problems encountered starting, running, or stopping MySQL server
- General query log: Established client connections and statements received from clients
- Binary log: Statements that change data (also used for replication)
- Relay log: Data changes received from a replication master server
- Slow query log: Queries that took more than a given period of time to execute
- DDL log (metadata log): Metadata operations performed by DDL statements

Database Auditing in MySQL

Error log
- Error log contains information indicating when the database server was started and stopped and also any critical errors that occurred while the server was running
- A system variable log_error determines a location a file error.log with information included in error log

Database Auditing in MySQL

Error log

It is possible to list the contents of a file error.log in the following way


cat /var/log/mysql/error.log | more


151110 14:44:15 mysqld_safe Starting mysqld daemon with
			databases from /var/lib/mysql
2015-11-10T03:44:15.916141Z 0 [Warning] Changed limits: max_open_files: 1024 
(requested 5000)
2015-11-10T03:44:15.916275Z 0 [Warning] Changed limits: table_open_cache: 431 
(requested 2000)
2015-11-10T03:44:16.831947Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and
 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. 
They will be merged with strict mode in a future release.
2015-11-10T03:44:16.832003Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2015-11-10T03:44:16.837216Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.9) starting as 
process 22405 .
…             …             …              …             …

Database Auditing in MySQL

General query log

A system variable general_log controls logging to a general query log


show variable like 'general_log';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

To start logging execute the following statement


set global general_log='ON';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+

Database Auditing in MySQL

General query log

General query log is written either to a file or to a relational table
A variable log_output determines whether general query log is written either to a file or to a relational table

show variables like 'log_output';

	      
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

A name of file for a general query log is determined by a variable general_log_file

show variables like 'general_log_file';

	     
+------------------+---------------------------------------+
| Variable_name    | Value                                 |
+------------------+---------------------------------------+
| general_log_file | /var/lib/mysql/csit115-VirtualBox.log |
+------------------+---------------------------------------+

Database Auditing in MySQL

General query log

To redirect a general query log to a relational table we change a value of variable log_output in the following way:

set global log_output='TABLE';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

General query query log is recorded in a relational table mysql.general_log

DESCRIBE mysql.general_log;

+--------------+---------------------+------+-----+----------------------+--------------------------------+
| Field        | Type                | Null | Key | Default              | Extra                          |
+--------------+---------------------+------+-----+----------------------+--------------------------------+
| event_time   | timestamp(6)        | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| user_host    | mediumtext          | NO   |     | NULL                 |                                |
| thread_id    | bigint(21) unsigned | NO   |     | NULL                 |                                |
| server_id    | int(10) unsigned    | NO   |     | NULL                 |                                |
| command_type | varchar(64)         | NO   |     | NULL                 |                                |
| argument     | mediumblob          | NO   |     | NULL                 |                                |
+--------------+---------------------+------+-----+----------------------+--------------------------------+

Database Auditing in MySQL

General query log
To remove old contents of a general query log execute the following TRUNCATE TABLE statement
TRUNCATE TABLE statement is surprisingly categorized as DDL statement and it removes the contents of a relational table forever
It means that TRUNCATE TABLE statement cannot be reversed with ROLLBACK statement

To list the contents of the general query log, we use SELECT statement. Please, see SELECT statement and the abbreviated results, listed after processing of the statement.

A column: event time, contains a timestamp recorded with a precision up to a single milisecond of a moment, when S Q L statement has been submitted for processing.

A column: user host, contains a name of a user and a name of a host from where the statement originated.

The columns: thread id and server id, contain the identifiers of a thread, that processed the statement and identifier of server, that processed the statement.

A column: command type, contains a string: Query, that indicates the processing of SELECT statement.

Finally a command: argument, contains a text of S Q L statement processed.

Database Auditing in MySQL

To find the contents of a general query log we use the following SELECT statement

 
SELECT * FROM mysql.general_log;

 
+----------------------------+---------------------------+-----------+-----------+...
| event_time                 | user_host                 | thread_id | server_id |
+----------------------------+---------------------------+-----------+-----------+...
| 2017-05-08 14:14:45.546765 | root[root] @ localhost [] |         3 |         0 | 
+----------------------------+---------------------------+-----------+-----------+...

                                                   ...--------------+---------------------------------+
                                                   ... command_type |  argument                       |
                                                   ...--------------+---------------------------------+
                                                   ... Query        | select * from mysql.general_log |
                                                   ...--------------+---------------------------------+

Database Auditing in MySQL

General query log
- Note, that when MySQL server is shutdown and restarted all values of system variables return to its default values or values set up in the system configuration file
- It means that after shutdown and restart the values of variables like general_log, log_output, general_log_file return to their original values
- To stop writing into a general query log execute a statement

The slow query log contains information about S Q L statements, that took more than a given period of time to process and required at least a given number of rows to be processed.

The threshold for a period of time to be considered as: too long, is stored in a system variable: long query time.

The system variable: long query time, contains the total number of seconds after which, the processing of the S Q L statement is recorded in the slow query log.

The threshold for the total number of statements to be processed, is stored in a system variable: min examined row limit.

The variable: min examined row limit, contains the total number of processed rows after which, processing of S Q L statement is recorded in the slow query log.

The minimum and default values of: long query time and min examined row limit, are respectively: 0 and 10.

A statement at the bottom of the present slide, shows a default value of a system variable: long query time.

Database Auditing in MySQL

Slow query log
- The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined
- The minimum and default values of long_query_time are 0 and 10, respectively

Database Auditing in MySQL

Slow query log


show varfiables like min_examined_row_limit;


+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+

The value can be specified to a resolution of microseconds
For logging to a file, times are written including the microseconds part
For logging to tables, only integer times are written; the microseconds part is ignored

Database Auditing in MySQL

Slow query log

To change a value of long query time use the following statement


SET long_query_time = 0
SHOW VARIABLES LIKE 'long_query_time';


+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+

Database Auditing in MySQL

Slow query log

A system variable slow_query_log controls logging to a slow query log


show variables like 'slow_query_log';


+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

Database Auditing in MySQL

Slow query log

To start logging execute the following statement


set global slow_query_log='ON'


show variables like 'slow_query_log';


+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

Database Auditing in MySQL

Slow query log
- Slow query log is written either to a file or to a relational table
- A variable log_output determines whether general a query log is written either to a file or to a relational table

Database Auditing in MySQL

Slow query log

A name of file for a slow_query_log is determined by a variable slow_query_log_file


show variables like 'slow_query_log_file'


+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| slow_query_log_file | /var/lib/mysql/csit115-VirtualBox-slow.log |
+---------------------+--------------------------------------------+

Database Auditing in MySQL

Slow query log

To redirect slow query log to a relational table we change a value of the variable log_output in the following way:


set global log_output='TABLE'


+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

Database Auditing in MySQL

Slow query log

A slow query log is recorded in a relational table mysql.slow_log


DESCRIBE mysql.slow_log


+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field          | Type                | Null | Key | Default              | Extra                          |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| start_time     | timestamp(6)        | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| user_host      | mediumtext          | NO   |     | NULL                 |                                |
| query_time     | time(6)             | NO   |     | NULL                 |                                |
| lock_time      | time(6)             | NO   |     | NULL                 |                                |
| rows_sent      | int(11)             | NO   |     | NULL                 |                                |
| rows_examined  | int(11)             | NO   |     | NULL                 |                                |
| db             | varchar(512)        | NO   |     | NULL                 |                                |
| last_insert_id | int(11)             | NO   |     | NULL                 |                                |
| insert_id      | int(11)             | NO   |     | NULL                 |                                |
| server_id      | int(10) unsigned    | NO   |     | NULL                 |                                |
| sql_text       | mediumblob          | NO   |     | NULL                 |                                |
| thread_id      | bigint(21) unsigned | NO   |     | NULL                 |                                |
+----------------+---------------------+------+-----+----------------------+--------------------------------+

Database Auditing in MySQL

Slow query log

To remove the old contents of a slow query log we use the following TRUNCATE TABLE statement

TRUNCATE TABLE mysql.slow_log;

TRUNCATE TABLE statement is surprisingly categorized as a DDL statement and it removes the contents of a relational table forever
It means that TRUNCATE TABLE statement cannot be reversed with ROLLBACK statement
To find the contents of some columns from a slow log execute SELECT statement

SELECT query_time, sql_text FROM mysql.slow_log

 
+-----------------+-------------------------------------------------+
| query_time      | sql_text                                        |
+-----------------+-------------------------------------------------+
| 00:00:00.034088 | truncate table mysql.slow_log                   |
| 00:00:00.000870 | select * from mysql.slow_log                    |
| 00:00:00.000914 | select query_time, sql_text from mysql.slow_log |
+-----------------+-------------------------------------------------+

Database Auditing in MySQL

Slow query log
- Note, that when MySQL server is shutdown and restarted all values of the system variables return to their default values or values set up in the system configuration file
- It means that after shutdown and restart the values of variables like long_query_time, min_examined_row_limit, slow_query_log, log_output, slow_query_log_file return to their original values
- To stop writing into slow query log execute a statement

The binary log, contains information about the events, that describe the database changes such as: table creation operations or changes to table data.

It also contains information about, the events for the statements, that potentially could have made changes, for example, a DELETE statement, that matched no rows.

The binary log, also contains information about, how long it took to process the UPDATE statements.

The binary log, has two important purposes:

First, it is used for replication.

The binary log, on a master replication server, provides a record of the data changes to be sent to the slave servers.

Second, it is used for the data recovery operations.

The binary log is not used for the statements: such as SELECT or SHOW, that do not modify data.

To log all statements, for example, to identify a problem query, we should use the general query log.

Database Auditing in MySQL

Binary log
- A binary log contains “events” that describe database changes such as table creation operations or changes to table data
- It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows)
- Binary log also contains information about how long took the computations of UPDATE statements
- Binary log has two important purposes:
  - it is used for replication; the binary log on a master replication server provides a record of the data changes to be sent to slave servers
  - it is used for data recovery operations
- Binary log is not used for statements such as SELECT or SHOW that do not modify data
- To log all statements (for example, to identify a problem query), we use a general query log

Database Auditing in MySQL

DDL log
- A DDL log, or metadata log, records metadata operations generated by data definition statements such as CREATE TABLE,DROP TABLE, and ALTER TABLE
- MySQL uses this log to recover from crashes occurring in the middle of a metadata operation
- When executing the statement DROP TABLE t1, t2, we need to ensure that both t1 and t2 are dropped, and that each table drop is complete

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapter 12, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
MySQL 5.7 Reference Manual, MySQL Server Administration, MySQL Server logs