EXECUTE permissions revisited

I have scripted a method of granting EXECUTE permissions to certain stored procedures or functions a while ago and then it hit me – this is a kind of reinvent-the-wheel example. It works, it can be used for extending rights to certain objects, but if you want them all, there’s easier way.

GRANT EXECUTE TO 'user'

You might want to create a database role for all stored procedures and assign users to it. Be aware, though, that by design a user through a procedure gets a possibility to manipulate the data, and that GRANT EXECUTE on a procedure preceeds DENY INSERT/DELETE/UPDATE on a table in permission evaluation. You should reconsider granting EXECUTE permissions to all procedures for too many users, as it is exposes a security hole.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s