Why is BULK INSERT Considered Dangerous?

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:

• SqlBulkCopyOptions Enum
• BULK INSERT

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

Another possibility is the impact of running a BULK INSERT operation.

Normally, this sort of thing would be run off-hours when possible, so as not to interfere with normal activity. A bulk insert might lock a table for hours, preventing other inserts from happening (as well as select, updates, or deletes).

Or, from a security perspective, it can produce very similar results to a DoS attack.

Admittedly, one can do this either accidentally or deliberately with transactions and simple INSERT statements. However, using a bulk insert process as intended can cause this effect.

Generally, I'd expect a DBA to be involved in organizing off-hours activity, as they also need to make sure backups and other scheduled jobs all complete. If people were to schedule this sort of thing without sufficient consideration for such activities, you could see backups fail - which can be a problem for a number of reasons.

This was kind of alluded to in an earlier answer ("... disable triggers"), but does not explain why disabling would be un-desirable from business standpoint.

In many businesses, triggers on main table are used to:

1. Validate integrity constraints (those with business logic more complicated than is usually used in DB constraints)

2. More importantly, to audit the data, specifically to insert data into corresponding audit table (or update audit fields in main table).

It's rather obvious what the problems with the former is (your application is liable to insert bad data which has negative effects on downstream processing). As far as the latter, if a trigger is disabled, you don't have any auditing information, which poses two problems from audit perspective:

• First of all, audit as a group can no longer audit the data changes and thus are unable to perform their primary function as Internal Audit.

• Second, lack of audit records may be a breach of auditing requirements that a company is governed by (e.g. SAS 70) - which may make your company liable for violating the contracts.