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.

Column Level Permissions

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server?   Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level.  This can be demonstrated by the following script.

[codesyntax lang=”tsql”]

[/codesyntax]

If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this?  Well, it is quite possible you have some of these permissions already in place.  It is possible you may have inherited something like this.  Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then.  How do you find out what columns have specific permissions applied to certain users?  Well, that is actually pretty straight forward.  We can query the system views and determine column level permissions.

[codesyntax lang=”tsql”]

[/codesyntax]

The previous query is a really simple version of how to find this information.  As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well.  Major_id maps to object_id and column_id maps to minor_id.

Conclusion

This query can be of good use to determine permissions in place for columns in tables within your database.  Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions.  With there being more complex methods, I am sure there are also some easier methods.  Let me know what you do to query these permissions.

T-SQL Tuesday #17 – APPLY Knowledge

Comments: 3 Comments
Published on: April 12, 2011

We have another opportunity to write as a part of TSQL Tuesday today.  This month Matt Velic (Blog | Twitter).  Matt has proposed a challenge that was derived from a comment on twitter.  The challenge this month is to write about “Apply.”

Admins Apply Within

As an administrator of databases, do you use Apply?  Not only can Apply be used to help return result sets as a part of your application, it can come in quite handy from an administration point of view too.  It can be used to help retrieve the text of currently executing code or code stored in cache.  Apply can be used to help retrieve index information and it can come in quite handy when performing a security audit.

Some Back Story

Just a few days ago, I posted some scripts to help find role membership of logins at the server level.  I wasn’t satisfied with the query and decided to rewrite it as a part of my entry for this months blog party.  You can read up on that script here.  My dissatisfaction with the query was that it felt klugy.  I wanted to write the query with something other than the Union and to present fewer rows to user.  So, I took another look at the query and purpose for the query and decided to use a combination of Apply and Pivot.

My new take on the script is simple, I will create a result set that will show a 1 for each server role for which a login is a member.  Each login will only have 1 row, opposed to the row per server role membership from the prior query.

The Script

[codesyntax lang=”tsql”]

[/codesyntax]

In this example, the bulk of the heavy lifting is done through the pivot.  You will see that the Apply only seems to play a minor role in this script.  It is an important role nonetheless.  All logins (unless you have altered the public role) are also a member of the public role.  That membership is not displayed through the views I have used in this query.  Due to that handling of the public role, and to ensure that the membership in that role is not forgotten, it must be accounted for in the query.

This query will add a new column for the public role to those columns already presented via the pivot.  In this column we will show that every login returned via the query is a member of the public role.  But how is that done?  Using the apply, in really basic terms, acts like a join to this subquery that I use for the public column.  I am taking the value of 1 for column public, and applying that value to all of the results from the Pivot portion of the query.  I am using the Cross version and there is one more version – called Outer.  In my testing, both the Outer and the Cross Apply will return the same results for this particular query.

Some common uses for Apply are in conjunction with TVFs.  Should you use the Apply with a function where the “Join” criteria would be more tightly defined, then the use of Cross V. Outer will produce different results.  Just as with an Outer Join, the Outer would return results for all records in the “outer” or “left” part of the join whether there was a matching record in the TVF or not.  In the same sort of setup, the Cross Apply would only return records that had a match in the TVF.

Conclusion

Again, this is an oversimplification of the Apply statement.  My main goal was to present a use for Apply.  Apply can be a useful tool in the hands of the database administrator.  Examine it and learn what other potential it may have for some of your administration needs.

This is just one piece of the puzzle when performing a security audit.  Getting this information quickly when requested for an audit can be very helpful.  The output format is quite simple and very conducive for many auditors and management.  A simple output report for the Server Roles in your environment is merely one example of the usefulness of Apply.

SSIS Job Ownership

Comments: 2 Comments
Published on: April 11, 2011

I was strolling along one day when I saw somebody asking how to find out who owns a maintenance plan.  That evolved into finding out who owns the the job associated with the maintenance plan.  All of this in SQL 2005 and SQL 2008.

