troubleshooting Question

SQL Injection and Database permissions

Avatar of avaj
avaj asked on
ASPMicrosoft SQL Server
3 Comments1 Solution308 ViewsLast Modified:
My site was recently attacked with an sql injection.  I have followd the shopping carts steps to hopefully prevent further attacks (is there a way to test this?); the shopping cart also recommends that I change the db permissions.  I tried their recommendation but then lost the data base tables (have since had them restored by hosting company).  I am about to put the site back on but would like to know if I should first do what shopping cart suggested:

Overview
Run the following two queries to reduce permissions on two system tables for the database user that is used in the ProductCart database connection string (UID located in the connection string saved to the file includes/storeconstants.asp). This measure can prevent the execution of certain types of SQL injection attacks, should vulnerable code exist in the ASP source code.

Instructions
The commands to run in query analyzer are:

deny select on sysobjects to DatabaseUser
deny select on syscolumns to DatabaseUser
& where DatabaseUser corresponds to the UID value in the database connection string in your storeconstants.asp file.

If you are setting up a new  store, run these queries after setting up the  database.

Troubleshooting
If you receive an error similar to the following:

Msg 4604, Level 16, State 1, Line 1

There is no such user or group 'YOURUSER'.
This is probably due to the fact that the user YOURUSER belongs to the default database role called Public.

You can get around the problem by running the same queries with Public instead of the user name used in the connection string. Therefore, the queries become.

deny select on sysobjects to Public
deny select on syscolumns to Public

I ran the two lines above in the analyzer and lost all the tables.  Did I just do something wrong - should I run the 2 lines above again?
ASKER CERTIFIED SOLUTION
avaj

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros