Drupal 9 View Shows Permission Denied For CiviCRM Tables

Question

I have my Drupal 9 database and my CiviCRM database in two separate databases on the same Amazon MySQL Aurora instance. The setup in our dev environment is identical and is working perfectly. However, after performing the upgrade to Drupal 9 and CiviCRM 5.37.2 from Drupal 8 I am now getting a MySQL access denied error in production, only when accessing CiviCRM with Views.

I have looked at this issue as an Amazon issue, a Drupal issue, and a CiviCRM issue. I am not positive it is a CiviCRM issue but the tables are specifically CiviCRM so I am hoping I can get some help, please.

This is the full error:

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'user'@'10.0.2.97' for table 'civicrm_contact': SELECT "civicrm_contact"."id" AS "id", "users_field_data_civicrm_uf_match"."uid" AS "users_field_data_civicrm_uf_match_uid", "contact_id_civicrm_contact"."id" AS "contact_id_civicrm_contact_id", "member_of_contact_id_civicrm_contact"."id" AS "member_of_contact_id_civicrm_contact_id" FROM {civicrm_contact} "civicrm_contact" INNER JOIN {civicrm_uf_match} "civicrm_uf_match" ON civicrm_contact.id = civicrm_uf_match.contact_id INNER JOIN {users_field_data} "users_field_data_civicrm_uf_match" ON civicrm_uf_match.uf_id = users_field_data_civicrm_uf_match.uid LEFT JOIN {civicrm_membership} "contact_id_civicrm_contact" ON civicrm_contact.id = contact_id_civicrm_contact.contact_id LEFT JOIN {civicrm_membership_type} "member_of_contact_id_civicrm_contact" ON civicrm_contact.id = member_of_contact_id_civicrm_contact.member_of_contact_id WHERE (users_field_data_civicrm_uf_match.uid = :users_field_data_uid) LIMIT 1 OFFSET 0; Array ( [:users_field_data_uid] => 3016 )

I have checked that when logging in directly to the database from the EC2 instance that I have access to the database and the specific table. My EC2 security group has the appropriate grants for Amazon Aurora.

I my settings.php has

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`civicrm_prod`.', ...

I updated the array tonight after the upgrade.

Any help is greatly appreciated.
Thanks,

Josh

Answer 1

Remove ` symbol added to the database name in settings.php

eg

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`civicrm_prod`.', ...

change to

$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => 'civicrm_prod.', ...