execute permissions

Question

execute permissions

Jonathan Leahy

SSC Rookie

Points: 25
More actions
December 3, 2007 at 11:31 am

#177095

Hi,
I have a couple of questions regarding permissions and security.
First, I have read many times, that a wise practice is to give your permissions through stored procedures, in other words, give execute permissions on stored procedures, and limit or do not allow direct access to tables and other objects.
How would I give a user or a role(and then add the user to the role) permission to execute all stored procedures, preferably a blanket statement(GRANT EXECUTE ANY... not a script I have to run everytime I create a new stored procedure)?
Second,
And here is the rub, shouldn't that user/role be able to drop a table/delete records/ perform any functionality if it is within the stored procedure they have execute rights on? With the exception of dynamic sql, which I understand.
Thanks for anyone's input in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Brian Fultz SSC Rookie Points: 29 More actions · Answer 1

Jonathan,

In SQL 2005 you can now use roles to handle giving execute access to all stored procedures. In 2000 you had to query the information_schema to find all of the procs and the pass that to a resultset and then execute.

The below code will create a role and give execute access to all of the stored procedures.

/* CREATE A NEW ROLE */

CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor

As for your second question, the user will only be able to execute the stored procs themselves. If there is a delete/update statement in the sp, they will be able to execute only in the context of the sp. They will not have access to delete/update from any other context.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

No blanket way, keep the permissions with the stored proc and include

...stored proc code...

go

grant execute on (procname) to (role)

go

in every stored procedure code. You should have this stuff under source control, keep a quick template handy with this code, and that way if you need to rebuild the procs for some reason you have permissions handy.

The reason they don't need permissions to the underlying tables to change data is that there is ownership chaining to help here. If the stored proc is owned by dbo (typically), then as long as the tables are owned by dbo, you're ok. Any schema works here, just be sure the schema and object are owned by the same person.

If this isn't the case, you get into more complex permissioning.

Brian Fultz SSC Rookie Points: 29 More actions · Answer 3

Steve,

Are you sure about the "no blanket" way?

I have used the role based execute permissions in SQL 2005 myself on previous database implementations.

There is more documentation on the process at: http://www.sqldbatips.com/showarticle.asp?ID=8

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Brian,

Very cool! I didn't realize you could do this in SS2K5. I think it' s probably an unintended "feature" from MS, but that is very handy. I'll have to write about it , unless you want to 🙂

Steve

Jonathan Leahy SSC Rookie Points: 25 More actions · Answer 5

What is the difference between:

/* CREATE A NEW ROLE */

CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor

And

GRANT SELECT ON SCHEMA::SchemaName TO UserName

example: GRANT SELECT ON SCHEMA::dbo TO db_executor

Thanks again.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

Not sure. In prior versions you couldn't grant execute to future procs. Looks like it's changed in SS2K5.

Brian Fultz SSC Rookie Points: 29 More actions · Answer 7

Steve,

Feel free to write about it. Too much work for me to get time to do any real writing. Just here to help where I can.

Jonathan,

Doesn't giving SELECT to the dbo schema give a lot more permissions than are required? From a security standpoint I would stay away from giving any users any level security related to dbo.

Jonathan Leahy SSC Rookie Points: 25 More actions · Answer 8

Jonathan Leahy

SSC Rookie

Points: 25

December 4, 2007 at 11:16 am

#757089

thanks to all.

Low Rider SSCommitted Points: 1502 More actions · Answer 9

I want to deny insert/update/delete permissions to a user who has been given grant execute permission in a 2005 db.

I only want the user to be able execute procs which only have select in it but no insert/update/delete.I know i can give individual permission only those procs

which have select on it but this will lead to maintainance issues as i will have to

keep giving permission whenever any new proc with only select in it is created in the db.

how is this possible ?

thanks in advance.