The issue is resolved in bits and pieces with this.
So, Some of the questions are able to run with the changes I have made in the admin panel for the access.
Two scenarios again:
- There is one question which is running fine even after applying the filters post the page load. It also runs fine when the user change and run the filters.
- The second case is - in some specific questions, this same activity is giving a permission error.
After deep diving into the network console, I've realised, metabase is working differently for the step 1 and step 2.
below are the details
For - Step 1 (Happy case)
- metabase hits the API end point /api/card/{card_id}/query
- In the response, it directly gets the data and rows json
- After I apply filter, it hits the same API again and get a similar respose
For - Step 2 (problem case)
- for the initial load, metabase hits the API end point /api/card/{card_id}/query
- gets the response with data and rows Json
- After I apply the second filter, Here is where metabase trys to hit a different API /api/dataset
Once this happens, the query trys to run a as a native query and gives a permission error to the user (since the user doesn't have permission to any native SQL editing).
Surprisingly, after I apply the filter, I get the error. When I am in the error page, If I reload the entire page, the data loads (this time it hits the /api/card/card_id/query API) including the second filter I've tried to apply. (this kind of solves the use case, but..) this is a very bad user experience. Since user has to apply the filter, wait for the permission error to pop up and reload the page again to access the results.
I'm sharing the screenshots and the stack trace for the second case below
"database_id": 2,
"started_at": "2022-04-07T07:22:38.671718Z",
"error_type": "missing-required-permissions",
"json_query":
{
"type": "native",
"native":
{
"template-tags":
{
"Case_Type":
{
"id": "108911ab-8cc1-1e77-2131-76146bcd35db",
"name": "Case_Type",
"display-name": "Case type",
"type": "text"
},
"ALIGNER_DELIVERY_DATE_filter":
{
"id": "dd67ab7d-d5df-e2d2-7c91-92a0c36b8323",
"name": "ALIGNER_DELIVERY_DATE_filter",
"display-name": "(Yes/No) Aligner Delivery Filter",
"type": "text"
},
"OUT_DATE_filter":
{
"id": "3a159486-e121-c51b-ff86-0c457db8296e",
"name": "OUT_DATE_filter",
"display-name": "(Yes/No) Out date Done",
"type": "text"
},
"VISIT_TYPE":
{
"id": "c9814917-e1ab-a25d-314c-bdc23004c89a",
"name": "VISIT_TYPE",
"display-name": "Visit type (HOME / VC/ CLINIC)",
"type": "text"
},
"uid":
{
"id": "41a17dbd-cf54-8b7a-16bb-586e3af1fc23",
"name": "uid",
"display-name": "Uid",
"type": "text"
},
"city":
{
"id": "9e570bb3-0e03-03b2-b0c4-abd0ab4a293a",
"name": "city",
"display-name": "City",
"type": "dimension",
"dimension":
[
"field",
375,
null
],
"widget-type": "category"
},
"delivery_status":
{
"id": "4a06a2e1-c284-389a-3496-a379f128da9c",
"name": "delivery_status",
"display-name": "Delivery status (Done/ Booked / Not Booked/ Booked Not Done)",
"type": "text",
"required": false
},
"ALIGNER_CONSULTATION_COMPLETION_filter":
{
"id": "1ff4c363-e19a-ede3-ab57-0ee76a6868bd",
"name": "ALIGNER_CONSULTATION_COMPLETION_filter",
"display-name": "(Yes/No) Aligner consultation completion",
"type": "text"
},
"Plan_Type":
{
"id": "03f6a7d6-d391-e0e1-85ec-3ab3f2b8272a",
"name": "Plan_Type",
"display-name": "Plan type",
"type": "text"
},
"end_date":
{
"id": "8553e2e7-6345-6cad-15c5-2e9ce0b09a2a",
"name": "end_date",
"display-name": "End date",
"type": "date"
},
"snippet: booked_slots":
{
"id": "f859d6d6-74ef-4f9f-8eb0-255a2ed9680c",
"name": "snippet: booked_slots",
"display-name": "Snippet: booked slots",
"type": "snippet",
"snippet-name": "booked_slots",
"snippet-id": 2
},
"start_date":
{
"id": "54be5667-0eb9-6df2-7fa1-37c36efbd65c",
"name": "start_date",
"display-name": "Start date",
"type": "date"
},
"SERVICE_PROVIDER":
{
"id": "c1f6d1ad-9550-2560-387a-930679428e10",
"name": "SERVICE_PROVIDER",
"display-name": "Service provider",
"type": "text"
},
"tracking_id":
{
"id": "e5ccf2e4-f854-d935-b346-50ba7c631bfe",
"name": "tracking_id",
"display-name": "Tracking",
"type": "text"
}
},
"query": "my query 123"
},
"database": 2,
"parameters":
[
{
"type": "date/single",
"value": "2022-04-08",
"target":
[
"variable",
[
"template-tag",
"end_date"
]
],
"id": "8553e2e7-6345-6cad-15c5-2e9ce0b09a2a"
},
{
"type": "date/single",
"value": "2022-04-02",
"target":
[
"variable",
[
"template-tag",
"start_date"
]
],
"id": "54be5667-0eb9-6df2-7fa1-37c36efbd65c"
},
{
"type": "category",
"value": "Yes",
"target":
[
"variable",
[
"template-tag",
"ALIGNER_CONSULTATION_COMPLETION_filter"
]
],
"id": "1ff4c363-e19a-ede3-ab57-0ee76a6868bd"
}
],
"middleware":
{
"js-int-to-string?": true,
"add-default-userland-constraints?": true
}
},
"status": "failed",
"class": "class clojure.lang.ExceptionInfo",
"stacktrace":
[
"--> query_processor.middleware.permissions$perms_exception.invokeStatic(permissions.clj:29)",
"query_processor.middleware.permissions$perms_exception.doInvoke(permissions.clj:24)",
"query_processor.middleware.permissions$perms_exception.invokeStatic(permissions.clj:26)",
"query_processor.middleware.permissions$perms_exception.invoke(permissions.clj:24)",
"query_processor.middleware.permissions$fn__46237$check_ad_hoc_query_perms__46242$fn__46243.invoke(permissions.clj:83)",
"query_processor.middleware.permissions$fn__46237$check_ad_hoc_query_perms__46242.invoke(permissions.clj:79)",
"query_processor.middleware.permissions$fn__46269$check_query_permissions_STAR___46274$fn__46275.invoke(permissions.clj:98)",
"query_processor.middleware.permissions$fn__46269$check_query_permissions_STAR___46274.invoke(permissions.clj:88)",
"query_processor.middleware.permissions$check_query_permissions$fn__46288.invoke(permissions.clj:107)",
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__51006.invoke(pre_alias_aggregations.clj:40)",
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46684.invoke(cumulative_aggregations.clj:60)",
"query_processor.middleware.visualization_settings$update_viz_settings$fn__46622.invoke(visualization_settings.clj:63)",
"query_processor.middleware.escape_join_aliases$escape_join_aliases_middleware$fn__48032.invoke(escape_join_aliases.clj:64)",
"query_processor.middleware.fix_bad_references$fix_bad_references_middleware$fn__50971.invoke(fix_bad_references.clj:91)",
"query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__47569.invoke(resolve_joined_fields.clj:111)",
"query_processor.middleware.resolve_joins$resolve_joins$fn__51618.invoke(resolve_joins.clj:178)",
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__51160.invoke(add_implicit_joins.clj:246)",
"query_processor.middleware.large_int_id$convert_id_to_string$fn__47588.invoke(large_int_id.clj:59)",
"query_processor.middleware.format_rows$format_rows$fn__51212.invoke(format_rows.clj:74)",
"query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__46956.invoke(add_default_temporal_unit.clj:23)",
"query_processor.middleware.desugar$desugar$fn__46595.invoke(desugar.clj:21)",
"query_processor.middleware.binning$update_binning_strategy$fn__39509.invoke(binning.clj:229)",
"query_processor.middleware.resolve_fields$resolve_fields$fn__45930.invoke(resolve_fields.clj:34)",
"query_processor.middleware.add_dimension_projections$add_remapping$fn__50338.invoke(add_dimension_projections.clj:487)",
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__50632.invoke(add_implicit_clauses.clj:164)",
"query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__47976.invoke(upgrade_field_literals.clj:117)",
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__47333.invoke(add_source_metadata.clj:125)",
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50883.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)",
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__48970.invoke(auto_bucket_datetimes.clj:147)",
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__45911.invoke(resolve_source_table.clj:45)",
"query_processor.middleware.parameters$substitute_parameters$fn__48624.invoke(parameters.clj:109)",
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__45984.invoke(resolve_referenced.clj:79)",
"query_processor.middleware.expand_macros$expand_macros$fn__52276.invoke(expand_macros.clj:184)",
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__48404.invoke(add_timezone_info.clj:15)",
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__51227.invoke(splice_params_in_response.clj:32)",
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643$fn__50648.invoke(resolve_database_and_driver.clj:35)",
"driver$do_with_driver.invokeStatic(driver.clj:60)",
"driver$do_with_driver.invoke(driver.clj:56)",
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50643.invoke(resolve_database_and_driver.clj:34)",
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__46530.invoke(fetch_source_query.clj:286)",
"query_processor.middleware.store$initialize_store$fn__46721$fn__46722.invoke(store.clj:11)",
"query_processor.store$do_with_store.invokeStatic(store.clj:44)",
"query_processor.store$do_with_store.invoke(store.clj:38)",
"query_processor.middleware.store$initialize_store$fn__46721.invoke(store.clj:10)",
"query_processor.middleware.validate$validate_query$fn__50978.invoke(validate.clj:10)",
"query_processor.middleware.normalize_query$normalize$fn__50985.invoke(normalize_query.clj:22)",
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__48350.invoke(add_rows_truncated.clj:35)",
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49641.invoke(results_metadata.clj:82)",
"query_processor.middleware.constraints$add_default_userland_constraints$fn__48368.invoke(constraints.clj:42)",
"query_processor.middleware.process_userland_query$process_userland_query$fn__50919.invoke(process_userland_query.clj:146)",
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__51307.invoke(catch_exceptions.clj:169)",
"query_processor.reducible$async_qp$qp_STAR___43282$thunk__43283.invoke(reducible.clj:103)",
"query_processor.reducible$async_qp$qp_STAR___43282.invoke(reducible.clj:109)",
"query_processor.reducible$sync_qp$qp_STAR___43291$fn__43294.invoke(reducible.clj:135)",
"query_processor.reducible$sync_qp$qp_STAR___43291.invoke(reducible.clj:134)",
"query_processor$process_userland_query.invokeStatic(query_processor.clj:247)",
"query_processor$process_userland_query.doInvoke(query_processor.clj:243)",
"query_processor$fn__52324$process_query_and_save_execution_BANG___52333$fn__52336.invoke(query_processor.clj:258)",
"query_processor$fn__52324$process_query_and_save_execution_BANG___52333.invoke(query_processor.clj:251)",
"query_processor$fn__52368$process_query_and_save_with_max_results_constraints_BANG___52377$fn__52380.invoke(query_processor.clj:270)",
"query_processor$fn__52368$process_query_and_save_with_max_results_constraints_BANG___52377.invoke(query_processor.clj:263)",
"api.dataset$run_query_async$fn__65303.invoke(dataset.clj:69)",
"query_processor.streaming$streaming_response_STAR_$fn__38418$fn__38419.invoke(streaming.clj:162)",
"query_processor.streaming$streaming_response_STAR_$fn__38418.invoke(streaming.clj:161)",
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)",
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)",
"async.streaming_response$do_f_async$task__26888.invoke(streaming_response.clj:84)"
],
"card_id": null,
"context": "ad-hoc",
"error": "You do not have permissions to run this query.",
"row_count": 0,
"running_time": 0,
"ex-data":
{
"type": "missing-required-permissions",
"required-permissions":
[
"/db/2/native/"
],
"actual-permissions":
[
"/collection/4/read/",
"/collection/780/read/",
"/collection/20/read/",
"/collection/131/read/",
"/collection/343/read/",
"/collection/562/read/",
"/collection/403/read/",
"/collection/3/read/",
"/collection/417/read/",
"/collection/145/read/",
"/collection/root/read/",
"/collection/452/read/",
"/collection/861/read/",
"/collection/867/read/",
"/collection/19/read/",
"/collection/125/read/",
"/collection/79/read/",
"/collection/401/read/",
"/collection/419/read/",
"/collection/420/read/",
"/collection/376/read/",
"/collection/397/read/",
"/collection/788/read/",
"/collection/377/read/",
"/collection/747/read/",
"/collection/402/read/",
"/db/2/schema/",
"/collection/366/read/",
"/collection/864/read/",
"/collection/355/read/",
"/collection/400/read/",
"/collection/815/read/",
"/collection/822/read/",
"/collection/393/read/",
"/collection/860/read/",
"/collection/695/read/",
"/collection/78/read/",
"/collection/132/read/",
"/collection/367/read/",
"/collection/424/read/",
"/collection/24/read/",
"/collection/418/read/",
"/collection/18/read/",
"/collection/17/read/",
"/collection/398/read/",
"/db/5/schema/",
"/collection/863/",
"/collection/399/read/",
"/collection/421/read/",
"/collection/498/",
"/collection/770/read/",
"/collection/677/read/",
"/collection/663/read/",
"/collection/327/read/",
"/collection/412/read/"
],
"permissions-error?": true
},
"data":
{
"rows":
[],
"cols":
[]
}
}
on the first post