Answers.Body: <p>Your objective is to protect the internal sensitive data from an attack on the public. An option is not to configure the defence on database level but to separate both sites (and run them on separate servers), and connect them with the CiviCRM API. To restrict the allowed API calls you can use <a href="https://docs.civicrm.org/civiproxy/en/latest/" rel="nofollow noreferrer">CiviProxy</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-05T11:37:08.867Z
Answers.Text: I did look into these options, but the site has been in development for some time and is now is set up as a multisite, and it's probably too late to change that now. (My question was misleading about that, sorry - edited now). At the time when i was doing the research, CiviProxy and remote API calls in general were not ready. I asked a question about it at the time https://civicrm.stackexchange.com/questions/19737/best-way-to-integrate-two-sites-with-different-security-levels and decided to go with the db user option on advice from the answers there - oh I've just seen one of them was yours!
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-05T08:43:26.290Z
Answers.LastActivityDate: 2018-07-05T08:43:26.290Z
Answers.UserDisplayName: Kainuk - CiviCooP
Body: <p>I have a Drupal multisite setup with two sites connected to the same civicrm database. One site needs access to all of the data as usual. But I would like to hide some data from the other site, so that it accesses the DB on a "need to know basis". There are lots of custom fields for example that contain sensitive data, that the second site will never use.</p>
  
  <p>I would like to secure this at the mysql level by letting the second site connect using a different DB username and to grant that user only the necessary privileges.</p>
  
  <p>It would be nice if I could do this in a subtractive way - first <code>grant all privileges on civicrm.*</code> and then remove access to the sensitive tables. However this does not work in mysql. You have to grant permissions to all the tables one by one if you don't want to include them all. So I did that, and granted SELECT, INSERT, UPDATE and DELETE on all the tables. But there were problems before I even began <code>revoke</code>ing anything. The event registration form was missing some fields, for example.</p>
  
  <p>I tried adding a few other permissions - LOCK_TABLES, EXECUTE ... but clearly I am missing something.</p>
  
  <p>What permission which is necessary for civicrm to work is missing from that list but is granted when you <code>grant all privileges</code>?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-07-04T16:16:20.317Z
Comments.Text: Two issues to consider:
  
  1. Civi must have privileges to create and access temporary tables.
  
  2. The civicrm_custom_field table (the list of the defined custom fields) is going to be common to both installations. if a particular table of custom field values (civicrm_value_*) is not accessible to one of the Civi installations, then I would expect a wide variety of queries will fail rather than simply producing null values.
  
  i would consider achieving your goal with hooks rather than DB privileges.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-07-04T19:42:26.193Z
Comments.Text: Can you clarify why the 'sub' site needs access to the db at all - or is this just your approach to limiting what they can see via the UI?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-07-04T20:03:25.607Z
Comments.Text: The sub site is a public website where we want to take donations, newsletter sign-ups and event registrations. The main site contains a lot more data including case work records which are largely stored in custom fields and do not have anything to do with the data that the public site needs to access. I don't want to just limit what they see in the UI - I've done that already. I want to ensure that if a hacker gets in and manages to access the database through a vulnerability in the public site - for example if they gain access as a Drupal administrator - they won't see client case notes.
Comments.UserDisplayName: naomi
Tags: <drupal><permissions><mysql>
Title: How can I restrict access to some CiviCRM tables at the database user level?
ThreadText: # How can I restrict access to some CiviCRM tables at the database user level?
  
  (Asked by: username_0 on 2018-07-04)
  
  I have a Drupal multisite setup with two sites connected to the same civicrm database. One site needs access to all of the data as usual. But I would like to hide some data from the other site, so that it accesses the DB on a "need to know basis". There are lots of custom fields for example that contain sensitive data, that the second site will never use.
  
  I would like to secure this at the mysql level by letting the second site connect using a different DB username and to grant that user only the necessary privileges.
  
  It would be nice if I could do this in a subtractive way - first `grant all privileges on civicrm.*` and then remove access to the sensitive tables. However this does not work in mysql. You have to grant permissions to all the tables one by one if you don't want to include them all. So I did that, and granted SELECT, INSERT, UPDATE and DELETE on all the tables. But there were problems before I even began `revoke`ing anything. The event registration form was missing some fields, for example.
  
  I tried adding a few other permissions - LOCK\_TABLES, EXECUTE ... but clearly I am missing something.
  
  What permission which is necessary for civicrm to work is missing from that list but is granted when you `grant all privileges`?
  
  **Tags:** drupal, permissions, mysql
  
  > **Comments:**
  > **username_1**: Two issues to consider:  1. Civi must have privileges to create and access temporary tables.  2. The civicrm_custom_field table (the list of the defined custom fields) is going to be common to both installations. if a particular table of custom field values (civicrm_value_*) is not accessible to one of the Civi installations, then I would expect a wide variety of queries will fail rather than simply producing null values.  i would consider achieving your goal with hooks rather than DB privileges.  
  > **username_2**: Can you clarify why the 'sub' site needs access to the db at all - or is this just your approach to limiting what they can see via the UI?  
  > **username_0**: The sub site is a public website where we want to take donations, newsletter sign-ups and event registrations. The main site contains a lot more data including case work records which are largely stored in custom fields and do not have anything to do with the data that the public site needs to access. I don't want to just limit what they see in the UI - I've done that already. I want to ensure that if a hacker gets in and manages to access the database through a vulnerability in the public site - for example if they gain access as a Drupal administrator - they won't see client case notes.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2018-07-05. Score: 1
  
  Your objective is to protect the internal sensitive data from an attack on the public. An option is not to configure the defence on database level but to separate both sites (and run them on separate servers), and connect them with the CiviCRM API. To restrict the allowed API calls you can use [CiviProxy](https://docs.civicrm.org/civiproxy/en/latest/)
  
  > **Comments:**
  > **username_0**: I did look into these options, but the site has been in development for some time and is now is set up as a multisite, and it's probably too late to change that now. (My question was misleading about that, sorry - edited now). At the time when i was doing the research, CiviProxy and remote API calls in general were not ready. I asked a question about it at the time https://civicrm.stackexchange.com/questions/19737/best-way-to-integrate-two-sites-with-different-security-levels and decided to go with the db user option on advice from the answers there - oh I've just seen one of them was yours!  
  
  ---
  
  