Table of contents
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 name | Description |
|---|---|
| DATABASE_PRINCIPAL_CHANGE_GROUP | This 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; |
You can also see this with SSMS:
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:
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):
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
- Public preview of the next release of SQL Server
- SQL developer tools
- Basic SQL Server Security concepts – logins, users, and principals
MySQL
- SERVER_AUDIT — MariaDB Audit Plugin
- MySQL free audit plugin from McAfee
- Percona Audit Log Plugin
- Percona Server audit log plugin best practices
- MySQL 5.7 Reference Manual
steve jeremy says:
json parser is the best online tool editor to edit json data into json parser.
its is easy to read and understand by humans and machines.