[pmwiki-users] most user-friendly method of database configuration

[Crisses]

On Oct 15, 2006, at 4:21 PM, Ben Stallings wrote:

> Crisses wrote:
>> That has to fall on the shoulders of the database admin, too.  I  
>> could create separate unpriviledged users -- AND SHOULD -- to  
>> protect the database.  Then I need a way to have multiple database/ 
>> user combos in the wiki.
>
> I'm not sure I understand the argument for restricting database  
> access via multiple connections.  I built a number of database  
> front ends with UpdateForm, using two lines of defense: the  
> UpdateForm recipe was only enabled for page groups that had edit  
> passwords, and the recipe is unable to do any damage worse than  
> deleting a single record at a time -- it can't modify or delete  
> tables because it simply doesn't know those SQL commands.
The current AuthUserDBase is read-only (SELECT).

When I merge XesAuthUserDBase, people will have the option of using  
AuthUserDBase to create a stand-alone user auth system.  That  
optional feature will require write access to the database  (INSERT,  
UPDATE, DELETE).

If someone wants to be paranoid, they can have a user that is  
unprivileged (SELECT).  Another that has privileges (SELECT, INSERT,  
UPDATE, DELETE).  They can assign the database users to recipes based  
on the level of privileges needed for the recipe.  You're saying your  
recipe doesn't USE update/delete thus there's no danger of it being  
abused... but there's even less danger if the database user making  
the connection doesn't have those privileges.... and that's great  
protection against some SQL injection attacks.  You may write a rock- 
solid recipe, with no vulnerabilities but maybe I was sloppy.  So  
different users with different table/access privileges is another  
possible use for allowing admins fine access control.

Is it 100% necessary? no.  But while we're discussing different  
database connection info for admins, and consolidating the info, this  
is another side-benefit of not assuming every recipe uses the same  
database/user combo.  The benefit of what we've been discussing is  
that every database would be configured the same way in config.php,  
and the admin decides whether or not to have one database for all the  
recipes, or to have different info sent to each recipe.  However,  
only one method of setting them up is always going to be better than  
having to look up the different particulars for every recipe.

> I probably should have set up a restricted database user, but the  
> only way I can think of that dangerous SQL commands could be sent  
> is if my config.php (containing the fully privileged username and  
> password) got out, which would mean the FTP password was already  
> out, in which case the whole site is basically toast anyway.

Or the next time the server admin upgrades the database program  
itself, and it happens to have a bug/vulnerability in it....

> With DataQuery the same security will be in effect: you will only  
> be able to update a record if you have editing privileges for the  
> virtual page, and the recipe will not know the SQL commands for  
> modifying or deleting tables.   I'm also writing in another level  
> of defense, in that entire tables can be specified as off-limits to  
> the recipe (as well as fields within tables, which is already  
> possible with SelectQuery).

*joke warning* a REAL paranoid linux admin would want at least 3  
levels of defense. ;)

> So I'm having trouble imagining a situation where it would help  
> matters to have a restricted database connection.  Maybe this  
> failure of imagination explains why I was never a hacker.  Can you  
> help me out? --Ben S.

I'm not a hacker -- I'm the aforementioned paranoid linux admin.  I  
had one box hacked that was in my charge by someone who was  
thankfully ignorant and thought an rpm would work on a Debian box --  
hosed Bash, thus crashing the entire machine, but leaving his trail  
all over the machine.  In any case, this one hacked SSH by a sheer  
brute force attack on a user I never knew was even on the box.

SO.

The only moral of the story is "Hey, you never know."    It was only  
hacked by sheer luck, and by me not being paranoid enough -- I should  
have had that box locked down better (restrict SSH to specific users,  
denyhosts, etc.).  Worse yet, it was a paid job.  I'm only one admin  
(with some 70 trains of thought), and can only do so much.  I'm up  
against some several thousand -- maybe hundred thousand? -- people  
with a lot of time on their hands, and something to prove.  And the  
means to automate their attacks.  Every avenue I close off at least  
reduces their chances of screwing with my equipment (data,  
website...) by a considerable factor.  Wikis are particularly tasty  
for the hackers (and others) who make money via SEO.  YAY.  So we are  
actually targets.

Crisses