Answers.Body: <p>As per the comment by jackrabbithanna, the solution in this case is to give the civicrm database user SELECT rights on the Drupal Database.</p>
  <p>I think it is an odd solution given the way I thought Views and CiviCRM Entity worked, but it at least a simple one!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-22T08:31:31.610Z
Answers.LastActivityDate: 2023-08-22T08:31:31.610Z
Answers.UserDisplayName: ChumKui
Body: <p>I have an issue that I think relates to this question:</p>
  <p><a href="https://civicrm.stackexchange.com/questions/39830/drupal-9-view-shows-permission-denied-for-civicrm-tables">Drupal 9 View Shows Permission Denied For CiviCRM Tables</a></p>
  <p>and possibly this:</p>
  <p><a href="https://www.drupal.org/project/civicrm_entity/issues/3198146" rel="nofollow noreferrer">Views integration error on D9</a></p>
  <p>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 <code>uf_match</code> table.  It works fine on D7.</p>
  <p>The SQL error I get on a local dev setup is:</p>
  <pre><code>SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'civicrm'@'172.18.0.4' for table 'users_field_data': SELECT &quot;civicrm_case&quot;.&quot;id&quot; AS &quot;id&quot;, &quot;civicrm_contact_civicrm_case_contact&quot;.&quot;id&quot; AS &quot;civicrm_contact_civicrm_case_contact_id&quot;, &quot;users_field_data_civicrm_uf_match&quot;.&quot;uid&quot; AS &quot;users_field_data_civicrm_uf_match_uid&quot; FROM &quot;civicrm_case&quot; &quot;civicrm_case&quot; INNER JOIN civicrm_dev.civicrm_case_contact &quot;civicrm_case_contact&quot; ON civicrm_case.id = civicrm_case_contact.case_id INNER JOIN civicrm_dev.civicrm_contact &quot;civicrm_contact_civicrm_case_contact&quot; ON civicrm_case_contact.contact_id = civicrm_contact_civicrm_case_contact.id LEFT JOIN civicrm_dev.civicrm_uf_match &quot;civicrm_contact_civicrm_case_contact__civicrm_uf_match&quot; ON civicrm_contact_civicrm_case_contact.id = civicrm_contact_civicrm_case_contact__civicrm_uf_match.contact_id LEFT JOIN drupal_dev.users_field_data &quot;users_field_data_civicrm_uf_match&quot; 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] =&gt; 20 [:civicrm_case_status_id] =&gt; 1 [:users_field_data_uid] =&gt; 1 ) 
  </code></pre>
  <p>I have CiviCRM Entity installed (3.5) on D9.5.1 using MySQL8. Drupal and CiviCRM are in separate databases.  As per the <a href="https://docs.civicrm.org/sysadmin/en/latest/integration/drupal/views/#drupal-9" rel="nofollow noreferrer">documentation for Views on Drupal 9</a>, 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).</p>
  <pre><code>$databases['civicrm']['default'] = array(
    'database' =&gt; 'civicrm_dev',
    'username' =&gt; 'civicrm',
    'password' =&gt; 'civicrm',
    'prefix' =&gt; '',
    'host' =&gt; 'db',
    'port' =&gt; '3306',
    'namespace' =&gt; 'Drupal\\mysql\\Driver\\Database\\mysql',
    'driver' =&gt; 'mysql',
    'autoload' =&gt; 'core/modules/mysql/src/Driver/Database/mysql/',
  );
  
  $databases['default']['default'] = array(
    'database' =&gt; 'drupal_dev',
    'username' =&gt; 'drupal',
    'password' =&gt; 'drupal',
    'prefix' =&gt; '',
    'host' =&gt; 'db',
    'port' =&gt; '3306',
    'namespace' =&gt; 'Drupal\\mysql\\Driver\\Database\\mysql',
    'driver' =&gt; 'mysql',
    'autoload' =&gt; 'core/modules/mysql/src/Driver/Database/mysql/',
  );
  </code></pre>
  <p>What is interesting is that it is the <code>civicrm</code> user that has the access violation, not <code>drupal</code> (as it always used to be when I had a new CiviCRM custom field and forgot to update the <code>civicrm</code> table array in <code>settings.php</code>...)</p>
  <p><strong>I can solve this problem easily by simply giving the <code>civicrm</code> database user SELECT rights to the <code>drupal</code> database</strong>, as I did for the <code>drupal</code> database user on the <code>civicrm</code> 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.</p>
  <p>So is this a poorly configured View in D9, a missing step or incorrect config, or have I stumbled on an issue?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-08-21T19:18:21.447Z
Comments.Text: 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
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-08-21T19:21:09.633Z
Comments.Text: 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.
Comments.UserDisplayName: jackrabbithanna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-08-22T08:27:23.217Z
Comments.Text: 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
Comments.UserDisplayName: ChumKui
Tags: <drupal-views><civicrm-entity>
Title: Drupal 9 View unable to display logged in User
ThreadText: # Drupal 9 View unable to display logged in User
  
  (Asked by: username_0 on 2023-08-21)
  
  I have an issue that I think relates to this question:
  
  [Drupal 9 View Shows Permission Denied For CiviCRM Tables](https://civicrm.stackexchange.com/questions/39830/drupal-9-view-shows-permission-denied-for-civicrm-tables)
  
  and possibly this:
  
  [Views integration error on D9](https://www.drupal.org/project/civicrm_entity/issues/3198146)
  
  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](https://docs.civicrm.org/sysadmin/en/latest/integration/drupal/views/#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?
  
  **Tags:** drupal-views, civicrm-entity
  
  > **Comments:**
  > **username_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  
  > **username_1**: 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.  
  > **username_0**: 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  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2023-08-22. Score: 3
  
  As per the comment by username_1, 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!
  
  
  ---
  
  