Dynamic SQL and the security context

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Dynamic SQL and the security context

Author

Topic

emilg
Starting Member

13 Posts

Posted - 2005-07-07 : 05:54:41

The environment

I'm operating an ASP.NET application working with SQLServer 2000 backend. The application is working on the database only through stored procedures. There is no SQL code in the application.

For security reasons I created a special login and a special role for the application's connection string. The role grants only execution permissions on the required stored procedures and denies access to any other database objects (no SELECT/UPDATE/DELETE on the tables). In this way any attack using that account (direct login or sql injection) is limited.

The problem

The system works perfectly except 1 problem. The procedures that use dynamic SQL (with sp_executesql) are not working:

[CODE]
Exec Entities @TableName = 'TableTest', @TableColumn = 'ColumnX'
----------------------------------------------

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'TableTest', database 'Production', owner 'dbo'.
[/CODE]

This procedures, which have proper rights, contains code like:

[CODE]
CREATE PROCEDURE Entities
(
@TableName nvarchar(50),
@TableColumn nvarchar(100)
)
AS

declare @string nvarchar(1000)
set @string = 'select ' + @TableName + 'ID, ' + @TableColumn + ' as ' + @TableName + '
from ' + @TableName
exec sp_executesql @string
[/CODE]

It seems that the "exec sp_executesql" statement is executed in another security context that the one that the procedure that encapsulates it runs in.

Do you have any suggestion on how to solve/avoid this issue?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-07 : 05:59:08

Yes, do not use dynamic SQL, especially the way it is used in this procedure. Code like that completely invalidates using sproc's, since it can easily be used to perform SQL injection attacks.

emilg
Starting Member

13 Posts

Posted - 2005-07-08 : 03:43:00

Tks,
Unfortunately (or fortunately somehow) I cannot avoid using dynamic SQL because otherwise I would have to create a couple of hundred of procedures for all the cases that I have, which will be more difficult to maintain. I use check for most of the SQL injections attacks in the frontend.

I'll try the suggestions from here:
[url]http://support.microsoft.com/default.aspx?scid=KB;EN-US;301299[/url]

Emil

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-08 : 03:58:38

See if this helps: http://www.sommarskog.se/dynamic_sql.html
I don't know what "cases" you have there, but when you talk about dynamic searches, there is also a very good reading on the aforementioned site.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-08 : 07:07:34

quote:
Unfortunately (or fortunately somehow) I cannot avoid using dynamic SQL because otherwise I would have to create a couple of hundred of procedures for all the cases that I have, which will be more difficult to maintain

In other words, you're willing to sacrifice an excellently designed security model so that you don't have to create some more stored procedures and maintain them.

Sorry if I sound annoyed, but I am, and you're making the wrong decision here.

The fact is that these procedures were INCORRECTLY designed in the first place, they should never have used dynamic SQL at all. More to the point, any design that is generic about which table, database, or columns it accesses is poorly designed. Procedures should be specifically written to do their job, not generically written to do anything and everything. That's the reason you're having trouble maintaining the code, and you'll only have more problems if you go in the direction you've indicated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-08 : 09:05:09

quote:
Originally posted by emilg
[CODE]
Exec Entities @TableName = 'TableTest', @TableColumn = 'ColumnX'
----------------------------------------------

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'TableTest', database 'Production', owner 'dbo'.
[/CODE]

This procedures, which have proper rights, contains code like:

[CODE]
CREATE PROCEDURE Entities
(
@TableName nvarchar(50),
@TableColumn nvarchar(100)
)
AS

declare @string nvarchar(1000)
set @string = 'select ' + @TableName + 'ID, ' + @TableColumn + ' as ' + @TableName + '
from ' + @TableName
exec sp_executesql @string
[/CODE]

A classic case of a bad database design as Rob says. Tables and columns should be CONSTANTS and should not change or need to be dynamically referenced.

The stored procedure example you have posted is what I call a "fake" stored procedure -- you are wrapping code into a stored proc so that you (or your boss) is satisfied that you are using stored procedures in your app to access the db. But if you are going to do that, then you should instead delete ALL of your stored procedures and consider using a very flexible, built-in "stored procedure" that SQL Server has already defined for you: it's called SELECT.

Stored procedures like the one you have demonstrated are simply poor-man's replacements for standard SELECT statements and have all of the security and lack of database abstraction features of running SELECTs directly against your database, but with the added "advantages" of being extremely inflexible, harder to read and maintain, and less efficient.

If your database design dictates that stored procedures need to accept arguments that reference database objects directly (column or table names specifically) then it is time to normalize your database.

Exceptions to this rule are locked down DBA database maintainence stored procs run from time to time only by the DBA or in a DEV environment for testing and things like that, but never by a production app.

- Jeff

emilg
Starting Member

13 Posts

Posted - 2005-07-08 : 09:33:29

This procedured where not "requested" by a dynamic database design but simply by the number of small entities which had in commun the Id and Name columns, which where used in different dropdowns.

After reading the Sommarskog excellent material I see the main issue for this application remains, with all the precautions, the security one.

So I'm changing the procedures duplicating the dinamic ones, and maintaining them by some code.

Tks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-08 : 13:46:25

quote:
This procedured where not "requested" by a dynamic database design but simply by the number of small entities which had in commun the Id and Name columns, which where used in different dropdowns.

Then these small tables are good candidates to be consolidated into a single Entity table, which would solve the maintenance, security, and dynamic SQL issues in one stroke.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-11 : 02:32:34

quote:

Then these small tables are good candidates to be consolidated into a single Entity table, which would solve the maintenance, security, and dynamic SQL issues in one stroke.

Here's a funny one on that topic. Get a coffee, sit back and enjoy http://groups.yahoo.com/group/sql_server7_forum/message/5731

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

emilg
Starting Member

13 Posts

Posted - 2005-07-11 : 04:48:37

Joe might have a good point in the fact that you loose the posibility of using the FOREINGN KEYS and the performance also.

Another issue is because the structure of the tables is not the same, which would generate lot of overhead also (the structure I'm refering to it's a tree organizational structure with lots of dimentions...characteristics/subcharacteristics, which have their own specific properties)

I think I'll go on the option of genating through code the static procedures and granting the needed rights. In this way I'll use static procedures, but the maintenace will be dinamic.

Tks for the coffee :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-11 : 09:24:28

quote:

I think I'll go on the option of genating through code the static procedures and granting the needed rights. In this way I'll use static procedures, but the maintenace will be dinamic.

Good call; I think that's a great way to approach the situation.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources