Auditing & Monitoring Access to Sensitive Data - Satori

Auditing & Monitoring Access to Sensitive Data

An audit log consists of logging certain operations to a log. The audit log is then both kept for investigations into events, as well as analyzed continuously to find incidents that you want to know about to reduce the risk of data loss (such as compliance breaks, over-privileged users, and other security risks). The more relevant information and context you have about the events logged – the better. In addition to their security and operational usefulness, audit logs are also an important part of meeting compliance requirements.

Database audit logs are separated into two parts:

  1. An access log, detailing the history of successful and failed logins to the database.
  2. A query log, detailing the history of successful and failed queries made on the database.

As a certain database session contains one successful login event, but may contain a large number of queries sent to the database, and the amount of information may also be substantially larger (a query can be anything from a SELECT 1 heartbeat to a hundred lines of code).

Why are audit logs especially important in Amazon Redshift?

Amazon Redshift is used as a data warehouse or as part of a data lake solution with Redshift Spectrum. With the growing popularity of data democratization, and data analytics in general, organizations allow data access to more users and teams. This means that those organizations also want to understand what’s happening in their data analysis environments, both in terms of security and in terms of operational efficiency.

Where are Amazon Redshift logs kept?

There are two levels of auditing in Redshift. The logs are natively kept in system tables, and in addition, for long-term storage, you can enable audit logging to S3 buckets. Let’s explore what native logging into system tables gives you and what the addition of logging to S3 buckets gives.

Using Amazon Redshift System Tables

Using the built-in system tables, you can investigate events quickly using SQL from within the database itself. The retention period for such logs is under a week, so do not expect to use these in the long term. However, such logs are still handy. Here are some of the use cases:

  1. For dashboarding and monitoring purposes.
  2. For debugging and investigating ongoing or “fresh” incidents. Note that it takes time for logs to get from your system tables to your S3 buckets, so new events will only be available in your system tables.
  3. When you have not enabled native logs, you need to investigate past events that you’re hoping are still retained (the “ouch” option).

The following are the essential system tables (STLs) used for logging in Redshift:

Why are audit logs significant in Amazon Redshift?

Generating a query log from stl_querytext

For doing this, you need to reconstruct the chunks in stl_querytext. As an example, the following query pulls the last ten queries run on the cluster:
SELECT query,
LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) AS query_statement, COUNT(*) as row_count
FROM stl_querytext
GROUP BY query
ORDER BY query DESC
LIMIT 10;

The following query returns the top 10 longest queries:
WITH queries AS (
SELECT query,
LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) AS query_statement, COUNT(*) as row_count
FROM stl_querytext
GROUP BY query)
SELECT * FROM queries WHERE query_statement ILIKE 'select%'
ORDER BY LEN(query_statement) DESC
LIMIT 10;

Amazon Redshift Permissions Changes Log

The following log returns all grants and revokes of permissions:
WITH util_cmds AS (
SELECT userid,
LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) AS query_statement
FROM stl_utilitytext GROUP BY userid, xid ORDER BY xid)
SELECT util_cmds.userid, stl_userlog.username, query_statement
FROM util_cmds
LEFT JOIN stl_userlog ON (util_cmds.userid = stl_userlog.userid)
WHERE query_statement ILIKE '%GRANT%' OR query_statement ILIKE '%REVOKE%';

Showing the Last 10 Failed Logins
SELECT *
FROM stl_connection_log
WHERE event = 'authentication failure'
ORDER BY recordtime DESC
LIMIT 10;

Enabling Amazon Redshift Query Logs

This section describes how to automatically export the log data from Redshift to an S3 bucket for long-term storage. This may incur additional storage costs.

Enabling Query Logging in Amazon Redshift

User activity logging is disabled by default; enabling it requires changing the parameter group to set enable_user_activity_logging to true. You can edit the parameter group's setting from the cluster management properties tab.

Sensitive Data in Logs

Queries in the system tables are not redacted and kept as-is. This means they may contain sensitive information such as personally identifiable information (PII).

Analyzing the Audit Logs

For analysis, you may want to use a third-party log collector or build your own ETL service that will make the compressed log files accessible via a data-querying engine such as Amazon Athena. When creating this process, you may want to anonymize the queries to ensure that the final audit logs are free of sensitive data and can be used by more teams.

Amazon Redshift Audit Logs in Satori

For Amazon Redshift customers of Satori, you can use our Universal Audit feature, which logs all activities from all your data platforms in the same place. The advantages of using Satori universal audit include: