Re: Website & SQL Server Security


"csgraham74" <colin@xxxxxxxxxxxxxxxx> wrote in message
news:97929c8c-2936-4d02-ab24-683bec63d430@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ok,

im sure this has been dealt with before but when i read other posts i
kinda get lost in the jargon. Ok the reason for my being here is that
i developed a website that recently got hit by a sql injection attack.

Of the back of that i have decided to rethink my security strategy to
ensure it doesnt happen again.

from my vague understanding of sql server i have decided to setup my
sql server authentication to allow only db_datareader access and also
to give grant permissions to my stored procedures.

To me this means that front end users will only be able to run
"SELECT" command statements on my datatables & run the stored
procedures as i have set them up.

what im wondering about is this really what this means or does this
mean that someonce could do sql injection again through one of my
forms and delete or insert into my database ????


BTW ive added extra validation to all my user input formsto restrict
SQL Injection but its the database stuff i need to know more about.

any help appreciated.

You realize that to use impersonation (where SQL statements within a stored
procedure execute with privileges of the procedure's owner, when called by
less-privileged users who have been granted permission to execute) those
stored procedures cannot use any dynamic SQL within them. (Well actually,
they can, but dynamic SQL in them executes with the privileges of the
caller, not the owner.)

But the bonus of doing all your writes from within stored procedures is that
you guarantee yourself at least some degree of parameterization, and as long
as you don't concatenate dynamic T-SQL statements with user input passed as
parameters into your procs, it's much easier to safely implement a fair
level of dynamic -- for example, sp_executesql provides a way to pass
parameters to dynamic SQL. Given that this is the case, with due caution,
you can create privileged connections to execute the stored procedures, and
unprivileged connections to do the rest, without sacrificing too much
safety.


-Mark



CG


.

Relevant Pages

  • Re: A peculiar problem
    ... > and then to execute them. ... Text manipulation in SQL Server is very hard, ... > 1.Create a batch file with isql utility using an input file and putting ... Export the generated file(with the modified stored procedures) using ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance when executing stored procedure
    ... > Generally I would write stored procedures to do only one job. ... If SQL Server ... > which is even worse those sps can execute each other. ... > then executed spOrders which is executed in spCustomers and then got stuck ...
    (microsoft.public.sqlserver.programming)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • I think our understandings are different
    ... He said "direct execute sql". ... To say that stored procedures are "far ... > select listingId, listingName from Property ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: WLM for toddlers
    ... Just a little nitpicking - enclave SRBs for distributed threads are in xxxxDIST ... load modules will execute in this address space. ... SQL statements (and with ... SQL Stored Procedures) are executed in the DIST address space. ...
    (bit.listserv.ibm-main)
Loading