save contact with non numeric phone number gives access denied civicm_strip_non_numeric

Question

I recently update civicrm till 4.6.2 till 4.6.4 on drupal 7.38, mariadb 5.5.41. When saving a contact with a non numeric phone number (+32 123456798) I got the error:

Database foutcode: execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric', 1370

Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']
[type] => DB_Error
[user_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']"]

)

I found the solution in adding the "execute" privilege to my civicrm-db user in mariadb using phpmyadmin.

I have a second civicrm db (non related to my first) still on the old version 4.6.2 which has no issues at all. I has no "execute" privilege and does not need it.

Civicrm doc (http://forum.civicrm.org/index.php?topic=23489.0) does not mention execute privilege.

list of grants to my civicrm user: Grants for civicrm-db-user@localhost

GRANT USAGE ON *.* TO 'civicrm-db-user'@'localhost' IDENTIFIED BY PASSWORD 'pwdhere'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `civicrm`.* TO 'civicrm-db-user'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `civi`.* TO 'civicrm-db-user'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `civicrm-db`.* TO 'civicrm-db-user'@'localhost'

trigger rebuild did not fix the issue. (/civicrm/menu/rebuild?reset=1&triggerRebuild=1)

automatic_sp_priveleges variable = on in mariadb

Question: did I miss something in the update, does the latest version need different rights? I think I found a workaround, but not the actual error. Has someone the same issue?

Kind regards

Johan.

Answer 1

I checked a site that is currently being test-upgraded from 4.4.14 to 4.6.4, so we have both the 4.4.14 and 4.6.4 versions.

In 4.4.14: GRANT EXECUTE, ALTER ROUTINE ON FUNCTION mycividb.civicrm_strip_non_numeric TO 'myuser'@'localhost'

In 4.6.4: GRANT EXECUTE, ALTER ROUTINE ON FUNCTION mycividb.civicrm_strip_non_numeric TO 'myuser'@'localhost'

I.e. the same. This permission normally gets automatically granted for this function, when the function is created, which happens when triggers are set up. This is done automatically by MySQL:

The EXECUTE privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary

https://dev.mysql.com/doc/refman/5.5/en/stored-routines-privileges.html

From the MariaDB docs:

To run a stored routine, the EXECUTE privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the automatic_sp_privileges variable is set to 0.

https://mariadb.com/kb/en/mariadb/stored-routine-privileges/

Worth checking the automatic_sp_privileges variable.

Does a trigger rebuild (/civicrm/menu/rebuild?reset=1&triggerRebuild=1) fix the problem?

You say you solved the problem by adding the "execute" privilege to your drupal-db user. Did you mean your civicrm-db user? If you post the results of SHOW GRANTS for the relevant users, that might help to diagnose what's happening.

Answer 2

As far as I know you also needed the execute privilige in earlier versions for the phone strip function?

Answer 3

CiviCRM requires additional DB permissions beyond what you'd normally require for a CMS like WordPress, Joomla or Drupal.

When cloning a site for testing, the same permissions setup will need to be replicated for the cloned site. Just "stock" permissions won't do the trick :(

These permissions are documented in the CiviCRM Wiki: CiviCRM MySQL Permission Requirements

---

Select your MySQL version below, then execute the appropriate permissions SQL after replacing DBNAME, DBUSERNAME, DBHOSTNAME and DBPASSWORD to suit your access.

MySQL >= 5.1.6

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE 
   ON DBNAME.* TO 'DBUSERNAME'@'DBHOSTNAME' IDENTIFIED BY 'DBPASSWORD';

MySQL <= 5.1.5

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE, ALTER ROUTINE 
   ON DBNAME.* TO 'DBUSERNAME'@'DBHOSTNAME' IDENTIFIED BY 'DBPASSWORD';
GRANT SUPER ON *.* TO 'DBUSERNAME'@'DBHOSTNAME';