Stored Procedures – Common Security Practice

Tags: , ,
Comments: 1 Comment
Published on: September 20, 2011

In SQL Server a good practice is to access the data via calls through stored procedure.  Have a look at the document available in that link.

To further this practice, one may create a database role, then add users to that role.  Permissions to execute the stored procedures would then be granted to the role.  A role is simple enough to create.  You can do that with the following code.

[codesyntax lang="tsql"]

[/codesyntax]

After creating this role, simply add users to that role.  The next part of the process is to ensure that you have granted the appropriate permissions to this role.  There are two methods to do that: 1) blanket execute to all procedures, and 2) pick and choose the procs to which you wish to grant permission.

Before we get to adding permissions, let’s create a little test proc for testing purposes.  I will reuse something from a past article to simplify.  In that article, I already did the setup for the table – you can get it from here.  The stored procedure is as follows.

[codesyntax lang="tsql"]

[/codesyntax]

Let’s also make sure that the user has been added to the role.

[codesyntax lang="tsql"]

[/codesyntax]

As for the exercise in how to create that database user and the associated login, I will leave that for you to do.

In order to test, we need to connect to the SQL Server as that user.  Once connected, run the following to verify that your session is connected as desired.

[codesyntax lang="tsql"]

[/codesyntax]

On my connection, running that query will show that the LoggedInUser is testu.  Now, having confirmed that I am connected as the appropriate user, I will try to execute that test proc we created.

[codesyntax lang="tsql"]

[/codesyntax]

At this point, the expected results should be similar to this error message.

[codesyntax lang="tsql"]

[/codesyntax]

Now, I will switch over to the previous connection where I have administrative permissions.  I will now proceed to grant execute permissions following the first method – blanket grant.

[codesyntax lang="tsql"]

[/codesyntax]

And now, flip back to the user connection to test our permissions.  Try running that proc again, and your results should be similar to these.

ColorID ColorPlate ColorType
1 Red 1
2 Blue 2

That is good, but what else can this user now do?  The user can execute all user created stored procedures.  Do you necessarily want this?  What if your business requirements specify that certain user groups be able to execute only certain procs?

In that case, we now need to grant execute permissions on a more granular level.  A big problem with this method pops up right from the beginning.  What if there are thousands of stored procedures?  What if you need to grant execute permissions to hundreds of stored procedures for each role?

We have two avenues for these types of situations.  One avenue is to separate the various stored procedures via schema and then grant execute to the schema.  The other is via naming convention.

In the event you have a suitable naming convention to help mass assign permissions, here is a little script to help.

[codesyntax lang="tsql"]

[/codesyntax]

It is very simplistic, I know.  I also left an example of such a naming scheme.  In this example, the naming convention may imply that the procedure is a Reporting Services stored procedure.  I can query for all of the Reporting Services procs in the database, and then assign permissions to all of them much faster.

This method does not immediately grant permissions to the entire result set.  It does allow for you to review the results.

Are there more elaborate examples out there?  Certainly.  Find a method that suits you.  I would be very careful about using the first method though – it just might be too much granted to the user.

1 Comment - Leave a comment
  1. […] Stored Procedures – Common Security Practice […]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
September 2011
M T W T F S S
« Aug   Oct »
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, October 25, 2014