sql 一次性批量插入_考虑使用SQL批量插入的安全性

sql 一次性批量插入

In this article, we’ll discuss security implications of using SQL Bulk Insert and how to mitigate those risks.

在本文中，我们将讨论使用SQL大容量插入的安全隐患以及如何减轻这些风险。

Like all ETL tools, any flat file insert can invite security risks relative to the design. In the third part of this series (see TOC) related to SQL Bulk Insert, we’ll look at how we can restrict ETL access to accomplish what we need, but without giving developers too many permissions. I will point out that this may not apply to every company, where security may not be a high priority due to the nature of the company or the nature of our data requirements for ETL flows. As we’ve seen in our series, SQL bulk insert does read and possibly write data (error handling), which can be used for nefarious purposes, if an attacker is given too much permissions.

像所有ETL工具一样，任何平面文件插入都会带来与设计有关的安全风险。 在与SQL大容量插入相关的系列文章的第三部分（请参阅TOC ），我们将研究如何限制ETL访问以完成所需的功能，而又不给开发人员太多的权限。 我将指出，这可能并不适用于所有公司，因为公司的性质或我们对ETL流的数据要求的性质，安全性并不是优先考虑的事项。 正如我们在本系列文章中所看到的，SQL大容量插入确实会读取和可能写入数据（错误处理），如果给攻击者过多的权限，则可用于邪恶目的。

This applies to other ETL tools as well. SSIS packages are XML data and these can be manipulated as well, along with providing very useful information depending on how an attacker might discover and view these files. ETL applications that use DLLs and other development files often involve configuration files that can also be manipulated, so security risks exist with any tool and creating a design that recognizes these risks is the best way to protect ourselves.

这也适用于其他ETL工具。 SSIS包是XML数据，也可以对其进行操作，并根据攻击者可能发现和查看这些文件的方式提供非常有用的信息。 使用DLL和其他开发文件的ETL应用程序通常涉及也可以操纵的配置文件，因此任何工具都存在安全风险，并且创建能够识别这些风险的设计是保护自己的最佳方法。

Developers like to build for convenience and this makes sense in some data contexts, but convenience is often easier to attack and uncover, so it may be less appropriate for some data contexts. We’ll look at some examples of adding layers of security around inserting data from files that may add work for our developers, but will be more appropriate in situations where security is more important than convenience.

开发人员喜欢为了方便而构建，这在某些数据上下文中是有意义的，但是便利通常更容易受到攻击和发现，因此对于某些数据上下文可能不太合适。 我们将研究一些示例，这些示例围绕从文件中插入数据来增加安全性，这些数据可能为我们的开发人员增加了工作量，但是在安全性比便利性更重要的情况下更合适。

Considering the Security Context

考虑安全上下文

We’ve looked at the SQL bulk insert ability to read from a file and write to our table. We’ll now look at this on the file level. In our examples, we see the below file path:

我们已经研究了SQL大容量插入功能，可以从文件读取并写入表。 现在，我们将在文件级别上对此进行研究。 在我们的示例中，我们看到以下文件路径：

Our file path where our flat files are stored.

我们的平面文件所在的文件路径。

While this file path serves as a useful location for us to load flat files, we should consider that the user account that is executing the underlying insert statement must be able to read (and possibly write to) that file location. The writing part of the equation comes in when it involves logging, even if the permissions of the written logging data are tied down strictly in the output, in that the user doesn’t control what gets written, but that errors are written. In the least, we want to ensure that a separate folder with strict permissions exists for any flat file import to restrict the account access – notice that we’re not reading off the root drive, as we’ve seen that we can insert an entire file of data – think about using SQL bulk insert to view files through SQL Server by inserting the file’s data and reviewing it.

尽管此文件路径是我们加载平面文件的有用位置，但我们应考虑到执行基础插入语句的用户帐户必须能够读取（并可能写入）该文件位置。 方程式的书写部分来当它涉及到日志中，即使写入日志数据的权限是在输出严格的束缚，在用户不控制一下被写入， 但错误写入。 至少，我们要确保为任何平面文件导入都存在一个具有严格权限的单独文件夹，以限制帐户访问权限–请注意，由于我们可以插入整个目录，因此我们并未读取根驱动器数据文件–考虑使用SQL批量插入来通过SQL Server查看文件，方法是插入文件的数据并进行检查。

