Skip to main content

Audit log system table reference

Preview

This system table is in Public Preview. To access the table, the schema must be enabled in your system catalog. For more information, see Enable system table schemas.

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

Table path: This system table is located at system.access.audit.

Audit log considerations​

  • Most audit logs are only available in the region of the workspace.
  • Account-level audit logs record workspace_id as 0.

Audit log system table schema​

The audit log system table uses the following schema:

Column nameData typeDescriptionExample
versionstringAudit log schema version2.0
event_timetimestampTimestamp of the event. Timezone information is recorded at the end of the value with +00:00 representing UTC timezone.2023-01-01T01:01:01.123+00:00
event_datedateCalendar date the action took place2023-01-01
workspace_idlongID of the workspace1234567890123456
source_ip_addressstringIP address where the request originated10.30.0.242
user_agentstringOrigination of requestApache-HttpClient/4.5.13 (Java/1.8.0_345)
session_idstringID of the session where the request came from123456789
user_identitystringIdentity of user initiating request{"email": "user@domain.com",
"subjectName": null}
service_namestringService name initiating requestunityCatalog
action_namestringCategory of the event captured in audit loggetTable
request_idstringID of requestServiceMain-4529754264
request_paramsmapMap of key values containing all the request parameters. Depends on request type[["full_name_arg", "user.chat.messages"],
["workspace_id", "123456789"],
["metastore_id", "123456789"]]
responsestructStruct of response return values{"statusCode": 200, "errorMessage": null,
"result": null}
audit_levelstringWorkspace or account level eventACCOUNT_LEVEL
account_idstringID of the account23e22ba4-87b9-4cc2-9770-d10b894bxx
event_idstringID of the event34ac703c772f3549dcc8671f654950f0
identity_metadatastructIdentities involved in the action, including run_by and run_as. See Auditing group dedicated compute activty.{run_by: example@email.com;
run_as: example@email.com;

Sample queries​

The following sections include sample SQL queries you can use to gain insights into your audit logs system table.

note

Some 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 can access this table?​

This query uses the information_schema to determine which users have permissions on a table. Enter values for the catalog, schema, and table name parameters.

SQL
SELECT DISTINCT(grantee), privilege_type, 'catalog' AS level
FROM system.information_schema.catalog_privileges
WHERE
  catalog_name = :catalog_name
UNION
SELECT DISTINCT(grantee), privilege_type, 'schema' AS level
FROM system.information_schema.schema_privileges
WHERE
  catalog_name = :catalog_name AND schema_name = :schema_name
UNION
SELECT DISTINCT(grantee) AS `accessible by`, privilege_type, 'table' AS level
FROM
  system.information_schema.table_privileges
WHERE
  table_catalog = :catalog_name AND table_schema = :schema_name AND table_name = :table_name
UNION
SELECT table_owner, 'ALL_PRIVILEGES' AS privilege_type, 'owner' AS level
FROM system.information_schema.tables
WHERE
  table_catalog = :catalog_name AND table_schema = :schema_name AND table_name = :table_name

Which users accessed a table within the last seven day?​

For this query to work, enter the table path information in the query parameters.

note

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

SQL
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 = :table_full_name
    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 7 day
ORDER BY
  event_date DESC

Which tables did a user access recently?​

For this query to work, enter the user’s email in the :User parameter and a number in the :days_ago parameter.

SQL
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) < :days_ago
ORDER BY
  event_date DESC

Example result​

EVENTWHENTABLE ACCESSEDQUERY TEXT
getTable2023-05-31system.access.auditGET table
getTable2023-05-31system.access.table_lineageGET table
commandSubmit2023-05-31Non-specificshow functions;
commandSubmit2023-05-31Non-specificSELECT

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.

SQL
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 and 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.

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

Which users have logged into a Databricks app?​

This query returns an event for each login to a Databricks app instance.

SQL
SELECT
  event_date,
  workspace_id,
  request_params.request_object_id as app,
  user_identity.email as user_email,
  user_identity.subject_name as username
FROM
  system.access.audit
WHERE
  action_name IN ("workspaceInHouseOAuthClientAuthentication", "mintOAuthToken", "mintOAuthAuthorizationCode")
AND
  request_params["client_id"] LIKE "{{application-ID}}"
GROUP BY
  event_date,
  workspace_id,
  app,
  user_email,
  username

Replace {{application-ID}} with the Application ID value for the service principal assigned to a specific Databricks app. This value can be found in the admin settings for the Databricks workspace hosting the app.

Which Databricks apps have been updated to change how the app is shared with other users or groups?​

This query returns events for permission updates to change the access to Databricks apps, including the permission type, the user or group to which the new permission is assigned, and the user who submitted the change.

SQL
SELECT
  event_date,
  workspace_id,
  request_params['request_object_id'] as app,
  user_identity['email'] as sharing_user,
  acl_entry['group_name'],
  acl_entry['user_name'],
  acl_entry['permission_level']
FROM
  system.access.audit t
LATERAL VIEW
  explode(from_json(request_params['access_control_list'], 'array<struct<user_name:string,permission_level:string,group_name:string>>')) acl_entry AS acl_entry
WHERE
  action_name = 'changeAppsAcl'
AND
  request_params['request_object_type'] = 'apps'
ORDER BY
  event_date DESC
Last updated on