Databricks Unity Catalog Query Audit Logs

Learn about the audit schema for Databricks Unity Catalog queries

Query audit for Databricks Unity Catalog captures user's data access within Unity Catalog and presents the query audit logs in Immuta. Using the Databricks system.query.history and system.access.column_lineage tables, Immuta joins the tables and pulls out key details about the user's queries, which are then translated into audit logs that can be viewed in the Immuta UI or exported.

Immuta audits the activity of all Unity Catalog users and tables regardless of whether they are registered in Immuta.

Requirements

• Databricks Unity Catalog integration with query audit enabled
• A Databricks deployment with system tables capabilities

Store audit logs

By default, Databricks Unity Catalog audit logs expire after 90 days. Export the universal audit model (UAM) logs to S3 or ADLS Gen2, and store audit logs outside of Immuta in order to retain the audit logs long-term.

Audit frequency

Immuta collects audit records once an hour by default, and the frequency can be changed at any time from the Immuta app settings page. The frequency is a global setting based on integration type, so organizations with multiple Databricks Unity Catalog integrations will have the same audit frequency for all of them. The more frequent the audit records are ingested, the more current the audit records. However, there could be performance and cost impacts from the frequent jobs because Immuta will start a Databricks cluster to complete the audit ingest job if one is not already running.

To manually prompt the query audit, click Load Audit Events on the Immuta audit page.

Audit scope

Immuta audits all data sources and users in Unity Catalog. An administrator can configure the integration to just ingest specific workspaces when registering the connection. The audit is intended to ingest user queries for data access, so queries with terms that signify transformation queries are filtered out of audit.

Audit schema

Each audit message from the Immuta platform will be a one-line JSON object containing the properties listed below.

Property	Description	Example
action	The action associated with the audit log.	QUERY
actor.type	The Immuta user type of the actor who made the query. When the actor is not registered with Immuta, the type, id, and name fields will be unknown.	USER_ACTOR
actor.id	The Immuta user ID of the actor who made the query. When the actor is not registered with Immuta, the type, id, and name fields will be unknown.	[e-mail]
actor.name	The Immuta name of the user who made the query. When the user is not registered with Immuta, the type, id, and name fields will be unknown.	Taylor
actor.identityProvider	The IAM the user is registered in. bim is the built-in Immuta IAM. When the user is not registered with Immuta, this field will be omitted.	bim
actor.profileId	The profile ID of the user who made the query. When the user is not registered with Immuta, this field will be omitted.	10
sessionId	The session ID of the user who performed the action.	01ee14d9-cab3-1ef6-9cc4-f0c315a53788
actionStatus	Indicates whether or not the user was granted access to the data. Possible values are UNAUTHORIZED, FAILURE, or SUCCESS.	SUCCESS
actionStatusReason	When available, the reason from Unity Catalog that the user’s query was denied.	null if actionStatus is SUCCESS
eventTimestamp	The time the query occurred.	2023-06-27T11:03:59.000Z
id	The unique ID of the audit record.	9f542dfd-5099-4362-a72d-8377306db3b8
tenantId	The Immuta SaaS tenant ID.	your-immuta.com
targetType	The type of targets affected by the query; this value will always be DATASOURCE.	DATASOURCE
targets	A list of the targets affected by the query.	See the example below
auditPayload.type	The type of audit record; this value will always be: QueryAuditPayload.	QueryAuditPayload
auditPayload.queryId	The unique ID of the query. If the query joins multiple tables, each table will appear as a separate log, but all will have the same query ID.	01ee14da-517a-1670-afce-0c3e0fdcf7d4
auditPayload.query	The command text of the query that was run in the integration. Immuta truncates the query text to the first 2048 characters. The queries are included in audit records by default, but you can configure Immuta to exclude them from audit records on the app settings page.	SELECT VERSION AS 'version' FROM 'sample-data'.'__immuta_version'
auditPayload.startTime	The date and time the query started in UTC.	2023-06-27T11:03:59.000Z
auditPayload.endTime	The date and time the query ended in UTC.	2023-06-27T11:03:59.000Z
auditPayload.duration	The time the query took in seconds.	0.557
auditPayload.technologyContext.type	The technology the query was made in.	DatabricksContext
auditPayload.technologyContext.clusterId	The Unity Catalog cluster ID.	null
auditPayload.technologyContext.workspaceId	The Unity Catalog workspace ID.	8765531160949612
auditPayload.technologyContext.service	Where in Unity Catalog the query was made. Possible values are PLUGIN CLUSTER WAREHOUSE SERVERLESS_COMPUTE	WAREHOUSE
auditPayload.technologyContext.queryLanguage	The language used to write the query.	sql
auditPayload.technologyContext.warehouseId	The Unity Catalog warehouse ID.	559483c6eac0359f
auditPayload.technologyContext.notebookId	The Unity Catalog notebook ID.	869500255746458
auditPayload.technologyContext.account.id	The actor’s Unity Catalog account ID	52e863bc-ea7f-46a9-8e17-6aed7541832d
auditPayload.technologyContext.account.username	The actor’s Unity Catalog username.	[e-mail]
auditPayload.technologyContext.host	The Unity Catalog host.	deployment-name.cloud.databricks.com
auditPayload.objectsAccessed	The Unity Catalog objects accessed.	[]
auditPayload.securityProfile.sensitivity.score	The sensitivity score of the query. Classification must be configured for this field.	INDETERMINATE
auditPayload.version	The version of the audit event schema.	1
receivedTimestamp	The timestamp of when the audit event was received and stored by Immuta.	2023-06-27T15:18:22.314Z

