Why BULK INSERT is Considered Dangerous in SQL Server

bulk-insertimportpermissionssql server

I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database programmers? I can't believe the "filling up the disk abuse" excuse, unless I'm missing something, as the end result is no different than an application doing something like:

for (long i = 0; i < LONG_MAX; ++i)
    executeSQL("INSERT INTO table VALUES(...)");

and INSERT is a common DML command that anyone with basic write permission can execute.

For an application's benefit, BULK INSERT is far more efficient, faster, and relieves the programmer of the need to parse files outside of SQL.

Edit: I originally asked this question on the information security site for a reason – it is not the DBA's that are against using BULK INSERT, it is the "Information Assurance" (IA for short – the Cybersecurity folks) that are forcing the issue. I'll let this question stew for another day or two, but if the bulk operation does indeed bypass constraints or triggers, I can see that being an issue.

Best Answer

Given that there are probably just as many unfounded fears as there are unknown risks, I would think that it is difficult to really say why a policy is in place without asking whomever created the policy why they are concerned.

However, I would guess that it probably has something to do with what BULK INSERT / SqlBulkCopy / BCP / OPENROWSET(BULK ...) allow someone to do, namely:

  1. disable constraints (CHECK, DEFAULT, and FOREIGN KEY I believe)
  2. disable triggers (if there are audit triggers in place, by-passing them would probably be deemed undesirable; for more explanation on this particular issue, please see @DVKs answer)

The various options are described in the following documentation:

I did not mention the table locking issue noted by @RDFozz since that is not specific to BULK INSERT: anyone can table a TABLOCK / XLOCK or set the TRANSACTION ISOLATION LEVEL to SERIALIZABLE.

UPDATE

I came across two additional pieces of information that might help narrow this down:

  1. The issues of being able to disable Triggers, disable Constraints, and set IDENTITY_INSERT ON might not be an overwhelming reason to see ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS (starting with SQL Server 2017), or the bulkadmin server role as a threat. The reason is that in order to do any of those three things just mentioned, the User needs to have ALTER TABLE permissions on that Table, or on the Schema that the Table exists in. Ownership chaining does not cover DDL modifications. So, if the User doesn't have ALTER TABLE, then the ability to do these three things is a non-issue.

  2. What hasn't been discussed so far, and what might ultimately be the security issue is that both BULK INSERT and OPENROWSET(BULK... access external resources, outside of SQL Server. When accessing SQL Server via a Windows Login, that Windows account will be impersonated (even if you switch the security context using EXECUTE AS LOGIN='...') for doing the file system access. This means that you can only read files that you have been given permission to read. Nothing wrong with that.

    BUT, when accessing SQL Server via a SQL Server Login, then the external access is done in the context of the SQL Server service account. This means that someone with this permission could read files that they should otherwise not be able to read, and in folders that they should not have access to.

    At minimum, if SQL Server was set up to run as an account created just for SQL Server (the preferred method), then such a User could read only those files that the "SQL Server" account has access to. While this is a limited problem, it still allows for reading files such as SQL Server log files (and I did test the following example and it does work):

    SELECT tmp.[Col1]
FROM   OPENROWSET(BULK
   N'C:\Program Files\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Log\ERRORLOG.1',
                  SINGLE_NCLOB) tmp([Col1]);

    Most people won't have access to the MSSQL\Log folder, so this would be a way to circumvent existing security restrictions.

    And, if SQL Server is running as the Local System account, then I suspect that the scope of the problem only increases, and that a User with this permission would be able to read a wide range of system-related files.

    AND, this is likely why the other methods of doing bulk imports — BCP and SqlBulkCopy — do not require the bulkadmin permission / role: they are initiated outside of SQL Server and will handle file system permissions on their own. In those cases, SQL Server never reads the file (or reaches outside of SQL Server), it just receives the data to import from the file that is being read by the external process.

Possible implications aside, it was said in the question:

For an application's benefit, BULK INSERT is far more efficient, faster,..

Ok, go on...

and relieves the programmer of the need to parse files outside of SQL.

Whoa Nelly. Let's stop right here. T-SQL is usually not the best choice of languages for parsing. It is often best to do the parsing before inserting stuff into the DB. One way to do this is to use Table-Valued Parameters (TVPs). Please see my answer to another question (here on DBA.StackExchange) that deals with topic of pre-parsing and validation along with efficient bulk importing of said data:

T-SQL: CSV->table pipeline with custom parsed numeric data, lookup values

Related Question