Release Date: 11/29/2004
| Update Date: 11/29/2004
Bart Duncanmicrosoft Corporation
Summary: Hand-columnist BART Duncan with Microsoft Product Support Services BRT Duncan Reviews Some best ways, using them to make Transact-SQL code to resist attacks.
This page
Introduction Protection Development SQL Server Safety Use the Minimum Permissions Account Compliance with the best way to protect T-SQL to learn the T-SQL command sub-reference with unique safety considerations
Introduction
There is a lot of good information sources on how to deploy SQL Server in a secure manner. However, the target users of these resources are usually those database administrators who have implemented their own application to protect tasks. In addition, there are still a lot of content to discuss how to write secure .NET and ASP.NET code, including access to the .NET code of SQL Server. However, many such resources are concerned about the data access code running on the application server, rather than the Transact-SQL (T-SQL) code executed in SQL Server. This column projected the attention to how to develop T-SQL code that is safely running on SQL Server.
Back to top
Protection development SQL Server security
The first step in developing security T-SQL is to protect the security of developing SQL Server. Why do you want to lock a SQL Server instance that does not save real data, and never demonstrate it to end users? This is because this will force you to write a safer T-SQL, and the application will also be easily protected when you deploy your application to the production. Below is a few specific steps, you can quickly protect the development server in these steps:
•
In developing or tested SQL Server, at least there should be a running, the latest Service Pack, and SQL security patch, so that your customers can successfully run your application on this SQL Server version.
•
By default, SQL Server 2000 Service Pack 3a disables unsafe features called "Cross Database Hold Link". When installing SP3 on the development server, if the Service Pack disables the Cross Database Ownership Link, you will help verify that you are writing T-SQL code based on secure server configuration.
•
A simple way to find a common security configuration issue on SQL Server is to run Microsoft Baseline Security Analyzer for the server. In addition to this method, you can use the resources listed in this column "Reference" section; these resources provide some additional steps to help you protect the security of developing SQL Server.
Typically, the best way to protect the security of the development server is as good as it is running in the production environment. The closer yourself from this goal, then you can confident on the code you develop, you can run normally in a safe production environment.
Back to top
Development as a minimum authority account
During the development process, everyone is fascinated using an account with Sysadmin or DBO SQL Server permission until the deployment is previously a lower account. There is a problem with this method: the designer's permission set is reduced to the lowest desired permission set and compared to the former more than in the development application process.
In view of the deployment application, you have to decide which permissions can be canceled, so don't develop T-SQL code using the SQL Sysadmin account. If you use the SQL sysadmin account, you may cause this result, that is, the application runs more privileged accounts than the required permissions. Therefore, please change the account with the least authority when developed.
When using such an account, you will gradually increase the specific permissions to perform, with exec (execution), some required stored procedures, SELECT, etc. from some tables. Please write these GRANT statements so that the same minimum authority can be easily deployed into the production environment without any guessing operation.
This concept is equally applicable to test. When performing temporary tests and more complex tests, the permissions and user privileges used by the account should be identical to the permissions and user permissions owned by the accounts used in the production environment.
Another advantage of using the minimum authority account during development is that you can avoid accidentally writing code that requires dangerous privileges or excessive permissions. For example, suppose you need to interact with third-party COM components in T-SQL. To this end, a method is to send a SQL batch command that directly calls sp_oacreate and sp_oamethod to manipulate the COM object. In the development environment of the SQL Server using the SYSADMIN account, the above method effect is good. However, when you try to prepare an application that has been developed, you will find that if you use a lower account, the method will not work. In order to allow the app to use a non-SYSADMIN account to run normally in the production environment, you must explicitly grant Execute permissions for sp_oacreate. Consider thinking if a user finally found a method, you can use the application to log in to perform any code, and use this permission to instantiate a COM object similar to Scripting.FileSystemObject for SQL Server, what will happen?
Back to top
Follow the best way to protect T-SQL
Defense a series of security vulnerabilities called "SQL injection" are crucial. Typically, you will use multiple layers to resist SQL injection attacks:
•
Perform the user provides input verification (for example, forced data type and maximum string length).
•
Symnas may have a character sequence for the database engine. In T-SQL, the most commonly used two strings in the injection attack are single quotes characters (') and comment character sequences (-).
•
In the T-SQL statement, do not use the value provided by the user inline. Please use the pre-processing statement and parameterization.
SQL Injection Attack has a detailed instructions in some other places, so I will not spend a lot of time to discuss the details of this problem. However, it is necessary to emphasize that the SQL injection problem is not limited to the T-SQL query constructed in the application layer. SQL injection problems may occur if a part of the T-SQL query constructed by the user provides a value. That is to say, a stored procedure to perform the query can be attacked by an Exec () command or the SP_EXECUTESQL stored procedure may also be attacked. Please refer to the "Reference" section Get some resource links, which provide examples of various SQL injection attack types, and some protection code is provided from these attacks.
Another best way is to avoid granting permissions for the base table. For queries you want users to perform, you should pack it in the stored procedure and just grant these stored procedures to Execute permissions. If you are working in this guide, even if the user tries to skip your app, log in directly to the database, and they cannot avoid any data verification, audit, business rules, or row-level security restrictions you have built in the stored procedure.
Back to top
Understand the T-SQL command with unique safety considerations
There are some T-SQL commands and extensions that have their own unique security considerations. One is sp_oacreate and its related system process series (such as sp_oamethod, sp_oaproperty, etc.). In the past, we have studied a potential security issue, and the security issue will be brought to the privilege of directly accessing these processes directly. In order to avoid this problem, do not write application code that directly call the sp_oa process, and will pack all the references to these processes during your own T-SQL stored procedure, and only grant access to these package stored procedures permission. Also, please do not allow the application code to pass the name of the COM object or method as a string that can be invigible to be invigible by the packaging process. Another built-in SQL Server extension with a unique security set is xp_cmdshell. This system stored procedure can run any executable or system command. Due to some obvious reasons, EXEC permissions on XP_cmdshell defaults only to sysadmin users only, and must be awarded this permission for other users. If you need an application to run a specific command or utility on the SQL Server, please note that you do not build an XP_cmdshell direct access to the application. Such risks are similar to the risk of directly accessing sp_oacreate. Once an Exec permission of XP_cmdshell is granted for an account, the account not only performs a specific command you want to access, but also enable hundreds of operating system commands and other executable. Similar to sp_oacreate, always call the XP_cmdshell call to another store, avoiding the Execute permissions directly on XP_cmdshell.
You should also avoid stringing the string parameters provided by any user or the string parameters provided by the application with the command to be executed by xp_cmdshell. If the above requirements cannot be reached, you must understand that there is a potential code injection attack specifically for XP_cmdshell (at least in SQL Server). The following stored procedures are case:
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 packed the XP_CMDShell call in your own stored procedure and only for the USP_DOFILECOPY store process, you have prevented the user from calling XP_cmdshell directly to perform any command. However, the following shell command is inserted into an example:
Exec USP_DOFILECOPY @FileName = '& del / S / Q // DEST / Share / &'
Using this @FileName parameter, the string to be executed is COPY // SRC / Share / & del / S / Q // dest / Share / & // dest / share. The summary (&) is processed by the operating system command interpreter as a command separator, so the string will be treated by cmd.exe as three mutually disconnected commands. The second command (DEL / S / Q // DEST / Share /) will try to delete all the files in // dest / share. The user can still perform any operating system commands by inserting a vulnerability in an a shell command in the stored procedure. One way to defense for such attacks is to pack the command string in a T-SQL function, as shown below. This user-defined function adds a shell escape (^) to escape any of any & characters or other characters with special meaning. - Function: Fn_escapecmdshellstring
- Description: returns an escaped version of a given string
- with cauts ('^') Added in Front of All The Special
- Command shell symbols.
- Parameter: @command_string nvarchar (4000)
-
Create function dbo.fn_escapecmdshellstring (
@command_string nvarchar (4000)) Returns nvarchar (4000) AS
Begin
Declare @escaped_command_string nvarchar (4000),
@curr_char nvarchar (1),
@Curr_CHAR_INDEX INT
SELECT @escaped_command_string = n '',
@Curr_Char = n '',
@curr_char_index = 1
While @curr_char_index <= len (@command_string)
Begin
SELECT @curr_char = substring (@command_string, @curr_char_index, 1)
IF @curr_char in ('%', '<', '>', '|', '&', '(') ',' ^ ',' ")
Begin
SELECT @escaped_command_string = @escaped_command_string n '^'
End
SELECT @escaped_command_string = @escaped_command_string @curr_char
SELECT @curr_char_index = @curr_char_index 1
End
Return @escaped_command_string
End
Here is the stored procedure after the command shell inserts the vulnerability:
Create Procedure USP_DOFILECOPY @FileName VARCHAR (255) AS
Declare @cmd varchar (8000) set @cmd = 'copy // src / share /'
DBO.FN_ESCAPECMDSHELLSTRING (@filename)
'// dest / share /'
EXEC MASTER.DBO.XP_CMDSHELL @cmd
The third T-SQL command set with unique safety considerations is a command that allows a dynamically built query: EXEC () and sp_executesql. The risk of SQL injection attack is not the only reason to avoid dynamic SQL. Any query dynamically executed through these commands will run in the current user's secure context, not running in the context of the stored procedure owner. This means that using dynamic SQL may force you to grant the user directly access the base table. The following stored procedures are case:
Create Proc dbo.usp_retrievemyuserinfo as
Select * from userinfo where username = user_name ()
This process restricts the current user so that it cannot view any other user's data. However, if the SELECT statement in this process is performed by dynamic exec () or via sp_executesql, you must grant the user's direct Select permissions for the USERINFO table, because this dynamic execution query is in the current user's security context In progress. If the user can log in to the server directly, they can use this permission to skip the row-level security provided by this stored procedure to see all users' data.
Back to top
summary
All in all, the following recommendations will help you develop T-SQL code that is safely running in SQL Server:
•
Protect your development of SQL Server, as if it is a production server. This helps to ensure that you develop secure code, you can also help you define the minimum authority set required for your application.
•
Use the SQL Server account with the lowest authority when performing T-SQL development and test. Do not use the SYSADMIN or DBO account.
•
It is important to note, such as sp_oacreate and xp_cmdshell, for the storage procedure to be permitted to perform any other code, such as sp_oacreate and xp_cmdshell. If you have to use these extensions, you must consider their unique security hazards.
•
Follow the best way to protect T-SQL development, including: transfer the data provided by the user with explicit parameters, writing code that avoids SQL injection attack, avoiding unnecessary dynamic SQL, granting access stored procedures Permissions do not grant permissions to directly access the base table.
•
Safe T-SQL can make a secure application. With the following resources, you can ensure your server is secure and make sure you have a secure database client application.
Back to top
Reference
SQL Server Security Resource Page
Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication
SQL Server 2000 SP3 Security Features and Best Practices: Secure Multi-Tier Deployment
SQL Server for Developers
Bart Duncan has been engaged in SQL Server product support in the past six years, recently promoted to Escalation Engineer (senior engineer). He lived in Dallas in Texas with his wife.
