Security Recommendations for MS SQL Database

If you are particularly concerned about the possibility of SQL injection against your database, you can 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 could prevent the execution of certain types of SQL injection attacks, however it is not seen as a necessity since the ASP source code already contains methods to sanitize data entered by users.
 

These queries can be run successfully as long as the database user that is employed in the ProductCart database connection string has permissions to create and drop tables.
 

The change in permissions consists in denying SELECT permissions for the ProductCart database user to the sysobjects and syscolumns tables in the MS SQL database. This measure specifically protects a database from SQL injection attacks.

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 ProductCart store, run these queries after setting up the ProductCart 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

Adverse Effects

Can this have any adverse affects? Not within ProductCart. However, if there are other applications that are using the same database and the same database user, and need access those two tables, then this will affect those applications and not allow them to run properly. If this is the case, setup a different user that is used solely for ProductCart and run the queries above for that user only.
 

In addition, you will not be able to view any of the tables through Enterprise Manager or SQL Management Studio (since those functions require the use of the sysobjects and syscolumns tables). It does not affect the use of the Query Analyzer, since it does not need to dynamically retrieve information from the system tables in order to run straight queries.
 

Finally, if you need to reverse these commands, you can execute the following queries:

Grant  select on sysobjects to DatabaseUser
Grant  select on syscolumns to DatabaseUser