Auditing

Exasol allows you to have auditing for sessions and SQL command execution. The auditing doesn't require any transaction log or any database schema modification. Therefore, it doesn't have any performance impact on the system.

Exasol's auditing captures the following:

  • All sessions with information such as driver, client, host, and operating system user.
  • All executed SQL statements with information such as CPU, network, and hard disk read/write.

Capturing the above information helps in debugging and data security. For example, looking at the audit logs, you can check which query used a high amount of temp DB RAM at any time. Or you can see which user dropped a table at what time.

Implementation

  • You can enable the auditing from EXAoperation (click the Auditing check box) while creating a database. To know more about it, see Create the Database. If you have not enabled the auditing for a database, you can still do it from Edit a Database.
  • The audit information is logged into the following system tables:
    • EXA_DBA_AUDIT_SESSIONS: The system table stores all the sessions from the moment you enable it and start the database.
    • EXA_DBA_AUDIT_SQL: The system table stores all executed SQL statements from the moment you enable it and start the database.
    • EXA_DBA_AUDIT_IMPERSONATION: The system table stores all of the impersonation commands that each session executes from the moment you enable it and start the database.
  • The system tables are accessible to a user with SELECT ANY DICTIONARY system privilege.
  • You can view the size of the auditing data in EXA_STATISTICS_OBJECT_SIZES.
  • You can delete the audit logs by using TRUNCATE AUDIT LOGS command.

Examples

Auditing for a user

Here is an example of auditing all sessions of user JOHN in the last 24 hours.

SQL Statement

SELECT SESSION_ID, USER_NAME, LOGIN_TIME, LOGOUT_TIME 
FROM EXA_DBA_AUDIT_SESSIONS
WHERE USER_NAME = 'JOHN' 
  AND CLIENT NOT LIKE '%[Meta]%' 
  AND add_days(LOGIN_TIME, 1) > SYSDATE;

Output

SESSION_ID		USER_NAME	LOGIN_TIME		LOGOUT_TIME
1460639103178893350	JOHN		2019-11-20 11:11:18.221	 2019-11-20 12:00:10.226
1460641579837871138	JOHN		2019-11-21 09:23:18.141	 2019-11-21 10:20:28.240
1460639332787506215	JOHN		2019-11-22 05:33:18.229	 2019-11-22 07:05:10.210

Auditing for a session

Here is an example of auditing a session 1460639103178893350.

SQL Statement

SELECT STMT_ID, START_TIME, STOP_TIME, SUCCESS, SQL_TEXT
FROM EXA_DBA_AUDIT_SQL
WHERE SESSION_ID = 1460639103178893350 
  AND STMT_ID > 6
ORDER BY 1;

Output

STMT_ID	START_TIME	STOP_TIME	SUCCESS	SQL_TEXT
7	52:54.5		52:54.6		TRUE	create schema JOHN; 
8	58:47.8		58:47.8		TRUE	create table tmp_customers …
9	58:55.5		58:55.6		TRUE	create view … 
10	59:42.5		59:42.5		TRUE	commit; 
11	59:50.7		59:50.8		FALSE	IMPORT INTO tmp_customers …
12	02:51.4		02:51.5		TRUE	rollback; 

Delete auditing log

Here is an example of deleting the audit logs before a specified date.

SQL Statement

TRUNCATE AUDIT LOGS KEEP FROM '2019-01-01';
-- deletes all auditing information before the specified date.

Best Practices

Here are some best practices to ensure the data security and better auditing:

  • Enable auditing for your database.
  • Use FLUSH STATISTICS statement to update the statistical tables on demand.
  • Always implement a user concept that distinguishes between regular users and administrators.
  • Design and develop a comprehensive rights management system.
  • Grant permission to a user only if it is required.
  • Limit and secure any third-party access to the system.
  • Separate data and views so that users may only have access to the views.