This also means that when we’re done inserting our data from our files, we may want to migrate the files elsewhere, if we want to limit future reads. The risk of future reads is that attackers may infiltrate our system and, even with limited permission to a drive, be able to view past data we’ve inserted and used. Relative to our design, attackers may face limits on what they know about our systems if our load design is demarcated from other parts of the full data flow, such as a load server, transformation server, and presentation server. Just like with least permissions, when we consider how we store our files, we want least readability in that once a file has been read and saved to our load database or server, we want the file moved. And once we’ve completed what we need with our loaded data, we want it cleaned and migrated to our next step.

这也意味着，当我们完成从文件中插入数据后，如果我们想限制将来的读取，我们可能希望将文件迁移到其他位置。 将来读取数据的风险在于，攻击者可能会渗透到我们的系统中，即使在对驱动器的权限有限的情况下，也能够查看我们已插入和使用的过去数据。 相对于我们的设计，如果我们的负载设计与完整数据流的其他部分（例如，负载服务器，转换服务器和表示服务器）分开，则攻击者可能会在了解系统方面面临限制。 就像具有最小权限一样，当我们考虑如何存储文件时，我们希望可读性最低，因为一旦文件被读取并保存到负载数据库或服务器后，我们就希望文件被移动。 一旦我们完成了对加载数据的需求，就希望将其清除并迁移到下一步。

Using SQL Bulk Insert with Stored Procedures

在存储过程中使用SQL大容量插入

In the below code, in one query window we create an account and give it execute permission to a stored procedure that imports one of the files we have already created in the second part of this series. We’ll notice that the stored procedure does not allow input at all – it inserts data and returns a select of the data from the table, but the user cannot pass any information into the stored procedure. In a new query window with this new account logged in, we try the exact same flat file insert statement of our file without the stored procedure and the transaction fails. Even with the user assigned execute permissions to the stored procedure, the execution will still fail (see the next batch of code under “error thrown as well” in the comments).

在下面的代码中，在一个查询窗口中，我们创建一个帐户，并授予该帐户对存储过程的执行权限，该存储过程将导入我们在本系列第二部分中已经创建的文件之一。 我们将注意到存储过程根本不允许输入–它插入数据并从表中返回数据的选择，但是用户无法将任何信息传递给存储过程。 在登录了该新帐户的新查询窗口中，我们尝试使用与存储文件完全相同的平面文件插入语句，而没有存储过程，并且交易失败。 即使为用户分配了存储过程的执行权限，执行仍将失败（请参见注释中“错误也引发”下的下一批代码）。

---- Query window with database level permissions
CREATE TABLE etlImport5(
	VarcharSpefzTen VARCHAR(10),
	IntSpefz INT,
	BitSpefz BIT,
	VarcharSpefzMax VARCHAR(MAX)
)
 
CREATE PROCEDURE stpRestrictInsert
WITH ENCRYPTION
AS
BEGIN
	BULK INSERT etlImport5
	FROM 'C:\ETL\Files\Read\Bulk\daily_20180101.txt'
	WITH (
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		FIRSTROW = 2,
		MAXERRORS = 100
	)
 
	SELECT * FROM etlImport5
END
 
USE [master]
 
CREATE LOGIN etlRestrictedUser WITH PASSWORD = 'ShortPassword!'
 
USE [DatabaseThree]
 
CREATE USER etlRestrictedUser FROM LOGIN etlRestrictedUser
GRANT EXECUTE ON stpRestrictInsert TO etlRestrictedUser
 
 
--- New window with etlRestrictedUser logged in
USE DatabaseThree
GO
 
--- Errow thrown
SELECT *
FROM etlImport5
 
