Audit log system table reference

Preview

This feature is in Public Preview.

This article outlines the audit log table schema and provides you with sample queries you can use with the audit log system table to answer common account usage questions. For information on audit log events, see Audit log reference.

The audit log system table is located at system.access.audit.

Audit log considerations

  • Most audit logs are only available in the region of the workspace.

  • Only Unity Catalog account-level logs are available in all regions.

  • Account-level audit logs record workspace_id as 0.

Audit log system table schema

The audit log system table uses the following schema:

Column name

Data type

Description

Example

version

string

Audit log schema version

2.0

event_time

timestamp

Timestamp

2023-01-01T01:01:01.123

event_date

date

Calendar date the action took place

2023-01-01

workspace_id

long

ID of the workspace

1234567890123456

source_ip_address

string

IP address where the request originated

10.30.0.242

user_agent

string

Origination of request

Apache-HttpClient/4.5.13 (Java/1.8.0_345)

session_id

string

ID of the session where the request came from

123456789

user_identity

string

Identity of user initiating request

{"email": "user@domain.com", "subjectName": null}

service_name

string

Service name initiating request

unityCatalog

action_name

string

Category of the event captured in audit log

getTable

request_id

string

ID of request

ServiceMain-4529754264

request_params

map

Map of key values containing all the request parameters. Depends on request type

[["full_name_arg", "user.chat.messages"], ["workspace_id", "123456789"], ["metastore_id", "123456789"]]

response

struct

Struct of response return values

{"statusCode": 200, "errorMessage": null, "result": null}

audit_level

string

Workspace or account level event

ACCOUNT_LEVEL

account_id

string

ID of the account

23e22ba4-87b9-4cc2-9770-d10b894bxx

event_id

string

ID of the event

34ac703c772f3549dcc8671f654950f0

Sample queries

The following sections include sample queries you can use to gain insights into your audit logs system table. For these queries to work, replace the values within curly brackets {{}} with your own parameters.

Note

Some of these examples include verbose audit log events, which are not enabled by default. To enable verbose audit logs in a workspace, see Enable verbose audit logs.

This article includes the following example queries:

Who accessed this table?

This query uses the information_schema.

SELECT DISTINCT(grantee) AS `ACCESSIBLE BY`
FROM system.information_schema.table_privileges
WHERE table_schema = '{{schema_name}}' AND table_name = '{{table_name}}'
  UNION
    SELECT table_owner
    FROM system.information_schema.tables
    WHERE table_schema = '{{schema_name}}' AND table_name = '{{table}}'
  UNION
    SELECT DISTINCT(grantee)
    FROM system.information_schema.schema_privileges
    WHERE schema_name = '{{schema_name}}'

Which users accessed a table within the last day?

Note

Full names are not captured in the log for DML operations. Include the schema and simple name to capture all.

SELECT
  user_identity.email as `User`,
  IFNULL(request_params.full_name_arg,
    request_params.name)
    AS `Table`,
    action_name AS `Type of Access`,
    event_time AS `Time of Access`
FROM system.access.audit
WHERE (request_params.full_name_arg = '{{catalog.schema.table}}'
  OR (request_params.name = '{{table_name}}'
  AND request_params.schema_name = '{{schema_name}}'))
  AND action_name
    IN ('createTable','getTable','deleteTable')
  AND event_date > now() - interval '1 day'
ORDER BY event_date DESC

Which tables did a user access?

Note

To filter by date range, uncomment out the date clause at the bottom of the query.

SELECT
        action_name as `EVENT`,
        event_time as `WHEN`,
        IFNULL(request_params.full_name_arg, 'Non-specific') AS `TABLE ACCESSED`,
        IFNULL(request_params.commandText,'GET table') AS `QUERY TEXT`
FROM system.access.audit
WHERE user_identity.email = '{{User}}'
        AND action_name IN ('createTable',
'commandSubmit','getTable','deleteTable')
        -- AND datediff(now(), event_date) < 1
        -- ORDER BY event_date DESC

Example result

EVENT

WHEN

TABLE ACCESSED

QUERY TEXT

getTable

2023-05-31

system.access.audit

GET table

getTable

2023-05-31

system.access.table_lineage

GET table

commandSubmit

2023-05-31

Non-specific

show functions;

commandSubmit

2023-05-31

Non-specific

SELECT

request_params

FROM

system.access.audit

WHERE

service_name = "notebook"

AND action_name = "moveFolder"

LIMIT

5

View permissions changes for all securable objects

This query will return an event for every permission change that has occurred in your account. The query will return the user who made the change, the securable object type and name, and the specific changes that were made.

SELECT event_time, user_identity.email, request_params.securable_type, request_params.securable_full_name, request_params.changes
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name = 'updatePermissions'
ORDER BY 1 DESC

View the most recently run notebook commands

This query returns the most recently run notebook commands along with the user who ran the command.

Note

The runCommand action is only emitted when verbose audit logs are enabled. To enable verbose audit logs, see Enable verbose audit logs.

SELECT event_time, user_identity.email, request_params.commandText
FROM system.access.audit
WHERE action_name = `runCommand`
ORDER BY event_time DESC
LIMIT 100