2

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 revokeing 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?

Improve this question
3
  • 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.
    – BobS
    Commented Jul 4, 2018 at 16:16
  • 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?
    – petednz - fuzion ♦
    Commented Jul 4, 2018 at 19:42
  • 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.
    – naomi
    Commented Jul 4, 2018 at 20:03

1 Answer 1

Reset to default
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

Improve this answer
1
  • 1
    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 civicrm.stackexchange.com/questions/19737/… 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!
    – naomi
    Commented Jul 5, 2018 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.