SQL privileges needed for CMSMS?

[martin42]

Just locking down my RDBMS...

Am I right in thinking that:

1) public users without content editing privs, will only need SELECT privileges on the RDBMS?

2) content editors will only need SELECT, INSERT, UPDATE, DELETE privileges?

I assume that install & upgrade scripts need ALL privileges.

[martin42]

For anyone interested in minimizing user privileges to the RDBMS, here's a setup that seems to work.  I'm not an expert on PHP or SQL though...

1) The database itself (and its tables) is owned by a DBA person's account, not by an account that CMSMS uses. That way the CMS doens't inherit privileges it doesn't need.

2) The public CMSMS website accesses the database using an account that only has SELECT privilege on the tables and the sequences.

3) The content editor's CMSMS website accesses the database using an account that only has SELECT, INSERT, UPDATE, DELETE privs on the tables, and SELECT, UPDATE privs on the sequences.  (Remember to grant ALL privileges during upgrades.)

To make the public and content editor's CMSMS websites use different database credentials, I run two instances of the web server under different Unix user accounts (so that the public user can't see the /admin scripts). Then I use a special config.php to select the right settings based on Unix user id:

Code: Select all

<?php
$userid = posix_getuid();
include_once('config'.$userid.'.php');
?>

You can do the same thing with fileloc.php.  The point of all this is that it lets you protect the content editor's CMSMS website so that public internet users can't connect to it (e.g. firewall it off to authorised users only, or run it over HTTPS and set a password on the web directory).

Hopefully this will reduce the risk of pubic website users defacing the website or compromising the web server through the database, if there should be some lurking security bug somewhere in the CMSMS (always hard to be sure you've found them all).

If anyone's using MS SQL Server, then also:
  - disable xp_cmdshell etc;
  - stop SQL Server from running as LOCAL SYSTEM ;
  - don't let CMSMS connect to the database as user 'sa'.
Hope this helps.

[calguy1000]

Removing Create privilege from the administrator or content editor may cause problems with various modules, for example with Feedback form, which, if I remember correctly creates a new table for each form that is created.