mission impossible: How to protect a Sql Server (with VB.NET) from somebody who as administrator rig

[Pieter]

Hi,

I'm sorry for the probable cross-posting, but I need a solution that can be
either via Sql Server (preferable) or via .NET...

In one of our commpany's we're having a huge problem with a crazy situation:
A lot of people which are working for the company are also supplier to the
company. So they are manage their own delivery's, and their own payments...

They used to have applications in Access, but if they want to (and they do,
hehe) thay can change their own payments they earn. Some of them have even
administration rights, and full access to the Sql Server Database (it
personell etc).

So what I need is:
- make the application in VB.NET, without giving them the source offcourse
- a protection so that, even with administrator rights, they can't change
their own data. I was thinking about something like a checksum-algeorithm.

Does anybody has experience with this? how should I implement an
checksum-algorithm? What other options do I have?

Any help will be really appreciated!!

Thanks a lot in advance,


Pieter

 

[David Portas]

You can't protect your data against users with the SYSADMIN role. What
you should be able to do is deny admin level access to the users. You
didn't explain why they need that. If it's an issue that they need to
administer some things and not others then separate off the restricted
data into another server or instance to which they don't need admin
level access.

If it's just an issue of them historically having had an admin user
name and password, then change the user name or password and/or issue
new user names as appropriate.

 

[Pieter]

The problem is: the IT staff that must do the maintenance of the SQL Server
really needs the sysadmin...

 

[Cowboy \(Gregory A. Beamer\)]

Rather than attempt to change their rights for their data, consider putting
a mechanism into place that stores historical data so you have a way to
prove who change the data and when. This, in the long run, will have a
greater effect, as you can say "you change your payments from $1000 to $2000
on December 5, 2005 at 12:35:36.001 AM", which is against our rules. Rather
than locking them out, logging changes and even sending alerts to key staff
when certain fields are changed -- priceless.

This can certainly be done in SQL Server with triggers, but realize that the
sysadmin can change or delete triggers. If you put a VB.NET front end, you
have the power to do what you want, as long as they cannot circumvent the
front end you create. If they have table access, you are toast.

I cannot give you a firm direction without a better understanding of the
product, which is beyond the scope of an open forum. Now, if you want to
stop changes to certain fields, you can add a trigger that refuses updates
on that field. Realize they can disable it, however.

One more option: Have a second copy of the data that you do not want them to
change. You can then compare deltas and find cases where they are playing
games and then present them with the evidence.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

 

[Kevin Spencer]

SQL Server permissions are incredibly configurable. You don't have to make
people administrators to enable them to perform specific types of tasks. You
can create accounts that have these specific permissions and no other.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
A watched clock never boils.

 

[Jim Underwood]

Ok, here is a really ugly solution...

Encrypt and decrypt the data in your application. Store encrypted data in
the database, at least for the fields in question. This adds a lot of
overhead to your application, but as long as you are not dealing with large
amounts of data you may still be ok. Only those with access to the
application will be able to store the data in the proper encrypted format.

Note, in order for such an encryption to work you have to make it complex
enough so that "1000" will be encrypted differently for every record. i.e
make the primary key part of your encryption algorithm.

Now, you probably need to have access to this data for reporting, in which
case you can store the critical values in both encrypted and plain text
form. A simple procedure in the application will find all the records that
were tampered with. Also, when it comes time to pay out this money, make
sure it is the encrypted value that is being used.

 

[Pieter]

Thanks guys for the help and hints.

I guess I will indeed need to encrypt the values, or better: add a hash so
the values can be used in reports.

The only problem with reports is thatonce the report is exported, values can
be changed afterwurths... Some kind of solution may be to hash the total of
the report and print iton the report, and give the people that receive the
report a tool that recreates the hash to be able to verify it...

 

[Jim Underwood]

It looks like you have a few technical solutions to work with, but I want to
stress one point...

Very often the proper solution to a problem like this is not technology, but
policy. This sounds like one of those cases.

While you can make it difficult for anyone to manipulate the data, you
should not have to go to these extents. With a system of this type, that
controls payments, very few should have access that allows them to change
data. Suppliers should not be managing the database that pays them, and
anyone who changes the data in anyway should be terminated on the spot.
Companies often look for technical solutions for bad business practices.