How do I use logs to track activity in my Amazon Redshift database cluster?
How can I perform database auditing on my Amazon Redshift cluster?
Short description
Amazon Redshift provides three logging options:
- Audit logs: Stored in Amazon Simple Storage Service (Amazon S3) buckets
- STL tables: Stored on every node in the cluster
- AWS CloudTrail: Stored in Amazon S3 buckets
Audit logs and STL tables record database-level activities, such as which users logged in and when. These tables also record the SQL activities that these users performed and when. CloudTrail tracks activities performed at the service level.
Note: To view logs using external tables, use Amazon Redshift Spectrum. For more information, see Analyze database audit logs for security and compliance using Amazon Redshift Spectrum.
Resolution
Audit logs and STL tables
The following table compares audit logs and STL tables. Choose the logging option that's appropriate for your use case.
| Audit logs | STL tables |
| Must be enabled. To enable audit logging, follow the steps for Configuring auditing using the console or Configuring logging by using the Amazon Redshift CLI and API. | Automatically available on every node in the data warehouse cluster. |
| Audit log files are stored indefinitely unless you define Amazon S3 lifecycle rules to archive or delete files automatically. For more information, see Object lifecycle management. | Log history is stored for two to five days, depending on log usage and available disk space. To extend the retention period, use the Amazon Redshift system object persistence utility from AWS Labs on GitHub. |
| Access to audit log files doesn't require access to the Amazon Redshift database. | Access to STL tables requires access to the Amazon Redshift database. |
| Reviewing logs stored in Amazon S3 doesn't require database computing resources. | Running queries against STL tables requires database computing resources, just as when you run other queries. |
| Using timestamps, you can correlate process IDs with database activities. Cluster restarts don't affect audit logs in Amazon S3. | It's not always possible to correlate process IDs with database activities, because process IDs might be recycled when the cluster restarts. |
| Stores information in the following log files: |
- Connection log
- User log
- User activity log Note: You must enable the enable_user_activity_logging database parameter for the user activity log. For more information, see Enabling logging. | Stores information in multiple tables. Use the following tables to review information similar to what is stored in the S3 audit logs:
- SVL_STATEMENTTEXT: Provides a complete record of SQL commands that have been run on the system. Combines all of the rows in the STL_DDLTEXT, STL_QUERYTEXT, and STL_UTILITYTEXT tables.
- STL_CONNECTION_LOG: Logs authentication attempts, connections, or disconnections. | | Records all SQL statements in the user activity logs. | Queries that are run are logged in STL_QUERY. DDL statements are logged in STL_DDLTEXT. The text of non-SELECT SQL commands are logged in STL_UTILITYTEXT. | | Statements are logged as soon as Amazon Redshift receives them. Files on Amazon S3 are updated in batch, and can take a few hours to appear. | Logs are generated after each SQL statement is run. | | Records who performed what action and when that action happened, but not how long it took to perform the action. | Use the STARTTIME and ENDTIME columns to determine how long an activity took to complete. To determine which user performed an action, combine SVL_STATEMENTTEXT (userid) with PG_USER (usesysid). | | You are charged for the storage that your logs use in Amazon S3. | There are no additional charges for STL table storage. | | Leader-node only queries are recorded. | Leader-node only queries aren't recorded. |
CloudTrail
Using information collected by CloudTrail, you can determine what requests were successfully made to AWS services, who made the request, and when the request was made. For more information, see Logging Amazon Redshift API calls with AWS CloudTrail.
CloudTrail log files are stored indefinitely in Amazon S3, unless you define lifecycle rules to archive or delete files automatically. For more information, see Object Lifecycle Management.
Related information
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor 3 Monaten
- AWS OFFICIALAktualisiert vor 4 Monaten
- AWS OFFICIALAktualisiert vor 4 Monaten
- AWS OFFICIALAktualisiert vor 6 Monaten