Logo of Trend Micro

SQL Audit Logs Retention Period

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: AlibabaCloud-RDS-009

For reliability and compliance purposes, ensure that the retention period configured for SQL audit logs is 6 months or greater. The retention period represents the length of time to keep SQL audit log data recorded for your RDS database instances.

Reliability
Performance
efficiency
Operational
excellence

SQL auditing monitors database events and records them in an audit log within the Alibaba Cloud MySQL account. A log data retention period of 6 months or more should allow you to collect the necessary amount of audit data useful to check for anomalies and potential security breaches, or misuse of information and access to your SQL database.

Audit

To determine if your RDS database instances are configured with a sufficient log data retention period, perform the following operations:

Using Alibaba Cloud Console

01 Sign in to your Alibaba Cloud account.

02 Navigate to Relational Database Services (RDS) console at https://rdsnext.console.aliyun.com/dashboard.

03 In the left navigation panel, under ApsaraDB RDS, choose Instances.

04 Click on the ID (link) of the SQL database instance that you want to examine, listed in the Instance ID/Name column.

05 In the RDS resource navigation panel, under Autonomy Services, choose SQL Explorer and Audit to access the SQL Explorer service dashboard.

06 On the SQL Explorer dashboard, choose Service Settings to access the SQL Explorer service settings. If the Service Settings button is not available, the SQL Explorer service is not enabled for the selected RDS instance. You can follow the steps outlined on this page to enable SQL Explorer.

07 In the Service Settings configuration box, check the Storage Duration setting to determine the log data retention period. If the Storage Duration setting is set to a value less than 6 months, the auditing policy for the selected RDS database instance does not have a sufficient log data retention period configured.

08 Repeat steps no. 4 – 7 for each database instance provisioned in your Alibaba Cloud account.

Using Alibaba Cloud CLI

01 Run DescribeDBInstances command (OSX/Linux/UNIX) with custom output filters to list the ID of each RDS database instance available within your Alibaba Cloud account: 

aliyun rds DescribeDBInstances
  --output cols=DBInstanceId

02 The command output should return a list with the requested database instance identifiers: 

DBInstanceId
------------
rm-abcd1234abcd1234
rm-1234abcd1234abcd

03 Run DescribeSQLCollectorRetention command (OSX/Linux/UNIX) with the name of the RDS database instance that you want to examine as the identifier parameter, to describe the log data retention period configured for the selected RDS instance: 

aliyun rds DescribeSQLCollectorRetention
  --region eu-west-1
  --DBInstanceId 'rm-abcd1234abcd1234'
  --output cols=ConfigValue

04 The command output should return the log data retention period in number of days: 

ConfigValue
-----------
30

The ConfigValue attribute value represents the number of days to retain log data. If the ConfigValue attribute is set to a value less than 6 months, as shown in the example above, the auditing policy for the selected RDS database instance does not have a sufficient log data retention period configured.

05 Repeat steps no. 3 and 4 for each database instance provisioned within your Alibaba Cloud account.

Remediation / Resolution

To extend audit log data retention period for your RDS database instances, perform the following operations:

Using Alibaba Cloud Console

01 Sign in to your Alibaba Cloud account.

02 Navigate to Relational Database Services (RDS) console at https://rdsnext.console.aliyun.com/dashboard.

03 In the left navigation panel, under ApsaraDB RDS, choose Instances.

04 Click on the ID (link) of the SQL database instance that you want to examine, listed in the Instance ID/Name column.

05 In the RDS resource navigation panel, under Autonomy Services, choose SQL Explorer and Audit to access the SQL Explorer service dashboard.

06 On the SQL Explorer dashboard, choose Service Settings to access the SQL Explorer service settings. If the Service Settings button is not available, the SQL Explorer service is not enabled for the selected RDS instance. You can follow the steps outlined on this page to enable SQL Explorer.

07 In the Service Settings configuration box, ensure that the Enable button is activated, set Storage Duration to 6 Months or longer, then choose OK to apply the changes.

08 Repeat steps no. 4 – 7 for each RDS database instance available in your Alibaba Cloud account.

Using Alibaba Cloud CLI

01 Run ModifySQLCollectorRetention command (OSX/Linux/UNIX) to extend audit log data retention period for the selected RDS database instance. The following command example sets the log data retention period to 365 days (1 year): 

aliyun rds ModifySQLCollectorRetention
  --region eu-west-1
  --DBInstanceId 'rm-abcd1234abcd1234'
  --ConfigValue 365

02 If successful, the output should return the command request ID: 

{"RequestId":"ABCDABCD-1234-ABCD-1234-ABCD1234ABCD"}

03 Repeat steps no. 1 and 2 for each RDS database instance available within your Alibaba Cloud account.

References

Publication date Feb 26, 2024

Related AlibabaCloud-RDS rules