Audit creation and deletion of users for SOX evidences

Preamble

New episode in our Sarbanes–Oxley Act (SOX) journey, the audit company want us to track any user creation and deletion that are appearing disappearing in our databases.

My testing has been done with Oracle enterprise edition 12cR1 (12.1.0.2) in a container environment and with only one pluggable database.

It would be exactly the same in a non-CDB (container database) environment but as this architecture is deprecated I force myself to use PDB (pluggable database) architecture. it also means that legacy audit chapter apply to pre-12cR1 releases as well.

Virtual machine is running Oracle Linux Server release 7.3 64 bits.

The Oracle legacy audit chapter is also valid for any database pre-12cR1…

I thought I would write a post only on Oracle but I came to know that one of our few SQL Server database is unfortunately in SOX perimeter. So had to investigate a bit on SQL Server that I know mush much less than Oracle…

To complete the article I have finally add a part on MySQL 5.7.17 64 bits to try to get whole perimeter on database flavors on which I work on…

Oracle

Legacy audit

First thing to check is to know if unified auditing is activated or not on your database with:

SQL> SELECT VALUE FROM v$option WHERE parameter='Unified Auditing';
 
VALUE
----------------------------------------------------------------
FALSE

By default you should be in this mode that is called mixed mode auditing where you have both access to legacy audit and new unified auditing. If the above query answer true or if you activate unified auditing then only new unified auditing feature is usable and legacy audit is no more accessible.

Let say for now you have default mode and you don’t want to change it right now, see next chapter. This would also be the method to use for pre-12cR1 databases. I just set static parameter AUDIT_TRAIL to db value:

SQL> show parameter AUDIT
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 BOOLEAN     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         INTEGER     1048576

Activate audit for create and drop user commands in my PDB1 pluggable database with:

SQL> AUDIT CREATE USER;
 
AUDIT succeeded.
 
SQL> AUDIT DROP USER;
 
AUDIT succeeded.

You can confirm it has been activated with:

SQL> col user_name FOR a20
SQL> col proxy_name FOR a20
SQL> SET lines 150
SQL> SELECT * FROM dba_priv_audit_opts;
 
USER_NAME            PROXY_NAME           PRIVILEGE                                SUCCESS    FAILURE
-------------------- -------------------- ---------------------------------------- ---------- ----------
                                          DROP USER                                BY ACCESS  BY ACCESS
                                          CREATE USER                              BY ACCESS  BY ACCESS

If I connect with SYSTEM (or any DBA account) and I execute below drop and create user commands:

SQL> CREATE USER test1 IDENTIFIED BY "secure_password";
 
USER created.
 
SQL> DROP USER test1 CASCADE;
 
USER dropped.

I get below audit records:

SQL> col obj_name FOR a15
SQL> col username FOR a15
SQL> SELECT username,TO_CHAR(TIMESTAMP,'dd-mon-yyyy hh24:mi:ss') AS TIMESTAMP,action_name,obj_name FROM dba_audit_trail;
 
USERNAME        TIMESTAMP                     ACTION_NAME                  OBJ_NAME
--------------- ----------------------------- ---------------------------- ---------------
SYSTEM          16-feb-2017 12:56:51          CREATE USER                  TEST1
SYSTEM          16-feb-2017 12:56:53          DROP USER                    TEST1

But if you do it with the usual account you would use in such situation i.e. SYS account then audit trail remains empty… This has been a concern until Oracle 10gR1 where Oracle corporation has thought to a solution to audit SYS operation. Of course this will not be stored in SYS.AUD$ table as SYS could delete its own entries from this table. SYS audit records are stored as flat file in a filesystem to let an option to secure them with root Unix account ! This is well explained in MOS note Audit SYS User Operations (How to Audit SYSDBA) (Doc ID 174340.1).

By default AUDIT_SYS_OPERATIONS static initialization parameter is set to true. If you are in a PDB environment then the audit directory of your PDB is a directory inside AUDIT_FILE_DEST. For a non-CDB or the CDB itself then it is simply AUDIT_FILE_DEST directory. The directory name is the GUID of your PDB that you can find with something like:

