0

For a blog post, I have db design of 1 database in D7, the database contains multiple database users:

Anonymous - Only Read privilege on Database
Writer - CRUD privilege on Database
Admin - Has all privilege to Database

How do I achieve this in Drupal 7 ? The settings.php in Drupal has only one database connection section, as I'd need to connect the database from all 3 database users, and change the database user when they log in, like when Admin logs in, he'll also be admin database user, and so on...

0

I haven't ever tried this so it's just theory...

  • Define 3 different connections in settings.php, with the relevant credentials. The default database should login as the user with only read access.
  • The connection key for the other 2 should be the name of the relevant Drupal role. (in reality they can be anything you choose to map against but for this example...)
  • Write a custom module to implement hook_boot(), with code like this:

    function MYMODULE_boot() {
      global $user;
      if (in_array('admin', $user->roles)) {
        db_set_active('admin');
      }
      elseif (in_array('writer', $user->roles)) {
        db_set_active('writer');
      }
      else {
        db_set_active(); // Use the default
      }
    }
    

As I say I've never tried that but it feels like it would work. I'm also not sure it would be considered 'best practice'. The code could be made a lot more generic as well but I'll leave that up to you :)

You need to bear in mind that unless you lock down Drupal's own permissions to match those of the DB CRUD permissions exactly, your users may well get exceptions when they (or indeed the system on their behalf) tries to write to the database.

Actually I'm thinking this out as I'm going along and I'm not sure a situation exists where Drupal wouldn't try to write to the database during a bootstrap. I'll leave this code in as an example in case you manage to get it to work, but my gut instinct is you might be out of luck here.

  • Thanks Clive, I'll try it... I have seen cache data being inserted/updated/deleted from Database, I will have to watch carefully which tables are used for common Drupal core/Cache/Bootstrap to give table level rights... But data security is the main concern here... – user12947 Jan 23 '13 at 22:57
1

Although the question is a little unclear, it sounds like you just need 3 roles in your Drupal install

  1. Administrator
  2. Writer
  3. Anonymous ( built-in )

You can add the roles at /admin/people/permissions/roles and configure access permissions for each role, regarding node creation etc, at /admin/people/permissions

  • Well, I might not have been clear, pardon my English... I already have these roles set up from D7 admin section. Though from front end it looks secure, database would always be vulnerable if I have only one user group with ALL RIGHTS in db, so, I want DB setup according to those roles. I have already made db_users like that of roles of Drupal, but couldn't connect all at once with Drupal, as it lets just 1 connection parameter. – user12947 Jan 23 '13 at 22:29
  • edited the question, to make it more clear.... – user12947 Jan 23 '13 at 22:40
1

You seem to make the assumption that, since the anonymous users are not allowed to create content, there isn't any database table that is written for anonymous users, which is not true. There are some database tables that are written, even when the user is the anonymous user, or another user who is not allowed to create any node; those tables include:

  • Cache tables
  • The cache_form table, which contains data about any form shown to the user, including the login form
  • The batch table, used when a module uses batch operations to complete an operation
  • The flood table, which is also used to register the IP of those users who try to login using a username, but a wrong password, too much times
  • The semaphore table, that is used to to coordinate long-running operations across requests
  • The sessions table, which contains session data

Drupal, and any module require write access to the database. Without that, Drupal doesn't work.

If you are concerned about security, you should be sure to use modules without security issues, and to update Drupal, or modules, when there are fixes for security issues.

  • I wonder why is it called cache when its stored in database, anyways, Drupal seems to work differently. Yes, I have noticed few tables which asks for Insert, Delete and Update for Anonymous users, but again table level privilege could be given to Anonymous for those tables. Do you really think configuring ALL PRIVILEGE database settings for Drupal and control your site from Roles would make your database secure ? – user12947 Jan 24 '13 at 12:19
  • There could be another option to give CRUD privilege for website users be it admin, writer or anonymous, and performing other database operations from CLI. – user12947 Jan 24 '13 at 12:22
0

Before we start, we must be aware of the fact that there are few tables of Drupal, which gets updated when an anonymous user makes request to Drupal 7:

The list goes below, on analysis, which requires more than SELECT privileges for an Anonymous user:

cache, cache_*, watchdog, semaphore, flood, sessions

Any Other depending on Contrib Modules you use, like ctools_css_cache Enable Query Log in your Database to observe what other tables require write access

To enable in Mysql:

SHOW VARIABLES like 'general%'; // To see global variable is enabled or not
SET GLOBAL general_log = ON;

Create Two users for your database, the one by default is the 'root' and the other of your choice like 'readuser'.

CREATE USER readuser identified by 'your password';
GRANT SELECT ON DATABASENAME.* TO readuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE_NAME.tablename TO readuser;

*Change DATABASE_NAME with yours and tablename with the list mentioned above.

Update Database Configuration in settings.php in the Drupal installation folder.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'database_name',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

$databases = array (
  'readuser_db' => 
  array (
    'default' => 
    array (
      'database' => 'database_name',
      'username' => 'readuser',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Multiple Database Configuration reference: https://www.drupal.org/docs/7/creating-custom-modules/howtos/how-to-connect-to-multiple-databases-within-drupal

Create a Custom Module and name it db_user_switch

create two files: db_user_switch.info and db_user_switch.module

db_user_switch.info:

name = "DB USER SWITCH"
description = "A Module to Switch Database Users."
core = "7.x"

db_user_switch.module:

// hook_init implementation
function db_user_switch_init(){
    if (!user_is_logged_in()) {
        db_set_active('readuser_db');
    }
    else {
        db_set_active();
    }
}

// hook_form_alter implementation
function db_user_switch_form_alter(&$form, $form_state, $form_id) {
  switch ($form_id) {
    case 'user_login_block' :
      array_unshift($form['#submit'], 'db_switch_onlogin');
      break;
  }
}

//Custom Login Form Submit Handler
function db_switch_onlogin($form, &$form_state) {
  db_set_active();
}

Enable the module and test, it should work.

For Reference see other posts: https://gist.github.com/CashWilliams/901f16e32b3d9f77a15f What MySQL privileges would a read-only drupal db user need

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy