What security vulnerabilities are there with IDENTITY_INSERT and DBCC CHECKIDENT

Posted on

Question :

I’m writing some simple C# code through a website to take an excel file and update a table on a database. My system admin seems to not want to give me access to pretty much anything including the two things above stating that I need a Security+ certificate in order to run those commands.

I’ve been doing this a long time (15 years), first I’ve ever run into a problem like this, and so this seems really really weird and strange.

I already can update, insert, delete and do modifications to tables as I have been given some access to the database. So what if any are the security implications of these additional permissions? Can someone explain the reluctance of the SA to grant me these permission? Please enlighten me, because at this point I’m just scratching my head trying to figure out why the application cannot be granted these rights.

Answer :

DBCC CHECKIDENT

Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

MSDN DBCC CHECKIDENT reference

IDENTITY_INSERT

User must own the table or have ALTER permission on the table.

MSDN Set IDENTITY_INSERT reference


When a user can delete, update, insert, and select records, you are granting the user the ability to manipulate the data within the table.

When a user has alter, they can adjust the columns, data types, seeding information, and much more. Best practice dictates lowest amount of permission necessary to achieve a function so that there is less opportunity that can be exploited.

Now if you can already modify the table, I’d be interested to see how he locked the permissions down so you could not run at least the IDENTITY_INSERT commands.

The least amount of permission you need to achieve these goals is DB_DDLADMIN which can open some security implications across the database server. You would also need ALTER TABLE privileges, this is less powerful than DB_DDLADMIN since it can be isolated to a single table and not server wide, but not without potential vulnerabilities.

Leave a Reply

Your email address will not be published. Required fields are marked *