Databricks Query Audit Logs

Audience: System Administrators, users with the AUDIT permission, and Data Owners

Content Summary: In addition to the executed Spark plan, the tables, and the tables' underlying paths for every audited Spark job, Immuta captures the code or query that triggers the Spark plan. This page outlines this process and provides examples of the captured query and resulting audit record. Immuta audits the activity of Immuta users on Immuta data sources.

For a tutorial on getting these audit logs, see the View Audit Logs page.

Requirements

• Databricks integration with native query audit enabled
• Databricks tables registered as Immuta data sources
• Databricks users registered as Immuta users: Note that the users' Databricks usernames must be mapped to Immuta. Without this, Immuta will not know the users are Immuta users and will not collect audit events for their data access activity.

Audit Messages

Each audit message from the Immuta platform will be a one-line JSON object containing the properties listed below. These audit records are stored with the recordType: spark.

Example queryText

Below is an example of the queryText, which contains the full notebook cell (since the query was the result of a notebook). If the query had been from a JDBC connection, the queryText would contain the full SQL query.

testTable = 'default.crime_data_delta'
testDb = 'test'

df = spark.table(testTable)
df.limit(1).collect()

filteredDf = df.filter('victim_age > 20')

filteredDf.write.saveAsTable('{}.audit_cell'.format(testDb))
spark.table('{}.audit_cell'.format(testDb)).limit(1).collect()

spark.sql('DROP TABLE IF EXISTS {}.audit_cell'.format(testDb))

This notebook cell had multiple audit records associated with it, but the example audit record in the tab to the right corresponds to the filteredDf.write.saveAsTable('{}.audit_cell'.format(testDb)) line.

Example Audit Record

{
"id": "b0d49f2a-4a34-4d50-b36e-fd9b619eed32",
"dateTime": "1617997828777",
"month": 1455,
"profileId": 1,
"userId": "kris@immuta.com",
"dataSourceId": 41,
"dataSourceName": "Crime Data Delta",
"projectId": 17,
"projectName": "test",
"purposeIds": [
22
],
"count": 1,
"recordType": "spark",
"success": true,
"component": "dataSource",
"accessType": "query",
"query": "'CreateTable `test`.`audit_cell`, ErrorIfExists\n+- Filter (victim_age#8907 > 20)\n   +- ImmutaResolvedTableAlias default.crime_data_delta\n      +- SubqueryAlias spark_catalog.immuta.default_crime_data_delta\n         +- TrustedPlan\n            +- Project [dr_number#8882, area_id#8883, area_name#8884, reporting_district#8885, crime_code#8886, crime_code_description#8887, mo_codes#8888, victim_sex#8889, victim_descent#8890, premise_code#8891, premise_description#8892, weapon_used_code#8893, weapon_description#8894, status_code#8895, status_description#8896, crime_code_1#8897, crime_code_2#8898, crime_code_3#8899, crime_code_4#8900, address#8901, cross_street#8902, location#8903, date_reported#8904, date_occurred#8905, ... 2 more fields]\n               +- SubqueryAlias spark_catalog.default.crime_data_delta\n                  +- Relation[dr_number#8882,area_id#8883,area_name#8884,reporting_district#8885,crime_code#8886,crime_code_description#8887,mo_codes#8888,victim_sex#8889,victim_descent#8890,premise_code#8891,premise_description#8892,weapon_used_code#8893,weapon_description#8894,status_code#8895,status_description#8896,crime_code_1#8897,crime_code_2#8898,crime_code_3#8899,crime_code_4#8900,address#8901,cross_street#8902,location#8903,date_reported#8904,date_occurred#8905,... 2 more fields] parquet\n",
"extra": {
"maskedColumns": {},
"metastoreTables": [
"default.crime_data_delta"
],
"pathUris": [
"dbfs:/user/hive/warehouse/crime_data_delta"
],
"queryText": "testTable = 'default.crime_data_delta'\ntestDb = 'test'\n\ndf = spark.table(testTable)\ndf.limit(1).collect()\n\n\n\n\n\n\n\n\n\n# doing spark things...\n\n\n\n\n\n\n\n\nfilteredDf = df.filter('victim_age > 20')\n\nfilteredDf.write.saveAsTable('{}.audit_cell'.format(testDb))\nspark.table('{}.audit_cell'.format(testDb)).limit(1).collect()\n\nspark.sql('DROP TABLE IF EXISTS {}.audit_cell'.format(testDb))",
"queryLanguage": "python",
"purposes": [
"Re-identification Prohibited.Expert Determination.SDM"
]
},
"dataSourceTableName": "default_crime_data_delta",
"createdAt": "2021-04-09T19:50:28.787Z",
"updatedAt": "2021-04-09T19:50:28.787Z"
}

