21

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.

migrated from security.stackexchange.com Oct 30 '17 at 15:39

This question came from our site for information security professionals.

19

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

  • With replication in place there are additional considerations to be made for bulk insert. – mustaccio Oct 30 '17 at 17:49
6

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.

  • Hi there. I'm not disagreeing with your description of what is undesirable here, and I do appreciate the detail, but just for the record, I did state in my answer that disabling triggers would be undesirable if there were audit triggers. True, that is not a detailed explanation, but not exactly accurate to say that that it was "not explained". Maybe better to say that it was too simplistic, or did not provide enough explanation regarding audit triggers, and did not mention validation (and +1 for mentioning that, and for the additional detail overall). Just a thought. – Solomon Rutzky Oct 30 '17 at 19:50
  • @SolomonRutzky - I specifically indicated that the answer "does not explain why" that is a problem :) If you can't define a business problem; technical details are often irrelevant, especially to OP who's having a business-based discussion with IA. In other words, if they say "oh, audit triggers may be disabled", IA will ask "what does tat mean for us?" - they aren't DBAs or developers, usually. – DVK Oct 30 '17 at 20:09
  • ok. I guess I just read that first sentence as saying that "the other answer mentioned disabling triggers is undesirable, but did not explain why". and yes, I generally agree with you about the need to properly define the issue, I was assuming (maybe not always the best policy ;-) that the OP understood the implications of disabling an audit mechanism. If I am incorrect, then thankfully you have provided that info here. And so I just updated my answer to link to this for that purpose :) – Solomon Rutzky Oct 30 '17 at 20:20
6

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.

2

One reason for this can be down to making logging of actions clear. If every action corresponds to one entry in a log file, it's fairly trivial to see something that caused a problem without any additional reference. If your log file says "INSERT FROM external.file", without external.file, you can't tell anything more.

Of course, you could modify how logging works, but as a starting point, enforcing every action to be atomic even within logging isn't a terrible idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.