SQL> SELECT guid,name FROM v$pdbs;
 
GUID                             NAME
-------------------------------- ------------------------------
46C0B24DE8A122B2E0536538A8C0CEB9 PDB$SEED
46C1CED77CF16416E0536538A8C0C22F PDB1

Looking in this directory (AUDIT_FILE_DEST/46C1CED77CF16416E0536538A8C0C22F) I have found a file containing (notice the hidden password):

.
.
Thu Feb 16 14:34:23 2017 +01:00
LENGTH : '189'
ACTION :[33] 'create user test2 identified by *'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '3764800561'
 
Thu Feb 16 14:34:34 2017 +01:00
LENGTH : '216'
ACTION :[60] 'BEGIN dbms_cmp_int.drop_cmp_by_cmpid(:sb1, :sb2, :sb3); END;'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '3764800561'
 
Thu Feb 16 14:34:34 2017 +01:00
LENGTH : '179'
ACTION :[23] 'drop user test2 cascade'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '3764800561'
.
.

If AUDIT_SYS_OPERATIONS is set to FALSE then SYS operations are really hidden from all !

Unified audit

The new unified auditing feature seems to be available for free even if, as usual, it is very complex to have a definitive clear answer with Oracle corporation. Follow MOS note How To Enable The New Unified Auditing In 12c ? (Doc ID 1567006.1) and you should get something like:

SQL> SELECT VALUE FROM v$option WHERE parameter='Unified Auditing';
 
VALUE
----------------------------------------------------------------
TRUE

What can I audit ?

SQL> SET lines 150 pages 1000
SQL> col component FOR a10
SQL> col name FOR a30
SQL> SELECT * FROM auditable_system_actions WHERE component='Standard' AND name LIKE '%USER%';
 
      TYPE COMPONENT      ACTION NAME
---------- ---------- ---------- ------------------------------
         4 Standard           43 ALTER USER
         4 Standard           51 CREATE USER
         4 Standard           53 DROP USER
         4 Standard          197 PURGE USER RECYCLEBIN

I create an audit policy to audit creation and deletion of users, for fun I add a small comment on it:

SQL> CREATE AUDIT policy create_drop_user_pol
     PRIVILEGES CREATE USER, DROP USER
     container = CURRENT;
 
AUDIT policy created.
 
SQL> COMMENT ON AUDIT policy create_drop_user_pol IS 'Audit policy for create and drop user commands';
 
COMMENT created.
 
SQL> col comments FOR a60
SQL> SELECT * FROM audit_unified_policy_comments WHERE policy_name='CREATE_DROP_USER_POL';
 
POLICY_NAME               COMMENTS
------------------------- ------------------------------------------------------------
CREATE_DROP_USER_POL      AUDIT policy FOR CREATE AND DROP USER commands

I activate it for all users with:

SQL> AUDIT policy create_drop_user_pol;
 
AUDIT succeeded.

Checking it has been done we see that, by default, two Oracle audit policies are there (apparently only in the case of a new 12c database creation). They would also be active in the case of mixed mode when pure unified auditing is not activated:/

SQL> col user_name FOR a10
SQL> col policy_name FOR a25
SQL> SELECT * FROM audit_unified_enabled_policies;
 
USER_NAME  POLICY_NAME               ENABLED_ SUC FAI
---------- ------------------------- -------- --- ---
ALL USERS  ORA_SECURECONFIG          BY       YES YES
ALL USERS  ORA_LOGON_FAILURES        BY       NO  YES
ALL USERS  CREATE_DROP_USER_POL      BY       YES YES

You can have what are auditing active audit policies with:

SQL> col POLICY_NAME FOR a20
SQL> col AUDIT_OPTION FOR a40
SQL> SELECT * FROM AUDIT_UNIFIED_POLICIES 
     WHERE policy_name IN (SELECT policy_name FROM audit_unified_enabled_policies)
		 ORDER BY policy_name,AUDIT_OPTION;
 
POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
CREATE_DROP_USER_POL CREATE USER
CREATE_DROP_USER_POL DROP USER
ORA_LOGON_FAILURES   LOGON
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     ALTER DATABASE LINK
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE
.
.

