Read Only access to a SQL Database? Best way?

10 pts.
Tags:
SQL injection
SQL Server 2000
SQL Server security
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

<pre>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</pre>

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

<pre>CREATE TABLE dbo.NewTable
(Column1 INT)</pre>

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 <a href=”http://itknowledgeexchange.techtarget.com/sql-server/avoiding-sql-injection-attacks/”>Avoiding SQL Injection Attacks</a>. By using this technique you should be able to avoid the SQL Injection problems.

You can read up on stored procedures <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-stored-procedures-the-work-horse-of-the-database/”>here</a> which is part of the Back To Basics series <a href=”http://itknowledgeexchange.techtarget.com/sql-server/tag/back-to-basics/”>here</a> and <a href=”http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1309865,00.html”>here</a>.

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

<pre>GRANT EXEC ON ProcedureName to Username</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following