This example audit record contains two fields under extra:

• queryText: The queryText will contain either the full notebook cell (when the query is the result of a notebook) or the full SQL query (when it is a query from a JDBC connection).

• queryLanguage: The queryLanguage corresponds to the programming language used: SQL, Python, Scala, or R. Audited JDBC queries will indicate that it came from JDBC here.

Enriched Databricks Audit Logs

Beyond raw audit events (such as “John Doe queried Table X in Databricks"), the Databricks audit records include the policy information enforced during the query execution, even if a query was denied.

Queries will be denied if at least one of the conditions below is true:

• User does not meet policy conditions.
• User is not subscribed to the data source.
• Data source is not in the user's current project.
• Data source is in the user's current project, but the user is not subscribed to the data source.
• Data source is not registered in Immuta.

Query Denied Records

Access denied audit records include details about the user (accessControls.entitlements) and the Subscription policy (accessControls.policySet) that blocked access to the table.

User Entitlements

The user's entitlements represent the state at the time of the query. This includes the following fields:

• project: The user's current project.
• attributes: The user's attributes.
• groups: The user's groups.
• impersonatedUsers: The user that the current user is impersonating.

Policy Information

The policySet includes the following fields:

• subscriptionPolicyType: The type of Subscription policy (such as MANUAL, ADVANCED, or ENTITLEMENTS).
• type: Indicates whether the policy is a SUBSCRIPTION or DATA policy. Query denied records will always be a Subscription policy type.
• ruleAppliedForUser: Indicates whether or not the policy was applied for the user. If false, the user was an exception to the policy.
• rationale: The policy rationale written by the policy creator.
• global: Indicates whether or not the policy was a Global policy. When false, the policy is Local.
• mergedPolicies: Shows the policy information for each of the merged Global Subscription policies.

Query Denied Audit Log Excerpt

The Databricks user described above attempts to query a patient_transactions table, which has not been added to a project. The user's query is denied, and the audit logs reveal the policy details. Focus specifically on the entitlements, policySet, actionStatus, and actionStatusReason sections below:

"accessControls": {
  "entitlements": {
    "attributes": [{
        "attribute": "SpecialAccess",
        "values": [
          "Addresses"
        ]
    },
    {
        "attribute": "OfficeLocation",
        "values": [
          "Maryland"
        ]
    }],
    "groups": [],
    "project": {
      "name": "Medical Claims",
      "id": 10,
      "projectKey": "Medical Claims",
      "equalized": false,
      "purposes": []
    },
  },
  "policySet": [{
    "subscriptionPolicyType": "MANUAL",
    "type": "SUBSCRIPTION",
    "ruleAppliedForUser": true,
    "global": false
  }]
},
"actionStatus": "UNAUTHORIZED",
"actionStatusReason": "User not subscribed to the datasource or it is not in the current project."

Although the user was subscribed to the data source, the project field shows that this user is currently working under the Medical Claims project, and the actionStatusReason indicates that the data source has not been added to that project. Consequently, the actionStatus field shows the query was UNAUTHORIZED.

Successful Query Records

User Entitlements

The user's entitlements includes the following fields:

• project: The user's current project.
• attributes: The user's attributes.
• groups: The user's groups.
• impersonatedUsers (when relevant): The username the current user is impersonating.

Policy Information

For a successful query, policySet includes all policies applied to the data source:

• subscriptionPolicyType: The type of Subscription policy (such as MANUAL, ADVANCED, or ENTITLEMENTS).
• type: Indicates whether the policy is a SUBSCRIPTION or DATA policy.
• ruleAppliedForUser: Indicates whether or not a policy was applied for the user. If false, the user was an exception to the policy.
• rationale: Provides the rationale given for the policy by the policy creator.
• global: Indicates whether or not the policy was a Global policy. When false, the policy is Local.
• mergedPolicies: Shows the policy information for each of the merged Global Subscription policies.

Successful Query Excerpt

The Databricks user described above attempts to query a patients table. The user's query is successful, and the audit logs reveal the policy details. Focus specifically on the entitlements, policySet, and actionStatus sections below:

"accessControls": {
  "entitlements": {
    "attributes": [{
        "attribute": "SpecialAccess",
        "values": [
          "Addresses"
        ]
      },
      {
        "attribute": "OfficeLocation",
        "values": [
          "Maryland"
        ]
      }
    ],
    "groups": []
  },
  "policySet": [{
      "subscriptionPolicyType": "MANUAL",
      "type": "SUBSCRIPTION",
      "ruleAppliedForUser": true,
      "rationale": "test",
      "global": false
    },
    {
      "type": "DATA",
      "dataPolicyType": "MASKING",
      "rules": [{
        "fields": [
          "lastname"
        ],
        "ruleAppliedForUser": true,
        "maskingType": "NULL"
      }],
      "global": false,
      "rationale": null
    },
    {
      "type": "DATA",
      "dataPolicyType": "MASKING",
      "rules": [{
        "fields": [
          "address"
        ],
        "ruleAppliedForUser": false,
        "maskingType": "hashing",
        "exceptions": {
          "operator": "all",
          "attributes": [{
            "name": "SpecialAccess",
            "value": "Addresses"
          }]
        },
      }],
      "global": false,
      "rationale": null
    }
  ]
},
"actionStatus": "SUCCESS",
"actionStatusReason": null,

This user is subscribed to the data source and the actionStatus shows their query was a SUCCESS.

The first masking policy in policySet masks the field lastname to NULL for everyone, as no exceptions are listed. Furthermore, the ruleAppliedForUser field is true, indicating that the querying user sees NULL values for this column.

However, the ruleAppliedForUser for the second masking policy in the policySet is false, indicating that the querying user can see the values in the address column in the clear because one of their entitlements is listed under exceptions to the policy: SpecialAccess.Addresses.

Global Merged Subscription Policies Excerpt

The Databricks user described above attempts to query the maryland_employees table. The user's query is successful, and the audit logs reveal the policy details. Focus specifically on the entitlements, mergedPolicies, and actionStatus sections below:

"accessControls": {
  "entitlements": {
    "attributes": [{
        "attribute": "SpecialAccess",
        "values": [
          "Addresses"
        ]
      },
      {
        "attribute": "OfficeLocation",
        "values": [
          "Maryland"
        ]
      }
    ],
    "groups": []
  },
  "policySet": [{
    "subscriptionPolicyType": "ADVANCED",
    "advanced": "(@hasAttribute('OfficeLocation', 'Maryland')) OR (@isInGroups('Human Resources'))",
    "type": "SUBSCRIPTION",
    "ruleAppliedForUser": true,
    "global": true,
    "mergedPolicies": [{
        "id": 4,
        "name": "Human Resources Department Subscription Policy",
        "policyKey": "Human Resources Department Subscription Policy",
        "rationale": null,
        "subscriptionPolicyType": "ENTITLEMENTS",
        "entitlements": {
          "operator": "all",
          "groups": [
            "Human Resources"
          ]
        },
        "type": "SUBSCRIPTION"
      },
      {
        "id": 5,
        "name": "Maryland Office Policy",
        "policyKey": "Maryland Office Policy",
        "rationale": null,
        "subscriptionPolicyType": "ENTITLEMENTS",
        "entitlements": {
          "operator": "all",
          "attributes": [{
            "name": "OfficeLocation",
            "value": "Maryland"
          }]
        },
        "type": "SUBSCRIPTION"
      }
    ]
  }]
},
"actionStatus": "SUCCESS",
"actionStatusReason": null,

This user is subscribed to the data source and the actionStatus shows their query was a SUCCESS.

The mergedPolicies section illustrates both Global policies that were merged and applied to the data source. Although the querying user is not a member of the group Human Resources, the ruleAppliedForUser field is still true and they are subscribed to the data source because they met one requirement of the subscription policy: they possess the attribute OfficeLocation.Maryland.