Well, we were stumped for a bit trying to figure the link between the job tables in the msdb database and the ssis table in the same database.  Linking the two together is not very obvious and we struggled with it for a bit.  After some research and trying this that and the other, I was able to come up with the below script.

[codesyntax lang=”tsql”]

[/codesyntax]

This script is set to work out of the gate with SQL 2008.  Should you want it to work with SQL 2005 the change is simple.  Change the sysssispackages table to sysdtspackages90.  As you can see, the query joins the SSIS table to the jobsteps table with a pretty nasty string extraction.  There are other ways of extracting this information (I’m sure of it).  This works quite well for what it is intended.

Using this script, you can find out the jobowner, the packageowner, and the packagetype.  This is pretty good information to have on hand if you have several ssis packages that are stored in msdb and are run from a job.  One thing this script does not yet handle is if the SSIS file is stored on the file system.  Note that I only coded it so far to work with files stored in SQL.  When looking in the jobsteps table, you can tell the difference quickly by seeing that those stored in msdb have a /SQL at the beginning of the command string.  Those in the filesystem have a /FILE en lieu of that /SQL.

In a future revision I will work on parsing the package name out of that string that represents those stored in the file system.  And despite that nasty join, this runs quickly on my systems.  I am open to suggestions or other solutions that can provide this kind of insight.

SQL Server Role Membership

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 5, 2011

How well do you know the security in your SQL instances?  Do you know who has sysadmin level permissions?  SQL Server provides a few methods for you to find out who is a member of which roles at the server level.

For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role.  For those that want something a bit faster, you can use a script to return this information for you.  Just as with most things TSQL, there are numerous different ways of writing this script.  Here are some of those methods.

Verifying Server Role membership

[codesyntax lang=”tsql”]

[/codesyntax]

With this script, I am querying the sys.server_role_members and sys.server_principals views.  For simplicity sake, I am also using the SUSER_NAME() function to derive the role name.  Note that I threw in a union all to get back the ‘Public’ group membership.  The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.

An Alternative

[codesyntax lang=”tsql”]

[/codesyntax]

This one is quite simple as well.  Note that I am not employing the use of the SUSER_NAME function but have used another join in its place.  I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled.  That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.

Another Option

This is really the easiest of the three queries.

[codesyntax lang=”tsql”]

[/codesyntax]

I am still employing the union statement to populate the public role.  Notice the difference in the first half of the query though.  I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view.  This is a little easier to follow and write.  Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.

All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers).  The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server.  It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.

IP and Default Trace…T-SQL Tuesday #005

Comments: 9 Comments
Published on: April 6, 2010

As chance would have it, I had been checking Adam’s blog daily for the last few days to find the next T-SQL Tuesday.  Not having seen it, I started working on my next post yesterday evening.  The fortunate thing is that the post I was working on fits well with this months topic.  T-SQL Tuesday #005 is being hosted by Aaron Nelson.  And now I have one more blog to bookmark since I didn’t have his bookmarked previously.

This month the topic is Reporting.  Reporting is a pretty wide open topic but not nearly as wide open as the previous two months.  Nonetheless, I think the topic should be handled pretty easily by most of the participants.  My post deals with reporting on User connections.  I have to admit that this one really just fell into my lap as I was helping somebody else.

The Story

Recently I came across a question on how to find the IP address of a connection.  From experience I had a couple of preconceptions on what could be done to find this information.  SQL server gives us a few avenues to be able to find that data.  There are also some good reasons to try and track this data as well.  However, the context of the question was different than I had envisioned it.  The person already knew how to gain the IP address and was already well onto their way with a well formed query.  All that was needed was just the final piece of the puzzle.  This final piece was to integrate sys.dm_exec_connections with the existing query which pulls information from trace files that exist on the server.

After exploring the query a bit, it also became evident that other pertinent information could prove quite useful in a single result set.  The trace can be used to pull back plenty of useful information depending on the needs.  Here is a query that you can use to explore that information and determine for yourself the pertinent information for your requirements, or if the information you seek is even attainable through this method.

[codesyntax lang=”tsql”]

[/codesyntax]

Now, I must explain that there is a problem with joining the trace files to the DMVs.  The sys.dm_exec_connections maintains current connection information as does the sys.dm_exec_sessions DMV.  Thus mapping the trace file to these DMV’s could be problematic if looking for historical information.  So now the conundrum is really how to make this work.  So that the data returned in the report, some additional constraints would have to be placed on the query.  But let’s first evaluate the first go around with this requirement.

Query Attempts

[codesyntax lang=”tsql”]

[/codesyntax]

While evaluating this query, one may spot the obvious problem.  If not seen at this point, a quick execution of the query will divulge the problem.  SPIDs are not unique, they are re-used.  Thus when querying historical information against current running information, one is going to get inaccurate results.  Essentially, for this requirement we have no better certain knowledge what the IP Address would be for those connections showing up in the trace files.  The IP Addresses of the current connections will cross populate and render inaccurate results from the historical information.

My next step gets us a little closer.  I decided to include more limiting information in the Join to the sys.dm_exec_connections view.  The way I determined to do this was that I needed to also include the loginname as a condition.  Thus in order to get that, I need to include sys.dm_exec_sessions in my query.  To make it all come together, I pulled that information into a CTE.  Here is the new version.

[codesyntax lang=”tsql”]

[/codesyntax]

The information pulled back is much cleaner now.  But wait, this mostly reflects current connections or connections from the same person who happens to have the same SPID as a previous time that person connected.  Yes, an inherent problem with combining historical information to the current connection information in the server.

Solution

My recommendation to solve this need for capturing IP address information along with the person who connected, their computer hostname, and the time that they connected is to do so pre-emptively.  (I know this diverges from the report for a minute, but is necessary to setup for the report).  A solution I have implemented in the past is to use a logon trigger that records the pertinent information to a Table.

Tables

[codesyntax lang=”tsql” title=”AuditLogonViolation Table”]

[/codesyntax]

[codesyntax lang=”tsql” title=”AuditLogonEvent Table”]

[/codesyntax]

Trigger

[codesyntax lang=”tsql” title=”LogonTrigger”]

[/codesyntax]

This solution gets the IP Address from the ClientHost Field of EventData() which is where the logon occurred.  That should be enough to trace back to the source of the connection.  Also note that the application was not included in this, that is information that could be gained from the trace files.  What this does is create a structure for logging logon events as well as prevent logons from unauthorized sources using an account that has been compromised (while logging that action as well).  With this, I now have a solution that complements the trace file.  I could query both and create a more accurate report of the logon activity that has occurred historically (depending on retention policy etc.).  This last method is somewhat limited by the EventData function and the xml schema that comes with it.  You can check it out here.

Reports

So here are a couple of parting solutions that would be able to give us the relevant data for tracking these historical logons.

[codesyntax lang=”tsql”]

[/codesyntax]

The above is a simple query to pull back the information from both the violations and successful logon audit tables.

The following is a solution for combining the previous requirement (as I have translated it to truly correlate to Host rather than IP).

[codesyntax lang=”tsql” title=”Combining Audit with Trace”]

[/codesyntax]

The last such report that I wish to share related to this topic is to find current activity.  Since the original idea was to combine the trace with the DMV, I decided that I needed to at least look at the DMV for a relevant query.  These DMV’s provide current activity and need to be combined to provide a quick snapshot of the activity that is occuring on the server at a given moment.  One quick snapshot can tell me who is logged in, from what IP Address, the application, read and write activity, and what the SQL is that is being executed.

[codesyntax lang=”tsql” title=”SP_who2005″]

[/codesyntax]

There are several queries out there similar to this last one.  The idea is to capture a quick glimpse based on the DMVs which in essence are a report of a subset of server relevant information.

Conclusion

When it is necessary to provide reports on activity occurring on the server, it pays to do a little prep work.  Be Prepared.  It is not an easy task to be able to go back in time and report on data that isn’t captured.  The little prep work that one may need to do is well worth the effort in the end.

Edit:  Corrected 1 detail and fixed some formatting.

Security Audit

Comments: 4 Comments
Published on: March 19, 2010

Of late I have seen a lot of questions on how to audit the logins and users on each SQL Server.  I had the same questions for myself when I went through the same exercise some time ago.  My first step was to peruse the internet and see what I could find to get me started.  I found that to be quite helpful.  I found a lot of different scripts that were beneficial.  I, like most, did find one though that I preferred above the rest.  That script can be found here.

Why do I like this script?  I like the format.  It also generates a nice output that can be passed along to auditors.  The output is saved into an html format and seems more presentable to me.  Besides those facets, it meets the base requirements – I can find what roles and users have what permissions in each database on a SQL Server Instance.

The script didn’t quite suit all of my needs.  I think that is frequently the case.  The trick is being able to take the script and make necessary adjustments to suit whatever needs you may encounter.  The changes that I made to this script were in favor of progressing toward an automated inventory solution that I could run from a central location.  The script as it stood required manual intervention.  Granted, I have not yet completed my inventory solution, I have modified the script to work well with 2000 and 2005 and output the results to a properly consumable html file.  Since 2000 and 2005 behave differently in certain regards, I had to add some logic for the script to also behave differently if depending on the version of SQL Server it was run against.  This was necessary since I have SQL 2000 – SQl 2008 in my environment.

Scripts of Change

So, starting from the top.  I decided to use several more variables and create a bunch of temp tables.  The variables will help in the decision making, and the temp tables will help in Data storage for processing as the script runs.  Thus we have this block of code at the top in place of the old Variable block from the original script.

[codesyntax lang=”tsql”]

[/codesyntax]

That is the prep setup so we can now begin the true work of the script.  As, I said there was some decision logic added to the script.  I needed to find a way to determine SQL Server version and based on version execute a different script.  And now we have the decision block.

[codesyntax lang=”tsql”]

[/codesyntax]

Basically, I am checking the version and determining if I should use the SQL 2000 objects or if I can use the SQL 2005 objects since the 2000 objects are scheduled for deprecation.  Also, since xp_cmdshell is disabled by default in SQL 2005, I am prepping to enable that just for the final piece of this script.  Due to the nature of xp_cmdshell, it is advisable that you understand the security risk involved and revert it back to disabled – if you enabled it to run this script.  There are other methods for doing this, I am sure, but I chose this since I got consistent results and have not had time to revisit it.

After that decision tree, I have changed the main body of the script to also use a decision tree in building the dynamic sql.  That tree is built like the following snippet.

[codesyntax lang=”tsql”]

[/codesyntax]

I think you can see at this point some of the differences and why I chose to do it this way.  The final section of code change comes at the end of the script.  This is where the html file is finally built, and then saved out to the file-system.

[codesyntax lang=”tsql”]

[/codesyntax]

In this section, I am enabling xp_cmdshell if necessary.  I am also performing one more necessary trick.  I am using xp_cmdshell to flush bad dns records and ping a remote host.  I will be saving the file off to a central repository and found some bad dns records on my servers while doing this process.  By adding this step, I saved myself quite a bit of frustration in the long-haul.  After that, I use xp_cmdshell to bcp the results out to file.

[codesyntax lang=”tsql”]

[/codesyntax]

This took some work to get the ” ‘ ” all lined up correctly and working properly with BCP.  It was somewhat satisfying when it finally came together.

Now, remember I said you should reset xp_cmdshell back to disabled once completed?  Well, I built that into the script as a part of the cleanup.  I perform this action right before dropping all of those tables that I created.

[codesyntax lang=”tsql”]

[/codesyntax]

Conclusion

I effectively took a well working script and made it suit my needs / wants just a little better.  The initial code was just over 300 lines and I nearly doubled that with this script.  Is it worth the extra effort?  Yes!  Though it took some time and effort to make these modifications, I was able to finish auditing the servers well ahead of pace of doing it by hand.  Furthermore, I can still use this script and continue to reap the benefits of having taken the time to modify it.  Can the script be improved?  Sure it can.  I have a few things in line for it currently.  The biggest piece of it will be modifying it to be run from the inventory package I am still trying to finish in my spare time.

You can download the script in its entirety here.

Edit: Fixed some WP formatting issues.

page 2 of 2»








Calendar
June 2017
M T W T F S S
« May    
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, June 29, 2017