Skip to content

You are viewing documentation for Immuta version 2022.2.

For the latest version, view our documentation for Immuta SaaS or the latest self-hosted version.

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.

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

Best Practices: Store Audit Records

By default Immuta audit records expire after 60 days, so store audit records outside of Immuta in order to retain the audits long term.

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.

Property Description Example
ID integer b0000000-1234-abcd-11111111111111
DateTime integer or string The timestamp for when the record was created. This may be an ISO-8601 timestamp string or an epoch timestamp. 2504188066580 or 2017-08-31T14:01:15.607Z
Month integer 1455
ProfileID integer The profile ID of the user who made the query. 1
UserID string The user ID of the user who made the query. jane.doe@immuta.com
DataSourceID integer The ID of the data source that was queried. 12
DataSourceName string The name of the data source that was queried. Public Customer Data
ProjectID integer The ID of the project the data source is in. 18
ProjectName string The name of the project the data source is in. Project 1
PurposeID integer The ID of the project's purpose(s). 22
RecordType string The type of record captured. Databricks query audit records will always be spark.
Success boolean If true, the query was successful. true or false
Component string The Immuta component that generated the record. nativeSql
AccessType string Indicates whether access was granted to an individual blob or if this was a query potentially encompassing many blobs. query
Query string The query that was run in the integration. See the example below
actionStatus string Indicates whether or not the user was granted access to the data. UNAUTHORIZED or SUCCESS. See the Enriched Databricks Audit Logs section for details.
actionStatusReason string When a user's query is denied, this property explains why. When a query is successful, this value is null. See the Enriched Databricks Audit Logs section for details.
policySet array Provides policy details. See the Enriched Databricks Audit Logs section for details.
entitlements array Includes the user's groups, attributes, and current project at the time of the query. See the Enriched Databricks Audit Logs section for details.

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

Query Scenario

Before examining the audit log excerpt below, review the user's entitlements and the policy on the patient_transactions data source.

User Entitlements

  • groups: none
  • attributes: SpecialAccess.Addresses, OfficeLocation.Maryland
  • current project: Medical Claims

Data Source Policy

  • Subscription Policy: Allow individual users you select to access the data source.

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

Query Scenario

Before examining the audit log excerpt below, review the user's entitlements and the policy on the patients data source.

User Entitlements

  • groups: none
  • attributes: SpecialAccess.Addresses, OfficeLocation.Maryland
  • current project: None

Data Source Policies

  • Subscription Policy: Allow individual users you select to access the data source.
  • Data Policy: Mask by making null the value in the column(s) lastname for everyone.
  • Data Policy: Mask by hashing the value in the column(s) address for everyone except users who possess the attribute SpecialAccess.Addresses.

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

Query Scenario

Before examining the audit log excerpt below, review the user's entitlements and the policy on the maryland_employees data source.

User Entitlements

  • groups: none
  • attributes: SpecialAccess.Addresses, OfficeLocation.Maryland
  • current project: None

Data Source Policy

  • Merged Subscription Policy: Allow users to subscribe if they have the attribute OfficeLocation.Maryland or are a member of the group Human Resources.

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.