Unanswered: MS SQL Server Management Studio - permissions and stored procedures
My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.
I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!
Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables?
Your best bet is to avoid dynamic code within your stored procedure. Failing that, you need to avoid actually executing any submitted parameters within you stored procedure. Failing that, you need to thoroughly verify parameter strings before including them in any executed sql.
If it's not practically useful, then it's practically useless.
I am not using any dynamic code, i am just passing in variables to my stored proc.
I'm not sure what you mean by:
Failing that, you need to avoid actually executing any submitted parameters within you stored procedure.
I'm using SELECT statements only in my stored proc, for example:
SELECT t3.sub_id, t2.SIC_id, t1.business_name, t1.venue_id, t1.address1, t1.address2, t1.address3, t1.address4, t1.county, t1.town, t1.postcode, t1.tel, t1.img_thumb
FROM VENUE AS t1 INNER JOIN SIC AS t2 ON t1.venue_id = t2.venue_id INNER JOIN SUBSCRIPTION AS t3 ON t1.venue_id = t3.venue_id INNER JOIN SIC_TYPE AS t4 ON t2.SIC_id = t4.SIC_id
WHERE (t3.sub_id = 1) AND (t2.SIC_id = 8 OR t2.SIC_id = 9) AND (t1.town = @city OR @city = '0') AND (postcode LIKE @postcode + '%' OR @postcode = '0') AND (county = @county OR @county = '0')
Can you see anything wrong with that with regards to injection attacks?