Example audit record

{
  "action": "QUERY",
  "actor": {
    "type": "USER_ACTOR",
    "id": "[e-mail]",
    "name": "Taylor",
    "identityProvider": "bim",
    "profileId": "10"
  },
  "sessionId": "01ee14d9-cab3-1ef6-9cc4-f0c315a53788",
  "actionStatus": "SUCCESS",
  "actionStatusReason": null,
  "eventTimestamp": "2023-06-27T11:03:59.000Z",
  "id": "01ee14da-517a-1670-afce-0c3e0fdcf7d4",
  "tenantId": "your-immuta.com",
  "targetType": "DATASOURCE",
  "targets": [
      {
      "type": "DATASOURCE",
      "id": "2034",
      "name": "University Art Gallery Exhibition",
      "technology": "DATABRICKS"
    }
  ],
  "relatedResources": [],
  "auditPayload": {
    "type": "QueryAuditPayload",
    "queryId": "01ee14da-517a-1670-afce-0c3e0fdcf7d4",
    "query": "SELECT VERSION AS `version` FROM `sample-data`.`__immuta_version`",
    "startTime": "2023-06-27T11:03:59.000Z",
    "endTime": "2023-06-27T11:05:59.000Z",
    "duration": 23.568,
    "technologyContext": {
      "type": "DatabricksContext",
      "clusterId": null,
      "workspaceId": "3841033049363283",
      "service": "WAREHOUSE",
      "queryLanguage": "sql"
      "warehouseId": "559483c6eac0359f",
      "notebookId": null,
      "account": {
        "id": "52e863bc-ea7f-46a9-8e17-6aed7541832d",
        "username": "[e-mail]"
      },
      "host": "deployment-name.cloud.databricks.com",
      "rowsProduced": 2
    },
    "objectsAccessed": [
      {
        "name": "catalog.audit_uc_test.University Art Gallery Exhibition",
        "datasourceId": "2034",
        "databaseName": "catalog",
        "schemaName": "audit_uc_test",
        "type": "TABLE",
        "columns": [
          {
            "name": "status_description",
            "tags": [
              {
                "type": "TAG",
                "id": "122",
                "name": "Personally Identifiable Information.true",
                "source": "collibra",
                "context": "catalog",
                "deleted": false,
                "transient": false
              }
            ],
            "securityProfile": {
              "sensitivity": {
                "score": "INDETERMINATE"
              }
            }
        }]
    }],
    "securityProfile": {
      "sensitivity": {
        "score": "INDETERMINATE"
      }
    },
    "version": 1
  },
  "receivedTimestamp": "2023-06-27T15:18:22.314Z"
}

Limitations

• Immuta determines unauthorized events based on error messages within Unity Catalog records. When the error messages contain expected language, unauthorized events will be available for Databricks Unity Catalog audit logs. In other cases it is not possible to determine the cause of an error.
• Immuta audit records include unregistered data sources and users; however, activity from them will not appear in any governance reports.
• Databricks queries where the response is being served from cache will not be represented in the system.access.column_lineage table which means the query audit records will be unmapped in Immuta.
• The Databricks tables used to populate Immuta audit logs may be populated with a delay, so Immuta has a 5 hour buffer for updates with each audit ingest. When an audit event is first ingested, Immuta will create an audit log, even if it is missing details. If on a later audit ingest the Databricks tables have new details about the audit event, Immuta will update the audit log with the new information.

  • If using Immuta's audit export, there may be multiple records for a single Databricks query event due to updates. If this scenario occurs and you have multiple audit logs for the same event, they can be matched with the id field, and the audit log with the most recent receivedTimestamp should be used. Then, the additional logs with the missing objectsAccessed can be deleted. This is more likely to happen on setups with a shorter audit export window.