CiviCRM Community Forums (archive)

Hi fellows,

I'm preparing for a new installation of CiviCRM 4.x on a Drupal 7.x.

I'm a bit of a perfectionist, and I need to make sure that I provide my CiviCRM MySQL Database user minimal (but enough) permissions to the civicrm database.

I read the installation guide, and I understand that the TRIGGER and SUPER permissions might be needed under some situations, but I would love to have an exclusive list of permissions needed, instead of just doing a "GRANT ALL PERMISSIONS on `civiccrm`.*" to the user.

It Would even be great if a list is available for two separate scenarios:

• Privileges needed for the installation.
• Privileges needed for normal operations (post installation).

Thanks guys in advance.

P.S: I think I did my research (and RTFMing) before sending this

Hi Donald and All,

I went through this exercise and below are my findings for the permissions required for the installation. Here's how I approached it:

The Approach:
I began with only a simple SELECT privilege, and then gradually added permissions when needed as follows:

• The requirements needed by the CiviCRM installer (the "Check Requirements" button).
• Running the installation and granting permissions if the installation fails.
• Adding any privileges that should be there by common sense (namely: DELETE).
• And finally adding privileges mentioned explicitly in the Installation guide (namely: TRIGGER and SUPER).

The Results:

• SELECT: I started with only SELECT privilege (The installer's check will also fail if it doesn't exist, as it won't be able to verify the existence of the database in the first place).
• CREATE TEMPORARY TABLES, LOCK TABLES: Required by the module installer (as part of the installer's check)..
  Note: The installer's check succeeds at this point, though other permissions are required. I think this should be reported as a bug/improvement.
• DROP, CREATE, ALTER: Installation fails miserably if not granted (As the installer's trying to drop tables, create tables, then -I think- create foreign keys).
• INSERT, UPDATE: Installation fails if not granted (As the installer's trying to populate tables, they are also needed for CRUD operations after installation).
• DELETE: I added it by common sense (since it will be needed for CRUD operations).
• TRIGGER: Required to enable logging and multi-lingual support (Mentioned explicitly in the installation guide -OPTIONAL-).
• SUPER: Required in MySQL 5.1 if binary logging is enabled (Mentioned explicitly in the installation guide -OPTIONAL-). Please note that this is a Global/Administrative permission, so it should not be granted on a DB level.

The SQL:
So, to cut the long story short, here is the SQL you need to run:

1. Grant DB privileges to the 'civicrm' user, on the `civicrm` database (Privileges ordered more logically).
GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER ON `civicrm`.* TO 'civicrm'@'localhost';
2. Grant Global privileges to the 'civicrm' user.
GRANT SUPER ON *.* TO 'civicrm'@'localhost';
For the sake of completeness, let's also mention the minimal privileges needed for the 'drupal' user, on the `drupal` database (Detailed in the Drupal installation guide).

3. Grant DB privileges to the 'drupal' user on the `drupal` database.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `drupal`.* TO 'drupal'@'localhost';
4. Grant DB SELECT privileges to the 'drupal' user on the `civicrm` database.
_{If you plan to use the Drupal Views module to display CiviCRM data within your Drupal pages, and if you are going to use separate databases for Drupal and CiviCRM, you need to ensure that your Drupal database user has SELECT permissions for your CiviCRM database.}
GRANT SELECT ON `civicrm`.* TO 'drupal'@'localhost';
And that should be it (if something blows up into my face while running CiviCRM, I'll make sure I update this post) .


Notes:

• Now to make life easier, I suggest improving the installer's check to test for the exact privileges needed, and report a failure if a privilege is missing, and perhaps even report a warning if extra privileges exist.
  
• Comparing Drupal's permissions to CiviCRM's permissions, the difference is an extra "INDEX" permission in Drupal. (this might be the one that will blow into my face).
  
• Perhaps further investigation is needed to confirm that the permissions required for installation is the same as those needed for operation (Do civicrm and/or drupal create and drop tables on a regular basis for normal operational features? I honestly doubt it). I might dig into this in the near future.
  

Sorry for the long post everybody, I hope it helps
Thanks
Hany el-Kerdany