Security for Dynamic SQL within Stored Procedure

[MeanGreen]

I have simplified this statement to use dynamic sql. I know in this case I do not have to use dynamic sql, but I need to find a way to implement security with dynamic sql within a stored procedure. Here it is:

create table test
(idno integer identity(1,1),
tdata varchar(25))

CREATE proc usp_insert_test
(
@data varchar(25))
AS
begin
declare @ret integer
declare @sqlstr varchar(255)
select @sqlstr = 'insert test values(''' + @data + ''')'
execute (@sqlstr)
return
end
GO

I do not have permissions to the test table. I have granted "execute" permission to the stored procedure, but when I call the stored procedure I get:

Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'test', database 'TESTDB', owner 'dbo'.


Any thoughts on how I can grant execute premission to allow the inserts only through the stored procedure?

Thank you.

 

[osjohnm]

Hi MeanGreen

Are you using your own logon or have you created a logon for your test?
You might have to grant the logon atleast "read" permissions
and maybe "write" permissions to the table.

I'm assuming that SQL Server is allowing you to execute the procedure but since the login doesn't have access to the table it can't do the insert. Try granting insert permissions only at the table level and give that a try.

It sounds like the even though your "user" has execute permissions on the sp but without any access to the table SQL Server isn't going to allow the insert to take place.

John

 

[MeanGreen]

You are right, however, I cannot give user write permissions on the table. If I change the stored procedure to just "insert test values(@data)" it works fine. Again, I simplified this statement so you could see what happens. So how do I execute the dynamic sql within the stored procedure with just permissions on the stored procedure.

Any help would be greatly appreciated.

 

[osjohnm]

Hi

I checked something in BOL under stored procedures it talks about ownership chains. The owner of a procedure doesn't necessarily own the tables used inside. It also goes on about granting permissions to other users etc.

Lets assume that the there is only one user why don't you change the ownership of the procedure to belong to the user that will execute this proc and give that a try.

This might be a waste of time since in theory the user should be able to execute the stored proc regardless of its permissions on the table now that I think of it.

Thats one of the main principle sof views and procs is to deny access to tables directly but the dynamic sql is throwing a spanner in the works.

Let me see if I can come up with something

John

 

[osjohnm]

Hi

I created a table, and then just a simple insert proc.(no dynamic sql)
I created a user on the server called 'test' with no database roles and made master the default database.

I then added the test user as a database user under the public role. The test user had no insert access on the table and neither did public. I then granted execute permissions on the procedure to the user/logon and tried to execute the proc.
It worked this way.

If you first add the user as a database user and add the user/login to the publice role you should be able to get it to work.

Let me know how it turns out.

John

 

[MeanGreen]

I sent a message to Microsoft and this is what they had to say:

we made the choice to recheck permissions on dynamic sql to
avoid permissions escalation in the case of SQL injection attacks (where arbitrary SQL statements are added to valid user input - and then executed under the permissions of the Stored Proc)

There isn't anyway to prevent this validation, however there are some things you can do to avoid dynamic SQL:

1/ Review the CASE stement - useful for using alternate objects in select/from/order by statements

2/ Push dynamic code into the client app (build a sql sp call, or query), then use (password protected) application roles to execute

3/ Put the query into a table for a server side process to execute & return a recordset to the client

We realize that many of our customers would like to see this capability in a future version of the product and we are looking at providing it in a safe way. If you want to add your voice to the multitude please mail sqlwish@microsoft.com


Guess this means I cannot have my cake and eat it too. By the way, I did send an email to sqlwish for this feature.

Have a nice day.

 

[rzirpolo]

Have just come across your very interesting point and I did not realise this at all as I have just had an issue come up. The reason for my post is that I just want to clarify this applies to the problem I am having.

We have been making some changes with the Security setup in regards to the roles associated to logins.

In our VB program a function accesses a specific stored procedure i.e. calls it. The user logged in has exec rights to this stored procedure but not select rights to the table referenced in this procedure.

So is this an issue with SQL ? i.e. similar to what is discussed in this post ?

The reason I ask is that you reference inserts whereas my example is just using Select.


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

"For those of you watching in black and white, Spurs are in the all yellow strip." - John Motson

 

[SQLSister]

Stored procedures using dynamic SQL require the permissions to be set at the table/view level. As Microsoft stated this was to help avoid SQL injection attacks. There is no way to avoid setting permissions at the table/view level.

This is one of the main reasons why dynamic SQL should be avoided where possible. It can also be horrible to debug as the SQL isn't created until run-time. There are many possible SQL statements that could be created, therefore the testing is much more difficult and much more likely to miss a possible way for the program to break.