Unable to enable CiviCase - Definer v Invoker problem maybe

Question

Drupal 7, Civi 4.7.24 *******Updated at bottom*********

I am unable to enable CiviCase. All other civi components are enabled. I was running 4.7.21 and attempted to enable it and got the following error:

CiviCRM_API3_Exception: "A fatal error was triggered: Could not create the MySQL views for CiviCase. Your mysql user needs to have the 'CREATE VIEW' permission"

#0 /www/www/sites/all/modules/civicrm/CRM/Admin/Form/Setting.php(200): civicrm_api3("setting", "create", (Array:1))
#1 /www/www/sites/all/modules/civicrm/CRM/Admin/Form/Setting/Component.php(111): CRM_Admin_Form_Setting->commonProcess((Array:3))
#2 /www/www/sites/all/modules/civicrm/CRM/Core/Form.php(447): CRM_Admin_Form_Setting_Component->postProcess()
#3 /www/www/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#4 /www/www/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Setting_Component), "next", "Next")
#5 /www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Setting_Component), "next")
#6 /www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Setting_Component), "next")
#7 /www/www/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
#8 /www/www/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
#9 /www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Utils_Wrapper->run("CRM_Admin_Form_Setting_Component", "Enable CiviCRM Components", NULL)
#10 /www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#11 /www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#12 /www/www/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:4))
#13 [internal function](): civicrm_invoke("admin", "setting", "component")
#14 /www/www/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:3))
#15 /www/www/index.php(21): menu_execute_active_handler()
#16 {main}

Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
A fatal error was triggered: Could not create the MySQL views for CiviCase. Your mysql user needs to have the 'CREATE VIEW' permission

I have verified that the user does have all the privileges. I'm hosted with CiviHosting and I asked Hershel to check it. He found the following:

There indeed appears to be a problem with CiviCRM. We edited the code briefly to determine
the precise error and the message CiviCRM gives is indeed not precise. The View in
question was created, but it's not usable. When we run this query:

SELECT count(id) FROM civicrm_view_case_activity_upcoming LIMIT 0, 25

MySQL gives this error:

 #1356 - View 'saintfran_civicrm.civicrm_view_case_activity_upcoming' references invalid
table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

which is more complicated and does point to a possible bug in CiviCRM. Not sure offhand
how to fix this, but one idea would be to manually remove the View, upgrade CiviCRM and
try again.

So I deleted the View, upgraded, and attempted to enable again with the same result. Any others have this issue or know how I might go about fixing it?

****UPDATE: I investigated the actual view in phpmyadmin. I copied the SQL code and just ran it directly and it worked without error. So I went back to the view and Experimented by changing the SQL Security field to INVOKER instead of DEFINER. The view ran without error. I went back and tried to enable CiviCase but got the same error. Checked the view and it had changed the SQL Security back to DEFINER. I have no idea what the DEFINER/INVOKER does or the implications but that seems to be the issue. Where in Civi Code does it create this view and do a check when enabling the civicase component?

Answer 1

So the issue was not with CiviCRM it was with a security issue with MySQL. Here is the response from the hosting provider.

The issue is caused by some specifics how the MySQL privilege system works. In MySQL a user account consists of both the user name and a host definition component. Access to databases can also be restricted to specific hostnames.

The problem is that remote connections are enabled for the saintfran user to the saintfran_drupal database. Therefore, the user is stored as saintfran@% (% is a wildcard for all hostnames). This is also how the VIEW was created:

CREATE ALGORITHM=UNDEFINED DEFINER=saintfran@%

However, remote connections are not enabled for the saintfran_civicrm database. Access to this database is only allowed from localhost. Therefore, only VIEWS with saintfran@localhost as definer can be executed. Unfortunately, the wildcard does not work in this case.

There are several solutions:

1) We recommend the one you have already found - use INVOKER instead of DEFINER. 2) Make sure that the user has no remote connections enabled when creating the VIEW. 3) Enable remote connections for the saintfran_civicrm database.