Question

Read Only access to a SQL Database? Best way?

SQL Server 2000, SQL Server security, SQL injection

We're trying to prevent hacks on our site. We've been down several times and the idea came up that we could prevent the SQL Injections by making an account for just the public-facing side of the site that has Read Only set for it. This is a Microsoft SQL 2000 server BTW.

Our hosting company configured such an account and it couldn't select tables (or so the error said) and after a day of checking things they said we needed to change our SELECT statements to specify the database and owner account directly in order to access this.

Example:

Old query string:

oRs.Source = "SELECT DISTINCT [slu].[state], [slu].[stateLong] FROM [tblStateLookup] [slu]...

New query string:

oRs.Source = "SELECT DISTINCT [slu].[state], [slu].[stateLong] FROM [dbdeckard].[acct567x].[tblStateLookup] [slu], ...

Question - is this really the easiest way to do this? We have HUNDREDS of lines of code that would have to be updated if so.

Any better ideas?

You shouldn't need to specify the database or the owner. Have them setup the default database of the new account to be your database (dbdeckard). Then use the sp_changeobjectowner procedure to change the owner of all your table objects from acct567x to dbo (database owner). That way the other account will be able to automatically find the tables (as will your read/write account). When you query for an object in SQL (in this case a table) SQL Server first checks for tables with your name as the owner, then if it doesn't find a table it checks to see if the table is owned by the dbo user.

You can use a cursor to change all your tables at once. There is no risk of data loss by doing this.

declare @t sysname
 declare cur CURSOR for select name
 from sysobjects
 where uid = USER_ID('acct567x')
 open cur
 fetch next from cur into @t
 while @@Fetch_Status = 0
 BEGIN
       set @t = 'acct567x.' + @t
       exec sp_changeobjectowner @objname=@t, @newowner='dbo'
       fetch next from cur into @t
 END
 close cur
 deallocate cur

Now to avoid this problem in the future when you create objects you will need to specify the owner name of the object when you create it. For example

CREATE TABLE dbo.NewTable
 (Column1 INT)

If you don't do this you'll have to change the owner of the table after you create it using the sp_changeobjectowner procedure.

Now to deal with your SQL Injection problem you need to get away from embedded T/SQL statements and start using stored procedures with input parameters. You can read up on this on this blog posting Avoiding SQL Injection Attacks. By using this technique you should be able to avoid the SQL Injection problems.

You can read up on stored procedures here which is part of the Back To Basics series here and here.

When switching out to stored procedures don't forget to grant your account rights to execute the stored procedure.

GRANT EXEC ON ProcedureName to Username