You must initiate a new connection to see the feature working. I have dropped a test account with SYS and SYSTEM. If you are working with queued write (default mode) then you might need to flush the audit records on disk (UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter):

SQL> EXEC dbms_audit_mgmt.flush_unified_audit_trail;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col event_timestamp FOR a30
SQL> col object_name FOR a10
SQL> col system_privilege_used FOR a20
SQL> col unified_audit_policies FOR a45
SQL> col action_name FOR a15
SQL> col sql_text FOR a20 word_wrapped
SQL> col dbusername FOR a10
SQL> SET lines 200 pages 1000
SQL> SELECT event_timestamp,dbusername,action_name,object_name,sql_text,system_privilege_used,unified_audit_policies
     FROM unified_audit_trail
     ORDER BY event_timestamp DESC
     FETCH FIRST 4 ROWS only;
 
EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME     OBJECT_NAM SQL_TEXT             SYSTEM_PRIVILEGE_USE UNIFIED_AUDIT_POLICIES
------------------------------ ---------- --------------- ---------- -------------------- -------------------- ----------------------------------------
17-FEB-17 11.40.58.236976 AM   SYS        DROP USER       TEST2      DROP USER test2      SYSDBA, DROP USER    ORA_SECURECONFIG, CREATE_DROP_USER_POL, 
                                                                     CASCADE                                   ORA_SECURECONFIG
 
17-FEB-17 11.40.56.729434 AM   SYS        CREATE USER     TEST2      CREATE USER test2    SYSDBA, CREATE USER  ORA_SECURECONFIG, CREATE_DROP_USER_POL, 
                                                                     IDENTIFIED BY *                           ORA_SECURECONFIG
 
17-FEB-17 11.39.38.901577 AM   SYSTEM     DROP USER       TEST2      DROP USER test2      DROP USER            ORA_SECURECONFIG, CREATE_DROP_USER_POL, 
                                                                     CASCADE                                   ORA_SECURECONFIG
 
17-FEB-17 11.39.36.471115 AM   SYSTEM     CREATE USER     TEST2      CREATE USER test2    CREATE USER          ORA_SECURECONFIG, CREATE_DROP_USER_POL, 
                                                                     IDENTIFIED BY *                           ORA_SECURECONFIG

If we see ORA_SECURECONFIG audit policy appearing (why twice by the way ?) it is because this default Oracle policy already include audit of CREATE USER and DROP USER.

Audit cleaning

To see what are parameters related to cleaning and audit files creation execute below query:

SQL> col parameter_name FOR a30
SQL> col parameter_value FOR a20
SQL> SET pages 1000 lines 150
SQL> SELECT * FROM dba_audit_mgmt_config_params ORDER BY 1,3;
 
PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- ----------------------------
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               UNIFIED AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
 
14 ROWS selected.

So cleaning is done for file older than 5 days and each file is 10000 bytes maximum. In the case of being obliged to provide SOX evidences of users creation and deletion the 5 days retention looks a bit small…

Use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to change value. For example change unified audit retention to 1 day:

SQL> EXEC dbms_audit_mgmt.set_audit_trail_property(dbms_audit_mgmt.audit_trail_unified, dbms_audit_mgmt.os_file_max_age,1);
 
PL/SQL PROCEDURE successfully completed.

To clean use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure, I set use_last_arch_timestamp to false or you need to use DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP to instruct Oracle that you have historize the audit records before allowing their deletion (I see this as a double verification and I am not sure there is a real added value, at last it complexify the cleaning):

SQL> EXEC dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audit_trail_unified, use_last_arch_timestamp => FALSE);
 
PL/SQL PROCEDURE successfully completed.

You can display last set timestamp with something like:

SQL> SET serveroutput ON
DECLARE
 lat_ts TIMESTAMP;
BEGIN
  lat_ts := dbms_audit_mgmt.get_last_archive_timestamp(audit_trail_type=> dbms_audit_mgmt.audit_trail_unified);
  dbms_output.put_line('The last archive timestamp is: ' || NVL(TO_CHAR(lat_ts),'NULL'));