--- Error thrown
BULK INSERT etlImport5
FROM 'C:\ETL\Files\Read\Bulk\daily_20180101.txt'
WITH (
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n',
	FIRSTROW = 2,
	MAXERRORS = 100
)
 
--- Error thrown as well
EXEC stpRestrictInsert

We can neither select nor bulk load data, as we have permission to execute one stored procedure.

我们既不能选择数据也不能批量加载数据，因为我们有权执行一个存储过程。

We still get a bulk load error as more privilege must be granted in order to call this procedure.

我们仍然会收到批量加载错误，因为必须授予更多特权才能调用此过程。

The SQL bulk insert statement requires administer bulk operations and it also involves inserting data into a table. However, we don’t want to grant a data insert on the table, as it would mean that our user could insert data into the table without calling the procedure that we’ve restricted the user to. Because permission to the procedure will allow an insert, we will only grant the administer bulk operations permissions, which we’re required to do on the master database. We then call the stored procedure in the below code in our other window and we see it inserts and returns the select of the newly inserted data.

SQL批量插入语句需要管理批量操作，并且还涉及将数据插入表中。 但是，我们不想授予对表的数据插入权限，因为这意味着我们的用户可以在不调用限制用户使用的过程的情况下将数据插入表中。 因为对该过程的许可将允许插入，所以我们将仅授予管理批量操作许可，这是我们需要在master数据库上执行的。 然后，在另一个窗口中的以下代码中调用存储过程，我们将看到该存储过程将插入并返回新插入数据的选择。

---- Returning to query window with database level permissions
USE [master]
GRANT ADMINISTER BULK OPERATIONS TO etlRestrictedUser
 
--- Returning to window with etlRestrictedUser logged in post bulk operation grant
EXEC stpRestrictInsert

Our file inserts and shows the result of the select statement.

我们的文件将插入并显示select语句的结果。

If we try to import the flat file data with our new user even with being granted administer bulk operations, we see that we get a failure about being denied insert access to the etlImport5 table – this also shows why we don’t want to allow insert permissions, as we only want the user in this case to have access to the procedure:

如果即使被授予管理批量操作的权限，即使尝试使用新用户导入平面文件数据，我们也会看到无法拒绝对etlImport5表的插入访问的失败–这也说明了为什么我们不想允许插入权限，因为在这种情况下我们只希望用户有权访问该过程：

--- Even with administer bulk operation permissions, this fails
BULK INSERT etlImport5
FROM 'C:\ETL\Files\Read\Bulk\daily_20180101.txt'
WITH (
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n',
	FIRSTROW = 2,
	MAXERRORS = 100
)

The statement is denied because this statement requires explicit insert permission that hasn’t been granted, even if the account has administer bulk operations.

该语句被拒绝，因为该语句需要尚未授予的显式插入权限，即使该帐户已管理批量操作也是如此。

We can add parameters to our procedure that allow a dynamic file name or even allow us to specify a higher maximum amount of errors, but these invite risks even with strict parameterization. We must be extra cautious about validating the values that get passed into a stored procedure if we allow outside parameter. For an example, if we do allow a file path to be passed in, we should consider allowing only part of a file path such as the difference between a full file path and a file name only. Even then, we’re still allowing input so the risks of attack from internal and external sources exist.

我们可以在过程中添加参数，以允许使用动态文件名，甚至允许我们指定更高的最大错误数量，但是即使进行严格的参数设置，这些操作也会带来风险。 如果我们允许外部参数，我们必须格外谨慎地验证传递给存储过程的值。 例如，如果我们确实允许传递文件路径，则应考虑仅允许文件路径的一部分，例如仅完整文件路径和文件名之间的差异。 即使那样，我们仍然允许输入，因此存在来自内部和外部来源的攻击风险。

Permissions of Administer Bulk Operations

批量操作管理权限

The permission of administer bulk operations is a server level permission, according to Microsoft. However, this does not give the user access to do anything on the server level. For instance, with our etlRestrictedUse, we can try manipulating databases and see failures – like renaming DatabaseThree to Database_Three3.

微软表示，管理批量操作的权限是服务器级别的权限。 但是，这不能使用户有权在服务器级别执行任何操作。 例如，通过我们的etlRestrictedUse，我们可以尝试操作数据库并查看失败-例如将DatabaseThree重命名为Database_Three3。

Our server level permission does not allow us to do anything we want.

我们的服务器级别权限不允许我们做任何我们想做的事情。

We also saw in the above code that with our etlRestrictedUser we couldn’t import flat file data into our etlImport5 table because insert permissions weren’t allowed. In the same manner, we can’t create objects either – as that permission wasn’t granted. Notice what happens when we try to create a table etlImport6 in DatabaseThree:

在上面的代码中我们还看到，使用etlRestrictedUser我们无法将平面文件数据导入到etlImport5表中，因为不允许插入权限。 同样，我们也无法创建对象-因为未授予该权限。 注意当我们尝试在DatabaseThree中创建表etlImport6时会发生什么：

CREATE TABLE etlImport6(
	VarcharSpefzTen VARCHAR(10),
	IntSpefz INT,
	BitSpefz BIT,
	VarcharSpefzMax VARCHAR(MAX)
)

Even if I switch to master, I still get the same error.

即使我切换到主服务器，我仍然会遇到相同的错误。

So even though the user has permissions to execute the SQL bulk insert statement, without authorization to any tables and without authorization to create tables, for these two security contexts, the permission may be appropriate. This doesn’t only prevent external threats that could compromise an account, it also prevents internal threats. For very strict security environments, we should still consider only allowing high-level employees the right to have permission to execute these flat file tools. From intellectual property theft to private information, some development contexts may require developers to have very little access, as they could reveal information that should not be revealed externally.

因此，即使用户具有执行SQL批量插入语句的权限，没有对任何表的授权，也没有创建表的授权，对于这两个安全上下文，该权限可能是合适的。 这不仅可以防止可能危害帐户的外部威胁，还可以防止内部威胁。 对于非常严格的安全环境，我们仍应考虑仅允许高级别员工有权执行这些平面文件工具。 从知识产权盗窃到私人信息，某些开发环境可能要求开发人员具有很少的访问权限，因为他们可能会泄露不应在外部公开的信息。

摘要 (Summary)

In this tip, we’ve looked at the security context of using SQL bulk insert by creating a SQL authentication account, assigning it basic permissions for one insert, and testing the ability of the created user to complete the insert and view the result. In other words, we’ve looked at giving developers the ability to do some tasks, but significantly restricting what they’re able to do in security contexts where this may be appropriate. This doesn’t only protect us against outside attackers – it also protects us against internal attackers or internal threats who state things that should remain confidential. While it comes with costs that limit our ability to do things quickly, in some contexts, we may favor security over performance and this “cost” is actually a benefit for those contexts.

在本技巧中，我们通过创建SQL身份验证帐户，为它分配一个插入的基本权限以及测试创建的用户完成插入和查看结果的能力，来研究使用SQL批量插入的安全性上下文。 换句话说，我们着眼于为开发人员提供执行某些任务的能力，但在可能的情况下大大限制了他们在安全上下文中的能力。 这不仅可以保护我们免受外部攻击者的侵害，还可以保护我们免受内部攻击者或内部威胁的威胁，这些内部攻击者或内部威胁声明应保密的内容。 尽管它带来的成本限制了我们快速执行操作的能力，但在某些情况下，我们可能更倾向于安全性而不是性能，而这种“成本”实际上是对这些情况的好处。

目录 (Table of contents)

T-SQL’s Native Bulk Insert Basics

Working With Line Numbers and Errors Using Bulk Insert

Considering Security with SQL Bulk Insert

SQL Bulk Insert Concurrency and Performance Considerations

Using SQL Bulk Insert With Strict Business Rules

T-SQL的本机大容量插入基础知识

使用批量插入处理行号和错误

考虑使用SQL批量插入的安全性

SQL批量插入并发和性能注意事项

使用具有严格业务规则SQL批量插入

翻译自: https://www.sqlshack.com/considering-security-with-sql-bulk-insert/

sql 一次性批量插入