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:
- disable constraints (
FOREIGN KEY I believe)
- 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
I came across two additional pieces of information that might help narrow this down:
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.
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):
N'C:\Program Files\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Log\ERRORLOG.1',
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