CODEWITHSUNDEEP
c#sql-serverstored-proceduresado.net
user2896152
user2896152

Reputation: 816

Checks if the execution of a stored procedure could modify data

I would like to implement a WCF service with the scope to retrieve some data from a database.

The data is retrieved executing a stored procedure. Data is returned in xml format.

Practically, my service contains a method which one parameter specifying the name of the stored procedures to execute and, if necessary, some input parameters.

For security, I would like to check if the execution of the stored procedure passed as input could modify the data into the DB.

Let's do a simple example. I, a malicious user, find out how this service works, and call my method like this:

 ExecuteStoredProcedureToGetData("proc_InsertNewRow", "<XML><User Id="VeryBadBoy"/></XML>");

Is there a mechanism that I can use to check if proc_InsertNewRow modifies the database, working with C#, ADO.NET and SQL Server.

The logic I would give my implementation should be like this: I'm designed to retrieve some data, not to modify the data. I don't execute this stored procedure.

EDIT: I'll try to explain it better. My system generates a lot of data. Between the client and the server it was decided to add a new layer, my WCF service. So, when the client requests some data, the server forwards the data to my service passing the name of the stored procedure.

Ex. user opens the Products screen -> call to server GetProducts() -> call to WCF Service ExecuteStoredProcedure("proc_GetProducts").

The check if the call to ExecuteStoredProcedure modifies the data is an additional check (or maybe an Xy problem as someone as defined) I want to do because I don't want that someone uses my general purpose method to alter the data.

Upvotes: 0

Views: 92

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Although you've asked about Y, I'm going to go ahead and address X.

Trying to program your WCF to pre-parse every stored procedure name that gets passed to it, every time, is a terrible idea. It will be error-prone, bad for performance, and it's just not the right way or even right place to handle security.

I'd even go so far as to say that having a single WCF method to generically handle all stored procedure calls is also not the right way to do things, but that's even more tangential to your question.

The right way to do what you want is to find out from your database owners exactly which procedures the WCF users should be allowed to execute, and then program the WCF to only execute those procedures and return some kind of error/warning when the users tries to execute anything that is not on that list.

If you are concerned that the database owners may want to add new procedures in the future that are ok to use, they can keep the list of allowed procedures in a table, and you can just query that table each time a procedure call comes in.

But, disregarding your actual question even further, the BEST way to handle this is not to handle it at all...in the WCF. The Right Place to handle database security is in the database. The WCF should be given a specific user account to access the database. The Database Guys should be responsible for giving that user account the correct permissions to the procedures in their database. The only thing you should be responsible for in the WCF is handling any permissions errors returned by the database.

Upvotes: 2

Related Questions