END;
/
 
The LAST archive TIMESTAMP IS: NULL
 
PL/SQL PROCEDURE successfully completed.

You can create an automatic purging job with:

SQL> EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type => dbms_audit_mgmt.audit_trail_all, default_cleanup_interval => 24);
 
PL/SQL PROCEDURE successfully completed.
 
SQL EXEC dbms_audit_mgmt.create_purge_job(audit_trail_type => dbms_audit_mgmt.audit_trail_all, -
    audit_trail_purge_interval => 24, audit_trail_purge_name => 'purge_all_audit');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col job_name FOR a15
SQL> col job_frequency FOR a25
SQL> SELECT * FROM dba_audit_mgmt_cleanup_jobs;
 
JOB_NAME        JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY             USE JOB_CON
--------------- -------- ---------------------------- ------------------------- --- -------
PURGE_ALL_AUDIT ENABLED  ALL AUDIT TRAILS             FREQ=HOURLY;INTERVAL=24   YES CURRENT

SQL Server

Audit

First I have used the Docker official SQL Server image that we have seen how to create in another post. As a reminder the special version is:

Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)   Nov  1 2016 23:24:39   Copyright (c) Microsoft Corporation  on Linux (Ubuntu 15.10)

I have also installed on my desktop (the one running all my virtual machines) free SQL Server tools called command line tool (SQLCMD) and world famous SQL Server Management Studio (SSMS).

Using SQLCMD I connect to my instance in Powershell using:

sqlcmd -S server4.domain.com -U sa -P 'Password001*'

Use the following script to create a server audit and add to it the DATABASE_PRINCIPAL_CHANGE_GROUP audit specification:

USE master;
GO
CREATE server audit sox
TO file ( filepath ='\tmp');
GO
CREATE server audit specification create_drop_user
FOR server audit sox
ADD (database_principal_change_group);
GO

From official documentation:

Action group nameDescription
DATABASE_PRINCIPAL_CHANGE_GROUPThis event is raised when principals, such as users, are created, altered, or dropped from a database. Equivalent to the Audit Database Principal Management Event Class. (Also equivalent to the Audit Add DB Principal Event Class, which occurs on the deprecated sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal, and sp_dropPrincipal stored procedures.)
This event is raised whenever a database role is added to or removed by using the sp_addrole, sp_droprole stored procedures. This event is raised whenever any database principals are created, altered, or dropped from any database. Equivalent to the Audit Add Role Event Class.

I have not noticed it immediately but when fighting to understand why it was not working I realized that the server audit and server audit specification are deactivated by default. Make them enabled with:

ALTER server audit specification create_drop_user
WITH (state=ON);
GO
ALTER server audit sox
WITH (state=ON);
GO

To see it has been well done you can use below queries:

SELECT * FROM sys.server_audits;
SELECT log_file_path,log_file_name FROM sys.server_file_audits;
SELECT * FROM sys.server_audit_specifications;
SELECT * FROM sys.server_audit_specification_details;
audit01
audit01

You can also see this with SSMS:

audit02
audit02

Now create a login and create / drop an associated user with something like:

USE testdb;
GO
CREATE login test01
WITH password = 'Password001*';
GO
CREATE USER test01 FOR login test01;
GO
DROP USER IF EXISTS test01;
GO

Connect to your Docker container with below and notice the file that has been created:

[root@server4 ~]# docker ps -a
CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
9acc1e09d39b        microsoft/mssql-server-linux   "/bin/sh -c /opt/m..."   3 months ago        Up 31 hours         0.0.0.0:1433->1433/tcp   big_archimedes
[root@server4 ~]# docker exec -ti 9acc1e09d39b /bin/bash
root@9acc1e09d39b:/#
root@9acc1e09d39b:/# ll /tmp
total 24
drwxrwxrwt  2 root root    83 Feb 21 17:16 ./
drwxr-xr-x 21 root root  4096 Nov 21 14:28 ../
-rw-r-----  1 root root 20480 Feb 21 17:18 sox_52FD4C95-A404-42AA-8605-886D47F8A0EC_0_131321709852690000.sqlaudit

But the file is not readable as is and you must use SYS.FN_GET_AUDIT_FILE function to read it. I have used SQLCMD and formatted columns size. It is not mandatory to specify a filename as you can use * to fetch all files in targeted directory:

1> SELECT event_time,succeeded,
2> CAST(database_name AS VARCHAR(10)) AS database_name,
3> CAST(object_name AS VARCHAR(10)) AS object_name,
4> CAST(statement AS VARCHAR(40)) AS statement
5> FROM sys.fn_get_audit_file('\tmp\sox_52FD4C95-A404-42AA-8605-886D47F8A0EC_0_131321709852690000.sqlaudit',DEFAULT,DEFAULT)
6> WHERE database_name != ''
7> ORDER BY event_time DESC;
8> GO
event_time                             succeeded database_name object_name statement
-------------------------------------- --------- ------------- ----------- ----------------------------------------
           2017-02-21 17:18:14.4031847         1 testdb        test01      DROP USER IF EXISTS test01;
 
           2017-02-21 17:18:13.7125479         1 testdb        test01      CREATE USER test01 FOR login test01;
 
 
(2 ROWS affected)

Audit cleaning

Drop the user and login with:

DROP USER IF EXISTS test01;
GO
DROP login test01;
GO

Drop server audit and server audit specification with:

ALTER server audit specification create_drop_user
WITH (state=off);
GO
ALTER server audit sox
WITH (state=off);
GO
DROP server audit specification create_drop_user;
GO
DROP server audit sox;
GO

Activate audit log files rotation and maximum file on disk with:

ALTER server audit sox
WITH (state=off);
GO
ALTER server audit sox
TO file (maxsize = 10MB, max_rollover_files = 10);
GO
ALTER server audit sox
WITH (state=ON);
GO

Unless I’m wrong old audit files must apparently be cleaned with a system job…

MySQL

Here I focus only on free MySQL release so MySQL Community and MariaDB and Percona forks. The audit plugins of MySQL Enterprise is out of scope. For personal preference I also try to use official MySQL Community edition and not using forks of Percona and MariaDB.

The audit is not implemented by default in MySQL Community edition. The landscape is made of three free plugins:

  • MariaDB audit plugin
  • McAfee audit plugin
  • Percona audit plugins

The default MySQL installation I have performed in 5.7.17 and I have already described how I usually install it in this article.

MariaDB audit plugin

What I have done is download MariaDB 10.1.21 and extracted server_audit.so fiel located lib/plugin of the archive and copy it in below directory:

mysql> show variables like '%plugin_dir%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| plugin_dir    | /mysql/software/mysql01/lib/plugin/ |
+---------------+-------------------------------------+
1 row in set (0.01 sec)

But the installation failed:

mysql> install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1123 | Can't initialize function 'server_audit'; Plugin is disabled |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

I have found in MySQL error log:

2017-03-01T13:10:57.721958Z 5 [ERROR] Unknown variable type code 0x8200 in plugin 'SERVER_AUDIT'.
2017-03-01T13:10:57.722032Z 5 [ERROR] Bad options for plugin 'SERVER_AUDIT'.

So as MariaDB confirmed the MySQL 5.7 community support with MariaDB 10.1.11 I have downloaded this version and realized that the plugin size is different, this time the loading went well:

mysql> install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected (0.00 sec)

You can check it has been done in error file:

170222 15:15:06 server_audit: MariaDB Audit Plugin version 1.4.0 STARTED.

Or by displaying the plugins with:

mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name                       | Status   | Type               | Library         | License |
+----------------------------+----------+--------------------+-----------------+---------+
.
.
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so | GPL     |
+----------------------------+----------+--------------------+-----------------+---------+
45 rows in set (0.00 sec)

The MariadB audit plugin available variables are:

mysql> 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_loc_info         |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| 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              |
+-------------------------------+-----------------------+
16 rows in set (0.00 sec)

I have customized below ones to change audit log file, user to audit (restricting to root but you should add any DBA accounts), auditing only DCL commands and start audit:

mysql> set global server_audit_file_path='/mysql/dump/mysql01/server_audit.log';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global server_audit_incl_users='root';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global server_audit_events='QUERY_DCL';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global server_audit_logging=on;
Query OK, 0 rows affected (0.00 sec)

Then if you create and drop a user:

mysql> create user if not exists 'test01'@'%' identified by 'secure_password';
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop user if exists test01;
Query OK, 0 rows affected (0.00 sec)

You can see in server_audit.log file (other root action like standard queries are not captured so performance impact should be small). Connect and disconnect are always logged:

.
20170223 17:26:55,server4.domain.com,root,localhost,8,44,QUERY,,'CREATE USER IF NOT EXISTS \'test01\'@\'%\' IDENTIFIED WITH \'mysql_native_password\' AS \'*F31445443BB93ED07F5FAB7744A3FCE47021238F\'',0
20170223 17:27:45,server4.domain.com,root,localhost,8,48,QUERY,,'drop user if exists test01',0
.

If your DBAs have the (bad) habit to insert and delete directly in mysql.user table you need to tune server_audit_events as below:

mysql> set global server_audit_events='QUERY_DCL,QUERY_DML';
Query OK, 0 rows affected (0.00 sec)

The status variables have not been backported to MySQL community:

mysql> show global status like '%audit%';
Empty set (0.00 sec)

The cleaning is done automatically by playing with three below parameters:

| server_audit_file_rotate_now  | OFF                                  |
| server_audit_file_rotate_size | 1000000                              |
| server_audit_file_rotations   | 9                                    |

Means that if you rotate too quickly then older files are overwritten so you must keep them in a safe place before it happens…

De-activate plugin with:

mysql> uninstall plugin server_audit;
Query OK, 0 rows affected, 1 warning (0.01 sec)

McAfee audit plugin

Same as before download the library and copy it in your plugin directory (beware of the rights):

mysql> show variables like '%plugin_dir%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| plugin_dir    | /mysql/software/mysql01/lib/plugin/ |
+---------------+-------------------------------------+
1 row in set (0.01 sec)

I have tried the install plugin command, as suggested in documentation, but I have not been able to make it working:

mysql> install plugin audit soname 'libaudit_plugin.so';
ERROR 29 (HY000): File './bin/mysqld' not found (Errcode: 2 - No such file or directory)

So added in my.cnf file and restarted my instance:

plugin-load=AUDIT=libaudit_plugin.so

You can confirm it has been done with:

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
.
.
.
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)

Get the customizable variables with (display is quite verbose !):

mysql> show variables like '%audit%';

I have configured the audit by setting below parameters. audit_record_cmds parameter has been set with underscore, I got this trick from default value of audit_password_masking_cmds parameter:

mysql> set global audit_json_log_file='/mysql/dump/mysql01/mysql-audit.json';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global audit_record_cmds='create_user,drop_user';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global audit_json_file=on;
Query OK, 0 rows affected (0.05 sec)

If your DBAs have the (bad) habit of inserting directly in mysql.user table you should add below statement to your audit list:

mysql> set global audit_record_cmds='create_user,drop_user,insert,update,delete';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global audit_record_objs='mysql.user';
Query OK, 0 rows affected (0.00 sec)

If you create and drop a test user and display the audit file you will see something like:

[root@server4 ~]# cat /mysql/dump/mysql01/mysql-audit.json
{"msg-type":"header","date":"1488207808689","audit-version":"1.1.2-686","audit-protocol-version":"1.0","hostname":"server4.domain.com","mysql-version":"5.7.17-log","mysql-program":"./bin/mysqld","mysql-socket":"/mysql/software/mysql01/conf/mysql01.sock","mysql-port":"3316","server_pid":"2976"}
{"msg-type":"activity","date":"1488207814495","thread-id":"3","query-id":"147","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.5","_client_name":"libmysql","_pid":"3011","_client_version":"5.7.17","_platform":"x86_64","program_name":"mysql"},"pid":"3011","os_user":"mysql","appname":"/mysql/software/mysql01/bin/mysql","cmd":"create_user","query":"create user if not exists 'test01'@'%' identified by '***'"}
{"msg-type":"activity","date":"1488207821390","thread-id":"3","query-id":"148","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.5","_client_name":"libmysql","_pid":"3011","_client_version":"5.7.17","_platform":"x86_64","program_name":"mysql"},"pid":"3011","os_user":"mysql","appname":"/mysql/software/mysql01/bin/mysql","cmd":"drop_user","query":"drop user if exists test01"}

