Tuesday 31 January 2012

Admin: Bulkadmin vs ADMINISTER BULK OPERATIONS

I had an application thrust upon me recently which required the user executing it to have permissions to use the BULK INSERT command in T-SQL. I was aware of the server role bulkadmin which would have been a nice sledgehammer approach to crack this nut, but I was hoping for something a little more refined. Unfortunately, the only thing I came up with was the ADMINISTER BULK OPERATIONS command which would grant this privilege without having the server role membership but I was curious as to what the difference between the 2 was.

As far as I can tell, there is no difference between the 2 operations and both seem to achieve the same thing. From this I assume that bulkadmin is just a wrapper around the ADMINISTER feature although I'd be interested in knowing (as I couldn't find out) if there are other permissions the server role gives you.

Lets look at the example (running on SQL2008 SP2)

-- create some test logins
CREATE LOGIN [DOMAIN\user1] FROM WINDOWS
GO

CREATE LOGIN [DOMAIN\user2] FROM WINDOWS
GO

-- lets impersonate a user
EXECUTE AS LOGIN = 'BARRHIBB\user1'
GO
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO

SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO 
-- does the user have bulk op permissions? 
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO


-- lets go back to being a superuser 
REVERT
GO

USE MASTER
GO
-- how about if we just grant administer bulk operations to the user? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO

-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO

SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO



-- can the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO


Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.


So lets see what happens with the server role:

REVERT
-- remove the users permissions 
REVOKE ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
 

-- now add the user to the bulk admin role and check permissions again
EXEC MASTER..sp_addsrvrolemember @loginame = N'DOMAIN\user1', @rolename = N'bulkadmin'
GO
 -- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO

SELECT * FROM fn_my_permissions(NULL,'SERVER') 
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO




NB: The main difference here is that the permission has not been explicitly granted but it is an effective permission.

-- perhaps with the server role the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO


Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.


So it seems that the bulkadmin server role doesn't offer any "admin" from a security point of view which begs the question, just why would you add a user to this role rather than just explicity grant them the permission through the GRANT statement (or vice versa)?

1 comment:

  1. Hi

    Have you tried the bulk insert from various folders. The read permissions of bulk admin and bulk operations seem to differ. For some windows folder bulk operations alone doesn't help in importing the files using bulk insert command. While when we use bulk admin it works all the time. I'm still trying to figure out the reason.

    Thanks
    Ravi

    ReplyDelete

/* add this crazy stuff in so i can use syntax highlighter