Preventing data modification depending on other data state

Question

Post reply

Preventing data modification depending on other data state

cowabunga

SSC Enthusiast

Points: 111
More actions
March 4, 2010 at 9:41 am

#221240

Hello,
I come from SQL Server 7 and I'm now working on 2008 as a nearly newbie.
In my database, I have data which should not be updated under certain conditions depending on values in other tables (as an example -but there is a lot of other situations-, if a contract is closed, it is no more possible to change some -but not all- depending data in other tables).
Sure I have implemented the controls in order to avoid prohibited data modification inside my program logic, in stored procedures or in VB .Net programs.
But the problem is that some users can also access data through Access or Excel and directly change fields content values, going around my program logic.
What is the best way to check and prevent updates when done by outside tools like these ?
Of course, I imagine triggers can do the job. But I'm afraid about the overhead due to triggers activation on each updated record when it is useless (ie, until the contract is closed).
Am I wrong ? Is there an other best way do achieve my goal ?
Thanks for your help.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

In my humble opinion Triggers seems to be the only viable option.

Since the changes involve more than one table a Check constraint will not work.

Since Users are accessing the Database from multiple sources, GUI's Excel, etc you would have to control and lock down each individual application. You have no control of this but you can prevent this by using triggers.

I feel your pain but I do not see an alternative option to triggers in this scenario. I have experienced what you are going through.

Have you tried writing some triggers and performing benchmarks?

Are you empowered to prevent the users from Accessing your Database from outside you VB Application? I would guess that politics will be a major factor.

What type of Security is being used for you application and the users that Access the Database via ad-hoc tools?

Another option might be the use of Windows Group Policies but allowing users to access your Database is not a good thing.

Politics will usually prevail and defy reasonable logic.

Regards,

Welsh "Dick" Corgi 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Yikes. Having users in the business that have permission to manually update data outside of the confines of the application is a massive problem.

The only real way to maintain integrity going forward is to start controlling this access and bring any functionality that is required by the business into the application so there are not users who are able to do this.

Otherwise you would have to add a horrendous amount of triggers and constraints to make sure all of these ad-hoc updates meet the business rules which will impact performance no matter how you implement it and it's impossible to think of every conceivable scenario. It also creates an auditing nightmare.

Sometimes it seems like it's best to stick to the status quo and just let things carry on as they are, but it's in the best interests of the business to tackle the underlying issue here.

LutzM SSC Guru Points: 107049 More actions · Answer 3

What is the best way to check and prevent updates when done by outside tools like these ?

If you want to prevent updates done other than via your stored procedure you need to lock down the tables.

I'd recommend to create a user role that will have execute right on the procedures you want to be used and only select permission on the base tables.

Therewith, users still can use ACCESS or EXCEL to query the table but they won't be able to change anything.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

cowabunga SSC Enthusiast Points: 111 More actions · Answer 4

Having users in the business that have permission to manually update data outside of the confines of the application is a massive problem

I do agree, of course ! But the managers don't want to prohibite this.

And, as a response to Imu92, the Access/Excel users *must* be able to update data until the conditions prevent them doing so.

So, it seems there are only two ways :

- doing nothing

- writing a lot of triggers leading to performance problems, as I was afraid of.

Thanks a lot for your rapid answers.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I suspect that the reason that the users are accessing your application via Access & Excel is because that they can't get what they need via your VB.NET Application?

To provide the User Community with all of that functionality would probably take a significant development effort?

Unfortunately this is a common problem and if you can't deliver then it becomes very political.

I agree with all of the previous post but unfortunately I have been in situations where I had users locking & blocking, performance issues, etc with SQL Server & Oracle Databases by using Access & Excel... :w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

cowabunga,

I know exactly what you are going through...

Regards,

Welsh "Dick" Corgi

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 7

Would it be possible to use views that will exclude the rows/columns they're not allowed to change and grant update permission to that view?

That still would prevent accessing your main data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

cowabunga SSC Enthusiast Points: 111 More actions · Answer 8

Would it be possible to use views that will exclude the rows/columns they're not allowed to change and grant update permission to that view?

Not so simple !

They are allowed to change the data which must be protected only later when the conditions are met.

LutzM SSC Guru Points: 107049 More actions · Answer 9

cowabunga (3/4/2010)
Would it be possible to use views that will exclude the rows/columns they're not allowed to change and grant update permission to that view?
Not so simple !
They are allowed to change the data which must be protected only later when the conditions are met.

I don't see a contradiction here:

Using your example "if a contract is closed, it is no more possible to change some depending data" would result in a view with a condition WHERE table.contract <>'closed'

But since we don't really know the scenario we're just guessing here...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

I completely understand what you are through and I feel for you

Your are fighting a loosing battle.

I try to win over the user base but that puts you at odds with the IT Community..

God luck!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mike 84768 Right there with Babe Points: 792 More actions · Answer 11

Can you split your data into two tables with a UNION view over both? One table, for 'open' items can be updated, the other for 'closed' items has an INSTEAD OF trigger to prevent updating. As a rule, I like to move rows that are 'closed' to an archive or history table since you will only be executing reads against the table..get them out of the more active table and help keep the rowcount down for searches preceeding an update.

cowabunga SSC Enthusiast Points: 111 More actions · Answer 12

We have also history tables, but closed contracts remain 'alive' and their data are used.

And I cannot split tables.

mike 84768 Right there with Babe Points: 792 More actions · Answer 13

Then, with multiple connection/update interfaces, you are stuck with triggers which evaluate every update to determine whether it should be allowed or not.

Hmm...maybe with a snapshot you could let them update the rows then, on an appropriate schedule, evaluate the changed rows against the snapshot to determine whether you should revert them or not.

There might be an option for doing the same sort of thing using the Change Data Capture data if it is enabled.