1

I am trying to implement Views, but when I want to create an elementary users view, I get the following error :

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'XYZ'@'localhost' for table 'civicrm_uf_match'

This, though I followed the Wiki : https://wiki.civicrm.org/confluence/display/CRMDOC/Views3+Integration

Here under is a partial copy of my settings.php file where I have copied the requested info :


$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'paroisse_drup275', 'username' => 'xyz', 'password' => 'abc', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => 'dr_', ), ), );

$databases['default']['default']['prefix']= array( 'default' => 'dr_', 'civicrm_acl' => 'paroisse_civicrm.', 'civicrm_acl_cache' => 'paroisse_civicrm.', 'civicrm_acl_contact_cache' => 'paroisse_civicrm.', 'civicrm_acl_entity_role' => 'paroisse_civicrm.', 'civicrm_action_log' => 'paroisse_civicrm.', 'civicrm_action_mapping' => 'paroisse_civicrm.', 'civicrm_action_schedule' => 'paroisse_civicrm.', 'civicrm_activity' => 'paroisse_civicrm.', 'civicrm_activity_contact' => 'paroisse_civicrm.', ... );


I am in CiviCRM 4.7.6, with Drupal 7.43

  • That error indicates a permissions problem. Can you open MySQL (or PhpMyAdmin etc.) and post the results of the command: SHOW GRANTS FOR xyz@localhost; Also - my guess is that you're using separate MySQL users for Drupal and CiviCRM. Can you run that "SHOW GRANTS" command for both the CiviCRM and Drupal MySQL user? – Jon G - Megaphone Tech May 3 '16 at 13:08
  • Hi Jon, I actually use 2 different DBs. Not sure to understand what you ask : when I ask SHOW GRANT FOR ... to each user, I get the following answer : "#1044 - Access denied for user 'abcdef'@'localhost' to database 'mysql'" (where abcdef is my cPanel username) ; but if I ask ("MySQL DataBases" in cPanel) what privileges for each DB, each of them provide all privileges to their pertaining user (ALTER, CREATE, ..., SHOW VIEW, UPDATE) (18 privileges) – Jacques-Marie Moranne May 7 '16 at 9:38
1

Based on your comment, I see that each database only provides privileges to their respective user - and it seems implicit that you're using different users for each database.

That means that your Drupal MySQL user doesn't have access to the CiviCRM database. For Views to work properly, the Drupal user must have permission to run SELECT statements on the CiviCRM database. From the documentation you linked: "If you have different database users for your CiviCRM and Drupal databases, then you need to grant SELECT access for the Drupal user to all the CiviCRM tables."

Please try granting this access and try again, you should be good to go!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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