I have an issue that I think relates to this question:
Drupal 9 View Shows Permission Denied For CiviCRM Tables
and possibly this:
The Views I have recreated from my D7 install are all fine except for one use case - a contextual filter on the logged-in user. The View shows CiviCRM cases and I need to display those cases where the logged in user is the client. So this involves relating the two together via CiviCRM's uf_match table. It works fine on D7.
The SQL error I get on a local dev setup is:
SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'civicrm'@'172.18.0.4' for table 'users_field_data': SELECT "civicrm_case"."id" AS "id", "civicrm_contact_civicrm_case_contact"."id" AS "civicrm_contact_civicrm_case_contact_id", "users_field_data_civicrm_uf_match"."uid" AS "users_field_data_civicrm_uf_match_uid" FROM "civicrm_case" "civicrm_case" INNER JOIN civicrm_dev.civicrm_case_contact "civicrm_case_contact" ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_dev.civicrm_contact "civicrm_contact_civicrm_case_contact" ON civicrm_case_contact.contact_id = civicrm_contact_civicrm_case_contact.id LEFT JOIN civicrm_dev.civicrm_uf_match "civicrm_contact_civicrm_case_contact__civicrm_uf_match" ON civicrm_contact_civicrm_case_contact.id = civicrm_contact_civicrm_case_contact__civicrm_uf_match.contact_id LEFT JOIN drupal_dev.users_field_data "users_field_data_civicrm_uf_match" ON civicrm_contact_civicrm_case_contact__civicrm_uf_match.uf_id = users_field_data_civicrm_uf_match.uid WHERE ((civicrm_case.case_type_id = :civicrm_case_case_type_id)) AND ((civicrm_case.status_id = :civicrm_case_status_id)) AND ((users_field_data_civicrm_uf_match.uid = :users_field_data_uid)) LIMIT 11 OFFSET 0; Array ( [:civicrm_case_case_type_id] => 20 [:civicrm_case_status_id] => 1 [:users_field_data_uid] => 1 )
I have CiviCRM Entity installed (3.5) on D9.5.1 using MySQL8. Drupal and CiviCRM are in separate databases. As per the documentation for Views on Drupal 9, I have added a section in my Drupal install's settings.php to include the CiviCRM database - see below. In my old D7 system I needed to add all the CiviCRM tables in a separate array and no longer do that as per the documentation and from what I can glean elsewhere. Drupal has SELECT rights on the CiviCRM database, again as per the documentation (unchanged from D7).
$databases['civicrm']['default'] = array(
'database' => 'civicrm_dev',
'username' => 'civicrm',
'password' => 'civicrm',
'prefix' => '',
'host' => 'db',
'port' => '3306',
'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
'driver' => 'mysql',
'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
);
$databases['default']['default'] = array(
'database' => 'drupal_dev',
'username' => 'drupal',
'password' => 'drupal',
'prefix' => '',
'host' => 'db',
'port' => '3306',
'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
'driver' => 'mysql',
'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
);
What is interesting is that it is the civicrm user that has the access violation, not drupal (as it always used to be when I had a new CiviCRM custom field and forgot to update the civicrm table array in settings.php...)
I can solve this problem easily by simply giving the civicrm database user SELECT rights to the drupal database, as I did for the drupal database user on the civicrm database. Effectively giving each side the rights to view each other's database - but this seems wrong given the purpose of the CiviCRM Entity module.
So is this a poorly configured View in D9, a missing step or incorrect config, or have I stumbled on an issue?
