Answers.Body: <p>So the issue was not with CiviCRM it was with a security issue with MySQL. Here is the response from the hosting provider.</p>
  
  <p>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.</p>
  
  <p>The problem is that remote connections are enabled for the saintfran user to the saintfran_drupal database. Therefore, the user is stored as <code>saintfran</code>@<code>%</code> (% is a wildcard for all hostnames). This is also how the VIEW was created:</p>
  
  <p>CREATE ALGORITHM=UNDEFINED DEFINER=<code>saintfran</code>@<code>%</code></p>
  
  <p>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.</p>
  
  <p>There are several solutions:</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-09-11T16:44:48.583Z
Answers.LastActivityDate: 2017-09-11T16:44:48.583Z
Answers.UserDisplayName: frTommy
Body: <p>Drupal 7, Civi 4.7.24
  *******Updated at bottom*********</p>
  
  <p>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:</p>
  
  <pre><code>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-&gt;commonProcess((Array:3))
  #2 /www/www/sites/all/modules/civicrm/CRM/Core/Form.php(447): CRM_Admin_Form_Setting_Component-&gt;postProcess()
  #3 /www/www/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form-&gt;mainProcess()
  #4 /www/www/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine-&gt;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-&gt;perform(Object(CRM_Admin_Form_Setting_Component), "next")
  #6 /www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller-&gt;handle(Object(CRM_Admin_Form_Setting_Component), "next")
  #7 /www/www/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page-&gt;handle("next")
  #8 /www/www/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller-&gt;run()
  #9 /www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Utils_Wrapper-&gt;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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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?</p>
  
  <p>****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?
  <a href="https://i.sstatic.net/Qslg4.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Qslg4.png" alt="Sceenshot of phpmyadmin view edit"></a></p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-09-11T13:35:56.157Z
Comments.Text: MySQL does recommend using INVOKER rather than DEFINER in Stored Routine or View see https://dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html
Comments.UserDisplayName: frTommy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-09-11T15:29:23.040Z
Comments.Text: The check/creation is in CRM_Case_BAO_Case in createCaseViews and createCaseViewsQuery
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-09-11T15:31:29.763Z
Comments.Text: I finally found that. The creation code doesn't specify Invoker/Definer so it defaults to Definer. Now I'm working with the hosting provider support to see why the definer isn't being allowed permission when it should be.
Comments.UserDisplayName: frTommy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-09-11T16:21:08.143Z
Comments.Text: It's looking like it was an issue with the hosting provider. Stay tuned.
Comments.UserDisplayName: frTommy
Tags: <civicase><error>
Title: Unable to enable CiviCase - Definer v Invoker problem maybe
ThreadText: # Unable to enable CiviCase - Definer v Invoker problem maybe
  
  (Asked by: username_0 on 2017-09-11)
  
  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?
  
  **Tags:** civicase, error
  
  > **Comments:**
  > **username_0**: MySQL does recommend using INVOKER rather than DEFINER in Stored Routine or View see https://dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html  
  > **username_1**: The check/creation is in CRM_Case_BAO_Case in createCaseViews and createCaseViewsQuery  
  > **username_0**: I finally found that. The creation code doesn't specify Invoker/Definer so it defaults to Definer. Now I'm working with the hosting provider support to see why the definer isn't being allowed permission when it should be.  
  > **username_0**: It's looking like it was an issue with the hosting provider. Stay tuned.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2017-09-11. Score: 2 | Accepted answer
  
  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.
  
  
  ---
  
  