To be honest the file is not super readable and the time is in Unix time, you can get something human readable for date with:

mysql> select from_unixtime(1488207814495/1000);
+-----------------------------------+
| from_unixtime(1488207814495/1000) |
+-----------------------------------+
| 2017-02-27 16:03:34.4950          |
+-----------------------------------+
1 row in set (0.00 sec)

For better display I have used a free online JSON parser. You need to separate lines with a comma (,) and add brackets ([ and ]) at top and bottom:

audit03
audit03

Percona audit plugin

Installing Percona audit plugin in a standard MySQL community will end up with an error message (that might be different for any other releases):

mysql> install plugin audit_log soname 'audit_log.so';
ERROR 1126 (HY000): Can't open shared library '/mysql/software/mysql01/lib/plugin/audit_log.so' (errno: 2 /mysql/software/mysql01/lib/plugin/audit_log.so: undefined symbol: plugin_thdvar_safe_update)

This is clearly confirmed by Percona, you have to use Percona Server to benefit from their audit plugin ! So replaced my community edition with the Percona one but I would not do this on any of our MySQL instances as we rather prefer using the standard MySQL community edition…

Using Percona Server makes things a little bit easier:

mysql> install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)

The plugin is immediately working and has already started to log all commands running on your database (!!).

The available variables are (audit.log file is in your datadir directory by default):

mysql> show variables like '%audit%';
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | audit.log     |
| audit_log_flush             | OFF           |
| audit_log_format            | OLD           |
| audit_log_handler           | FILE          |
| audit_log_include_accounts  |               |
| audit_log_include_commands  |               |
| audit_log_include_databases |               |
| audit_log_policy            | ALL           |
| audit_log_rotate_on_size    | 0             |
| audit_log_rotations         | 0             |
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+
18 rows in set (0.00 sec)

I have changed static parameters in my.cnf file (other useful parameters are dynamics):

audit_log_file='/mysql/dump/mysql01/audit.log'
audit_log_format=JSON

Then dynamically I restrict only to create and drop user commands:

mysql> set global audit_log_include_commands='create_user,drop_user';
Query OK, 0 rows affected (0.00 sec)

The audit.log file contains the required information that I display with a visual JSON parser (see previous audit plugin testing):

audit04
audit04

This is not taking into account the direct (bad) inserts and deletes from mysql.user table. To do so you would need to audit only root account (and any other root like accounts) for insert and delete…

The list of available auditable commands can be get with below query:

SELECT name FROM performance_schema.setup_instruments WHERE name LIKE 'statement/sql/%' ORDER BY name;

Audit cleaning can be done manually or automatically using two below parameter to limit audit log size and audit log file number on disk:

| audit_log_rotate_on_size    | 0                             |
| audit_log_rotations         | 0                             |

Manual log rotation can be done using audit_log_flush dynamic parameter…

References

Oracle

  • How To Enable The New Unified Auditing In 12c ? (Doc ID 1567006.1)
  • Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database (Doc ID 1171314.1)
  • AUDIT_TRAIL Set to DB (DB,EXTENDED) yet Some Audited Entries for non-Sysdba Users Are Created in the OS Trail. (Doc ID 1279934.1)
  • Audit SYS User Operations (How to Audit SYSDBA) (Doc ID 174340.1)
  • AUDIT_SYS_OPERATIONS Set To FALSE Yet Audit Files Are Generated (Doc ID 308066.1)
  • The UNIFIED_AUDIT_TRAIL is Getting Populated even if Unified Auditing was not explicitly enabled in 12c (Doc ID 1624051.1)
  • Known Issues When Using: DBMS_AUDIT_MGMT (Doc ID 804624.1)

SQL Server

MySQL

About Post Author

One thought on “Audit creation and deletion of users for SOX evidences

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>