SQL Server TSQL security concerns - highlights for a newbie reference
Here it is first post in the year 2009, still I would like to visit the good old advice of securing your SQL Server environment.
For any user in IT, Security must be a top priority in order to ensure their PC or environment is secured. You might have heard or aware of SQL Injection attacks, also you might find plenty of good sources of information about how to deploy SQL
Server in a secure fashion. Few relevant blog posts here:
However, these resources are often targeted
at DBAs tasked with securing already developed
applications. For the users and Developers it is most important to cover up on the aspects of rich body of information that
discusses writing secure .NET and ASP.NET code, including .NET code
that accesses SQL Server, so I tend to give a brief overview of developing
T-SQL code that runs securely on SQL Server is the primary focus.
I bet that most of Development SQL Server environment in any Enterprise is not patched upto the latest service pack or at the least matching with Production SQL Server. It is a general tendency that why bother locking down a SQL Server instance that doesn't hold
real-world data and will never be exposed to end users? This is not at all acceptable in terms of your policies to make every environment secure, after all this is most required because this
will force you to write more secure T-SQL, and it will also make it
much easier to secure your application when it is deployed to
production.
So how to go about securing the platform, that is not a production related. It is a best practice is to avoid granting permissions on base
tables. You should wrap the queries you want the user to be able to
perform within stored procedures, and only grant EXECUTE permissions on
these stored procedures. If you follow this guideline, even if a user
does manage to bypass your application and log in to the database
directly, they will not be able to sidestep any data validation,
auditing, business rules, or row-level security restrictions that you
have built into your stored procedures.
There are a few T-SQL commands and extensions that present their own
unique security concerns. One of these is sp_OACreate and its related
family of system procedures (e.g., sp_OAMethod, sp_OAProperty,
etc.). Though this is related to SQL Server 2000 version, still I believe there are many such installations that are running on the production. It is better wrap all references to
these procedures in your own T-SQL stored procedures, and only grant
access to these wrapper stored procedures. Also, do not allow the
application code to pass in the names of COM objects or methods as
strings that are blindly invoked by the wrapper procedure.
A second built-in SQL Server extension that has a unique set of
security risks is xp_cmdshell, though majority of installation might have lock down this issue. For obvious reasons, EXEC
permissions on xp_cmdshell default to sysadmin users only, and
must be explicitly granted to other users. If you have a need for your
application to run a specific command or utility on the SQL Server, be
careful not to build a dependency on direct access to xp_cmdshell into
your application. You should also avoid concatenating any user- or
application-supplied string parameters to the command that will be
executed through xp_cmdshell.
Check the following code:CREATE PROCEDURE usp_DoFileCopy @filename varchar(255) AS
DECLARE @cmd varchar (8000)
SET @cmd = 'copy \\src\share\' + @filename + ' \\dest\share\'
EXEC master.dbo.xp_cmdshell @cmd
GO
GRANT EXEC ON usp_DoFileCopy TO myapplogin
By wrapping the xp_cmdshell call in your own stored procedure and only granting EXEC permissions on the usp_DoFileCopy
stored procedure, you have prevented users from directly calling
xp_cmdshell to execute arbitrary commands. However, consider the
following example of shell command injection:
EXEC usp_DoFileCopy @filename = ' & del /S /Q \\dest\share\ & '
For the reference the following suggestions can help you develop T-SQL code that runs securely on SQL Server:
- Secure your development SQL Server as if it was a production
server. This helps ensure you are developing secure code. It will also
help you define the minimum set of permissions that your application
needs to function properly.
- Use a minimally privileged SQL Server account for T-SQL development and testing. Do not use a sysadmin or dbo account.
- Be wary of stored procedures like sp_OACreate and xp_cmdshell
that allow T-SQL to execute arbitrary external code. If you must use
these extensions, be sure you have accounted for their unique security
implications.
- Follow best practices for secure T-SQL development,
including: passing user-supplied data as explicit parameters, coding to
avoid SQL injection attacks, avoiding unnecessary use of dynamic SQL,
and granting access to stored procedures instead of direct access to
base tables.
- Secure T-SQL is only one part of a secure application. Take
advantage of the resources below to ensure that your server is securely
configured and that you also have a secure database client application.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.