Friday, December 11, 2015

Global Temp Tables are a Security Risk

Courtesy http://gratisography.com/
I was working on a project to use bcp and xp_cmdshell to export data. Along the way, stumbled on a great idea to create reusable code and a stored procedure to export the results of any query to a delimited file -- I'd format the data into a global temp table  and export it with  bcp.  I even started working on a stored procedure that would accept parameters for the temp table name, delimiter, and export file name. Great. It's good, and it's fast, its reusable, and, I realized as I was working on it, it's a huge security problem.

The problem is the global temp table.  Here's what Microsoft says about global temp tables in SQL 2008 (the version I'm currently using) :

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. [Link]

So what this means is that once I populate my temp table anybody can look at the data.  Not good. I realize that if it's not sensitive data, it's not a big deal. If I use it wisely there shouldn't be a problem. But, once I put it in a stored procedure someone else may want to use it, and they may not realize that this is insecure. I bet this is how security vulnerabilities happen.


Before you flame me, I realize that there are security concerns about the use of xp_cmdshell but those can be mitigated by proper control of the sysadmin role.


More on BCP, if you are interested.

Read this entire thread if you are interested in learning more about xp_cmdshell security issues.

No comments:

Post a Comment