2

Most sysadmins would understand the risk of destructive commands like DROP or DELETE being executed by hackers to the SQL databases they manage. Is there a way to completely disable such commands from being executed, maybe by configuring MySQL / MS SQL / PostgreSQL, or modifying the source code if they are open source, deleting those dangerous features.

 Once you have your webapp online,
 would someone with honorable intentions
 ever need to DROP a table?!

This could be the holy grail solution to "primary" DB security, instead of running around trying to prevent these wholly unnecessary commands. Of course during development or maintainable periods they could be re-enabled, then turned off once not needed.

3
  • I've seen a lot of discussion about whether or not SQL servers should not execute DELETE FROM statements unless there is a WHERE clause (and if you want to delte everything, just put WHERE 'a'='a' or someother true statement). This way the command is there when its needed, but is much more difficult to abuse by accident Aug 12, 2009 at 23:58
  • Make sure you are really aware of potential growth issue. I hope you have a system where you want to keep every single record forever.
    – EBGreen
    Aug 12, 2009 at 23:58
  • The TRUNCATE TABLE command was always my favorite when demonstrating the dangers of SQL injection attacks. The classic one liner "; TRUNCATE TABLE--" typically scared the living hell out of novice programmers the first time they saw it in action. Lesson learned. ;-)
    – KPWINC
    Aug 13, 2009 at 3:58

6 Answers 6

6

DDL Triggers in SQL Server 2005 can disable tables from being dropped or modified.

If you limit your interaction with the database to stored procedures, you don't have to worry about this. The app account can only run stored procs - so there will be no permissions for sql injection.

If you can't do that, then you can remove rights to delete and do any type of DDL. That is better than nothing. But if someone wants to update all of your data with Nulls they can.

You will have a backup that is kept up to date if you have valuable data, or you will find yourself out of a job/business.

2

You don't need to modify db code because this already exists. It's just that people don't use it. Most web-apps need at most the ability to Insert, Select, Update, Delete, and Create. Many install processes do walk your through the process of using a privileged user to create the needed accounts and db's with minimum privileges. IIRC mediawiki does this pretty well.

However I can't count the number of web-apps I've found where the web-app db user has full privileges to its db or worst yet to the whole db installation. Additionally some apps are built in ways that require too many privileges. Or don't make use of internal privileged and non-privileged accounts for admin functions.

2

Sounds like you might want to check out a database firewall. GreenSQL is an open source one: http://www.greensql.net/

Cheers

1

Yes, when my database is production I might need to drop temporary tables I created.

If you are really paranoid you should probably have your developers use views and stored procedures so that the account that normally has access to the tables cannot actually directly manipulate and instead has to access them via a view/procedure.

At least with mysql you don't have to give accounts the ability to create and drop tables. Take some time and look at the permissions system and simply limit your accounts correctly.

Another option would you could use at the cost of performance would be to run all access through Mysql Proxy with a paranoid filter that blocks everything that you don't like.

1
  • +1 for the laugh "paranoid filter" gave me! Aug 13, 2009 at 0:28
1

This is no different to file access. The user account should have the absolute minimum rights required to do the job. Permissions such as drop should only be granted to an admin level account, unless it's absolutely unavoidable, in which case the application design should perhaps be reviewed. Delete is frequently required but should be restricted to only those tables where it is required.

The code should not need to be modified if care and common sense is used when granting permissions. Complete removal of potentially dangerous commands may well result in an unmaintainable database system.

0

What we have done is add everyone to the db_denydatawriter role (SQLServer only) which removes any insert, update and delete permissions.

We then control all our access to the database for our web users through stored procedures. Provided you don't give them permission to create their own stored procedures and provided you don't allow them to execute arbitrary SQL within the stored procedures you are pretty well locked down.

This goes beyond not being able to drop tables but if you aren't using stored procedures everywhere then you could face a big battle converting your code.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .