How can I enforce security or permissions on a bound dataset?

Question

Using a strongly typed dataset and its related table Adapters, normally when I want the changes to pass back, just pass it the table and let it do all the work.

What are some easy ways to enforce security roles on the application user as to which fields they can insert/update/delete when the database is using an application ID instead of user level security?

Do I have to go row by row and check each row against what this particular user is allowed to do (by checking the current version of every field against it's proposed version against their role's permissions?

I believe the first level of security would be locking down the columns on the UI a particular user is not supposed to modify, but what about at the data level? Is there a nice way to do this?

Is this easier in linq-to-sql?

Answer 1

First, are you asking about actual security or just control/saftey/fool-proofing (ie stopping users from doing something dumb)?

If you are trying to enforce security try to answer some questions like who is allowed to connect to the database? where is the actual enforcement (eg, application vs connection to the database)?

For example if your application is where enforcement is placed then what if your application is compromised? Can it then connect to the database and do whatever?

For role separation I would suggest different application that are running/assigned different roles. Each role has to authenticate against the database in some way and only have access to necessary data.

In summary, ask who is doing what to whom and what if they were compromised.