Problem with "execute" procedure rights.

Question

Problem with "execute" procedure rights.

Joy Smith San

SSC-Insane

Points: 24877
More actions
August 13, 2008 at 12:05 am

#192269

Dear All,
I have created a role which has been given only "execute" rights for procedures. It doesn't have any other rights including INSERT and DELETE. This works fine when I try to INSERT or DELETE directly in the database (System doesn't allow since this role doesn't have those rights.).
Problem is that, this ROLE can execute a procedure in which I have written INSERT and DELETE statements. I don't want this. USER should not be able to INSERT or DELETE even through procedures. but should be able to execute the procedures.
How can I do this..? Can anyone please help me.. Very urgent..
Thansk in advance.
Regards,
Santhosh.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 1

I'm a little confused. If you grant execute to a stored procedure that can insert data... it's going to insert data. The way around it is to not grant execute permissions to the procedures that manipulate data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks for your reply.

Wel, so a user who does't have permission to INSERT can still insert the records by executing procedures. right..?

Is there any way to check for the logged user (or ROLE perhaps) in procedure..so that I can write the procedure accordingly.

Thanks..

Santhosh.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

Not per se. The thing is, you need to be very careful about how you manage security. If you give a role blanket execution priveleges, then you may need to explicitly revoke permission on the procedures you don't actually want it to execute. The other approach is to explicitly grant permission on a stored procedure by stored procedure basis.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thank you so much.

I just wanted to confirm this. Fine, I will give the rights accordingly.

Infact there was an SQL injection attack on our database recently.

So I need to implement the security properly in the system.

Basically am a developer and just getting into DBA activities.

So I will require all your suuport. Thanks.

Santhosh.