By Thierry Moreau on

This is a database question for a security concern.

I would like my site to have all administrative actions done in https, and http for normal user interactions.

I'm looking for a scheme where there is little doubt than the https mechanisms can't be bypassed.

Here is my idea. Using Apache and mySQL. Two virtual hosts, respectively https and http for admin access and normal user access. Pointing to the same database, but with different database user, e.g. respectively wwwadmin and wwwnormal. Use default Drupal installation permissions for wwwadmin, but restrict wwwnormal permissions to SELECT on every tables, and INSERT, UPDATE and DELETE only on those tables (or even columns) that require such access.

This does not spare me the task of setting Drupal roles and access control correctly, but it gives assurance that 1) an excessively liberal access allowed to normal users would be catched by the database permission mechanism, and 2) a hacker who guesses the Drupal admin password can't perform the administrative actions unless she also hacks the https authentication mechanism (which I don't specify in this post).

Here are my four questions:

  1. Is there a starting point for the determination of the appropriate permissions for wwwnormal in the above scheme?
  2. If the mySQL permissions are erroneously too restrictive for wwwnormal, is it possible that the Drupal logic leaves the database in a messy state (i.e. database not properly rolled back as one would expect from top quality database application logic)?
  3. I suppose the task of determination of appropriate permissions is to be performed for any Drupal module that extends the database schema. Am I correct?
  4. Any advice from Drupal experts about this scheme?

Thanks in advance.

⋅ Categories: Drupal 5.x

Comments

Thierry Moreau’s picture

Thierry Moreau commented

A follow-up on my post.

OK, I experimented with the idea of tailored permissions for ordinary users. Below is the MySQL script for permission adjustments.

While experimenting, I created a database inconsistency, i.e. a session counter not updated for a comment that got inserted. This is an (expected) instance of a less than perfect START TRANSACTION / COMMIT WORK statement pair. Is the Drupal code base intended to have clean database transactions?

Basically, the concept seems workable if security is a must. The challenge is to collect the detailed MySQL permissions required for every Drupal module permissions that an ordinary user might have.

- Thierry Moreau

revoke all privileges, grant option from 'drupalparticip'@'localhost';

/* basic read-only access */

grant select
    , lock tables
on drupal5test.*
to 'drupalparticip'@'localhost';

/* cache mechanism */

grant insert, update, delete
on drupal5test.cache to 'drupalparticip'@'localhost';

grant insert, update, delete
on drupal5test.cache_filter to 'drupalparticip'@'localhost';

grant insert, update, delete
on drupal5test.cache_menu to 'drupalparticip'@'localhost';

grant insert, update, delete
on drupal5test.cache_page to 'drupalparticip'@'localhost';

/* ??? */

grant update
on drupal5test.history to 'drupalparticip'@'localhost';

/* user login and logout */

grant update(timezone,language,picture,status,access,login,init,data)
on drupal5test.users to 'drupalparticip'@'localhost';

grant insert, update
on drupal5test.watchdog to 'drupalparticip'@'localhost';

grant insert, update, delete
on drupal5test.sessions to 'drupalparticip'@'localhost';

/* sometimes, sequence numbers are needed by a module */
grant insert, update, delete
on drupal5test.sequences to 'drupalparticip'@'localhost';

/* Our ordinary user is allowed to post comments */
grant insert, update, delete
on drupal5test.comments to 'drupalparticip'@'localhost';
grant insert, update
on drupal5test.node_comment_statistics to 'drupalparticip'@'localhost';

flush privileges;