0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • That is strange. I am able to alter the table to add and modify columns. That is what I don't understand. How can someone exploit these permissions from a web front end? The operations I'm performing are set through the application itself, so I don't see how someone could do anything besides press the button I created on the UI to do what I'm trying to do. I would think they would have to do some sort of SQL Injection? And then someone would reseed the table and then what? Nothing I'm guessing.
    – rball
    Commented Feb 14, 2018 at 22:45
  • With the ddl_admin, you can truncate a table, with alter, they could drop it. If they could use SQL injection, that could be a plausible risk. Foreign keys should prevent this, but if you have select, insert, update, and delete web facing privileges to the database, you would have to worry about your data being seen or manipulated if an individual did somehow get access. A lot of times in security, it's more about the theory of what if and not so much logically what will probably happen. In enterprise IT, it's just best to mitigate all risk possible, hence the principle of least privileges.
    – Shaulinator
    Commented Feb 15, 2018 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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