Lock down DB write

Question

Lock down DB write

schlubadub

SSC Enthusiast

Points: 101
More actions
June 26, 2008 at 3:16 am

#183989

We host a client website (not written by us) that is suffering from ASP based SQL Injection attacks. We are currently in discussions with the client whether they are willing to pay to fix their old site (now outdated) or to develop an entirely new site.
In the meantime I am currently running a script daily to remove all of the offending information from the DB. I thought a quick solution to prevent any further injections would be to block all access to write to the DB.
There are two logins being used (one for main website, one for admin site) but they are both defined as db_owner (not how I would have done it, but that's how it is). If I remove them from the db_owner and just give them db_datareader they can no longer run any stored procedures owned by "dbo". If I leave them as db_owner but put them in db_denydatawriter it still allows them to write to the database.
I'm wondering if I'm missing a simple way to deny any kind of write operations, but still allow them access to the dbo owned stored procedures to read information. There are literally hundreds of stored procedures and I don't want to have to fiddle with any permissions on an individual basis (especially as the client hasn't agreed to pay for anything yet).
Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

If all access is via stored procedures, remove the users from the db_owner role and grant them EXECUTE permission to the dbo owned stored procedures that are used to select data. You don't need to make them members of db_datareader. Unfortunately, there isn't a fixed database role that grants EXECUTE permissions. You'll have to grant the permission on each stored procedure.

Greg

schlubadub SSC Enthusiast Points: 101 More actions · Answer 2

cool... thanks for that. I'll see how I go with it!

I found this article for granting execute permissions to all stored procedures for a user / role

The original article is here: http://www.mssqltips.com/tip.asp?tip=1203

CREATE PROCEDURE spGrantExectoAllStoredProcs @user sysname

AS

/*----------------------------------------------------------------------------

-- Object Name: spGrantExectoAllStoredProcs

-- Author: Edgewood Solutions

-- Development Date: 03.19.2007

-- Called By: TBD

-- Description: Issue GRANT EXEC statement for all stored procedures

-- based on the user name that is passed in to this stored procedure

-- Project: SQL Server Security

-- Database: User defined databases

-- Business Process: SQL Server Security

--

----------------------------------------------------------------------------

-- Num | CRF ID | Date Modified | Developer | Description

----------------------------------------------------------------------------

-- 001 | N\A | 03.15.2007 | Edgewood | Original code for the GRANT

-- EXEC process

--

*/

SET NOCOUNT ON

-- 1 - Variable declarations

DECLARE @CMD1 varchar(8000)

DECLARE @MAXOID int

DECLARE @OwnerName varchar(128)

DECLARE @ObjectName varchar(128)

-- 2 - Create temporary table

CREATE TABLE #StoredProcedures

(OID int IDENTITY (1,1),

StoredProcOwner varchar(128) NOT NULL,

StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)

SELECT ROUTINE_SCHEMA, ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME NOT LIKE 'dt_%'

AND ROUTINE_TYPE = 'PROCEDURE'

-- 4 - Capture the @MAXOID value

SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop

WHILE @MAXOID > 0

BEGIN

-- 6 - Initialize the variables

SELECT @OwnerName = StoredProcOwner,

@ObjectName = StoredProcName

FROM #StoredProcedures

WHERE OID = @MAXOID

-- 7 - Build the string

SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

-- 8 - Execute the string

-- SELECT @CMD1

EXEC(@CMD1)

-- 9 - Decrement @MAXOID

SET @MAXOID = @MAXOID - 1

END

-- 10 - Drop the temporary table

DROP TABLE #StoredProcedures

SET NOCOUNT OFF

GO

schlubadub SSC Enthusiast Points: 101 More actions · Answer 3

For some reason it fails as soon as I try to open a connection to the DB unless they are in the db_owner group... i'm not sure what permissions are required to do this in ASP

dbAEH.Open("PROVIDER=SQLOLEDB;DATA SOURCE=myServer;UID=myUser;PWD=myPassword;DATABASE=myDB;")

MannySingh SSChampion Points: 12465 More actions · Answer 4

TRY THIS:

use dbname

select 'GRANT EXECUTE ON '+name+ ' to username' from sysobjects

where type='P'

and category=0

Maninder
www.dbanation.com

MannySingh SSChampion Points: 12465 More actions · Answer 5

Forgot to mention, After running the above Query, all the GRANT Statements will be in the result window. Just copy and past to another window and execute, you should be good to go.

Maninder
www.dbanation.com