You can use the high-performance Advanced Auditing feature in Amazon Aurora to audit database activity. To do so
- Enabling it is dynamic and it doesn't perform a cluster restart
- You can download the audit logs from console or aws cli
- You must be using Aurora 1.10.1 or greater to use Advanced Auditing.
Enabling Advanced Auditing
Use the server_audit_logging parameter to enable or disable Advanced Auditing, and the server_audit_events parameter to specify what events to log.
Also you can play with server_audit_excl_users and server_audit_incl_users which are auto esclusive with each other.
list of parameters
server_audit_logging
Enables or disables Advanced Auditing. This parameter defaults to OFF; set it to ON to enable Advanced Auditing.
server_audit_events
Contains the comma-delimited list of events to log. Events must be specified in all caps, and there should be no white space between the list elements, for example: CONNECT,QUERY_DDL. This parameter defaults to an empty string.
You can log any combination of the following events:
CONNECT Logs both successful and failed connections and also disconnections. This event includes user information.
QUERY Logs all queries in plain text, including queries that fail due to syntax or permission errors.
QUERY_DCL Similar to the QUERY event, but returns only data control language (DCL) queries (GRANT, REVOKE, and so on).
QUERY_DDL Similar to the QUERY event, but returns only data definition language (DDL) queries (CREATE, ALTER, and so on).
QUERY_DML Similar to the QUERY event, but returns only data manipulation language (DML) queries (INSERT, UPDATE, and so on).
TABLE Logs the tables that were affected by query execution.
View Audit logs
- view and download by console
- by aws cli
aws rds --profile bt describe-db-log-files --db-instance-identifier sd99999aaaaaaaa --filename-contains audit
aws rds download-db-log-file-portion --db-instance-identifier sd99999aaaaaaaa --log-file-name "audit/audit.log.3.2017-08-07-09-21.8" > audit.log.3.2017-08-07-09-21.8
Replace \n with newline
vim :%s/\\n/\r/g
Audit Log Details
Log files are in UTF-8 format. Logs are written in multiple files, the number of which varies based on instance size. To see the latest events, you might have to review all of the audit log files.
Log entries are not in sequential order. You can use the timestamp value for ordering.
Log files are rotated when they reach 100 MB in aggregate. This limit is not configurable.
The audit log files include the following comma-delimited information in rows, in the specified order:
Field Description
timestamp The Unix time stamp for the logged event with microsecond precision.
serverhost The name of the instance that the event is logged for.
username The connected user name of the user.
host The host that the user connected from.
connectionid The connection ID number for the logged operation.
queryid The query ID number, which can be used for finding the relational table events and related queries. For TABLE events, multiple lines are added.
operation The recorded action type. Possible values are: CONNECT, QUERY, READ, WRITE, CREATE, ALTER, RENAME, and DROP.
database The active database, as set by the USE command.
object For QUERY events, this value indicates the executed query. For TABLE events, it indicates the table name.
retcode The return code of the logged operation.
Performance impacted
- Running a stress test, I could see that with the feature enabled it impacted the workaround of 6% less then it is not enabled.
| What | ENABLED | DISABLED |
|---|---|---|
| read | 1489660 | 1581590 |
| write | 1191728 | 1265272 |
| total | 3277252 | 632636 |
| lat_min | 40.83 | 43.20 |
| lat_avg | 128.91 | 121.42 |
| lat_95 | 170.48 | 158.63 |
Example audit log file
1502095712927028,sd22by0so8u820,sb_user,10.112.50.39,401,7513938,READ,sbtest,sbtest1,
1502095712927082,sd22by0so8u820,sb_user,10.112.50.39,476,7513937,QUERY,sbtest,'SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 29956341 AND 29956341',0
1502095712927082,sd22by0so8u820,sb_user,10.112.50.39,401,7513938,QUERY,sbtest,'SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN 31589948 AND 31589948',0
1502095712927136,sd22by0so8u820,sb_user,10.112.50.39,464,7513939,READ,sbtest,sbtest1,