default-deny in webapps?
This is … all about dbms’es — a topic I’m getting quite intimate with this semester (what with the whole “writing our own in class” thing and all).
I’m thinking … from a security perspective, default-allow is a bad thing, and default-deny is a good one — ie: you should have to explicitly enumerate the things that a system is allowed to do, and deny the ability to do any others.
So, I was thinking about how this relates to webapps and databases…
In most dbmses I can think of (mysql, oracle, postgres as far as I know), you have a very fine-grained per-database, per-table, even per-row privilege set. But more often than not, I think those privileges go ignored by developers developing applications that use it. Instead of defining per-table permissions, most apps expect full control of their databases.
Two good examples of this would be wordpress and cacti. WordPress hands you an install package that creates ~10 tables. It uses all of them, and internally updates them all, so requires at least the ability to modify the data stored in the table. You can’t restrict privileges below “full control of the whole db” without breaking wordpress. Same holds true of cacti. It ships with a .sql file that you run from the commandline as the cacti user on the cacti database. Cacti creates somewhere closer to 48 tables, and again needs admin on all of them.
Now, I grant that none of those pieces of software is particularly mission-critical, and that since both fall into the general neighborhood of multi-user content management (one way or the other), it doesn’t really matter too much. But I’m just thinking … why is it that an app that in general only needs select, update, and delete also require the ability to alter the table structure, drop tables, create temp tables? It seems … a little bit sloppy, you know?
Almost every db-driven webapp is the same way though. “Give me my own db with basically admin access, and I’ll do the rest”. Sure, it’s nice for ease-of-use… but … it just seems wrong, on some level.
March 5th, 2006 at 7:05 pm
The app we use at work that runs from a DB only has select access, directly. If it wants to run an update, it has to use a stored procedure. The SP will then have tons of checks to make sure that the app isn’t trying to do something stupid. There’s also a SP for inserting and deleting. Nothing in the app has drop / table mod privs, or even close.
It’s kind of a bitch to maintain, in that we have a hundred+ tables, so any insert/update on any of them has to have an SP, and any time we modify a column or something, the SPs have to be updated to match. However, it’s also very safe. It’s impossible to use the app to mess up the database.
Even if I go screw with the database myself outside of the app, I don’t have access to drop / create / modify tables. It seems a bit cumbersome to ask your DBA for something as simple as changing the length on a column, but our DBs sure won’t be breaking anytime soon.