Is it safe to grant Administer Bulk Operations?

4

November 10, 2016 by Kenneth Fisher

First I guess we had best start with definitions right? The permission Administer Bulk Operations and the role bulkadmin are required (one or the other) to perform bulk imports. Nothing more, nothing less. There is a really nice comparison of the two here.

So as with all permissions we only grant them if there is an actual need right? And the best practice of least privilege says that if someone has to be able to do a bulk load on a table then we should grant the bulk load to that one table right? There’s the rub. Bulk admin permissions are at the instance level and are not granular in any way. Ie you can’t grant it specifically to a single database or table. It’s all or nothing.

Now at first glance that seems like a real problem. But if you think about it for a bit longer it’s really not such a big deal. In order to perform a bulk import you actually have to have three permissions (at minimum).

  • CONNECT – Have to get into the database in the first place right?
  • INSERT – Yes, you actually have to have permission to put data into the table in order to do a bulk insert, just like a regular insert.
  • ADMINISTER BULK OPERATIONS – Last and in my opinion least.

 
So in my opinion it’s pretty harmless. You have to already be able to connect to the database and insert data into the table for this to have any effect. I wouldn’t grant anything unless there’s a specific need, but once there is one I wouldn’t be overly cautious about granting this one.

4 thoughts on “Is it safe to grant Administer Bulk Operations?

  1. Solomon says:

    Hey there, Ken. While this statement:

    “Bulk admin permissions are at the instance level and are not granular in any way. Ie you can’t grant it specifically to a single database or table. It’s all or nothing.”

    is technically true, that doesn’t mean that you can’t effectively grant these types of permissions (or any set of instance-level and/or database-level permissions) in a very granular and controlled manner.

    By using Module Signing (i.e. Certificates and ADD SIGNATURE) you can create a Login and/or User as a proxy for the desired set of permissions. The Login and/or User are created from the same Certificate that is used to sign the Stored Procedure / Function / Trigger / etc. That association will grant the permissions assigned to the Login and/or User to any module that is signed from that same Certificate. Meaning, if you only sign a Stored Procedure that bulk inserts into one particular table, then you are “effectively” granting the “bulk operations” / “bulkadmin” permission to just that table.

    This approach does allow for very granular control over permissions because the Certificate-based Login and/or User(s) cannot connect / logon or be impersonated via EXECUTE AS. So the permissions granted to the Login and/or User(s) is only ever applied to the modules that are signed with the same Certificate. And so, if you only grant EXECUTE on that Stored Procedure to one User or Role, then you are, again, “effectively” granting that permission to just that User or Role.

    To see this in action, I have examples posted in the following DBA.StackExchange answers:

    * http://dba.stackexchange.com/questions/62230/what-minimum-permissions-do-i-need-to-provide-to-a-user-so-that-it-can-check-the/103275#103275

    * http://dba.stackexchange.com/questions/154878/sql-server-impersonation-is-just-not-working/154884#154884

  2. Mark D Powell says:

    I do not believe that bulk inserts should ever be allowed on a production database if forward recovery to the point of failure is part of the service level agreement. From the moment a bulk operation takes place until you take a new backup the database is non-forward recoverable. Yes, you can run a differential backup however until it completes your shop is at risk of data loss. When you know exactly when the bulk operation is performed you might be able to schedule the Differential backup but what happens when the bulk operations happen on demand?

    • I’ve been wrong before but I believe that is only the case if you have the database in bulk recovery mode. The bulk admin permission gives you the ability to use the bulk insert commands but does not determine if minimal logging occurs. It also doesn’t affect recovery.

  3. […] SQL 2014 Learning Series 1: CONNECT ANY DATABASE SQL 2014 Learning Series 2: SELECT ALL USER SECURABLES New SQL Server 2014 Permissions: CONNECT ANY DATABASE Is it safe to grant ADMINISTER BULK OPERATIONS […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,628 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: