Future Request: Admin and Public MySQL Users

[KT Walrus]

For a bit of added security, I'd like to have the ability to have two separate MySQL user ids/passwords (one that only has Data MySQL privileges and the second that has both Data and Structure privileges) and have the IP.Board software take care in choosing which user id to use to connect to the database. Hopefully, only the Admin would need to connect with the user id that has Structure privileges and then, only when the code needs the extra privileges. This might give slightly better security.

Administrators that don't want to bother with this could just use their existing MySQL id during the initial board installation, providing it for both ids.

[Wolfie]

I know that in Linux, as root, you can specify users for MySQL, but is it really possible for the average joe to be able to set up an SQL database to have user level access without the ability to perform certain functions? If not, then this wouldn't be a request that IPB could do.

[KT Walrus]

Every host I've used has allowed you to create multiple MySQL users and assign the MySQL privileges for that user. It is very simple in phpMyAdmin. If they don't, then it is a simple task to just use the single MySQL user to be both the Admin user and the Public user.

[Matt]

Yeah, we have considered this. It wouldn't take too much effort to do although arguably each user will have access to the same data so I'm not entirely sure what the point would be. Both need access to members, posts, topics, forums, logs, etc. I can't think offhand of one table that isn't required by both systems.

[Jaggi]

[quote name='Matt' date='06 August 2009 - 08:01 AM' timestamp='1249542094' post='1838996']
Yeah, we have considered this. It wouldn't take too much effort to do although arguably each user will have access to the same data so I'm not entirely sure what the point would be. Both need access to members, posts, topics, forums, logs, etc. I can't think offhand of one table that isn't required by both systems.


the ones that start admin_ :P.

tbh i always setup a lesser user in mysql anyways which i run my forum on and then use phpmyadmin to do higher tasks or anything. I can see advantages to this but would require some quite big changes in the way ipb is setup.

[Guest]

I think the point is less what data the user has access to, and more what privileges the user has. The public-side MySQL user account shouldn't have access to alter/drop tables, truncate, etc.

That would be pretty cool. :)

[Mark]

But how would the system know if a user is in the ACP before it initiates the database connection? It has to check the admin_sessions table to verify if a user is in the ACP.

[Michael]

[quote name='Μark' date='06 August 2009 - 07:03 AM' timestamp='1249556600' post='1839047']
But how would the system know if a user is in the ACP before it initiates the database connection? It has to check the admin_sessions table to verify if a user is in the ACP.

Check the 'IPB_THIS_SCRIPT' constant, it is set to either 'public' or 'admin' before anything else gets done.

[Mark]

[quote name='Μichael' date='06 August 2009 - 01:36 PM' timestamp='1249562216' post='1839073']
Check the 'IPB_THIS_SCRIPT' constant, it is set to either 'public' or 'admin' before anything else gets done.


Yes, but that would mean that the elevated privileges account would be used before the user authenticates as an admin.

[bfarber]

Furthermore, I don't think most hackers run DROP TABLE - they just run DELETE FROM TABLE, which both would need to use, and which causes just as much damage realistically.

I'm not sure you'd actually be preventing anything in a real-world scenario here.


Now, it *could* be useful to have two logins if you have master/slave setup. One for a read database, and one for read/write database. Push select/show queries to the read-only database, everything else to the normal updatable database (which handles updating the read-only slave). But that's more for performance than security.