1

I have an issue that I think relates to this question:

Drupal 9 View Shows Permission Denied For CiviCRM Tables

and possibly this:

Views integration error on D9

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?

3
  • 1
    If Civi tables in a separate database, then both db users will need select privileges to both databases. It is common to use same db user and have databases on same server. Working as expected Aug 21, 2023 at 19:18
  • If the View's base table is a Civi table, it'll use the Civi database user. If base table is a Drupal user, it'll use the Drupal database user. Aug 21, 2023 at 19:21
  • OK, many thanks, so my 'hack' of select rights was actually a solution. That is not clear at all in the documentation, and this differs to Drupal 7 (where this same View works, but in the settings.php you add the tables). It is odd that it is only the connection with the Drupal users that is causing the issue. Anyway, I'll create an answer, thanks again
    – ChumKui
    Aug 22, 2023 at 8:27

1 Answer 1

3

As per the comment by jackrabbithanna, the solution in this case is to give the civicrm database user SELECT rights on the Drupal Database.

I think it is an odd solution given the way I thought Views and CiviCRM Entity worked, but it at least a simple one!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.