At 09:37 AM 16/01/2012, richwiz11 wrote:

>Hi, I need a way of logging or protecting data from being deleted by a user.
>
>I work for a POS (point of sale) company, each user "shop" would have a local firebird db running. We are entering into a new market where regulation states we have to protect our system from tax evasion (where possible)

So having all of your users with SYSDBA privileges bombs you out totally.

>We have figured out to how protect sales data from being altered.

While every Joe Blow is SYSDBA? I don't think so.

>The problem I am left with is how to protect a row from being deleted. (firstly, have come to the conclusion that it impossible to fully protect data

That's true.

>, however the idea here is just make it a bit harder and scare people off)

Oh yeah...

>The two method I have thought of is
>a) Using roles and remove the delete privilege

That's the one.

>(we currently just using SYSDBA, which is probably a bit bad anyway)

Try "totally insane".

>or
>b) Add some kind of trigger logging to keep this deleted data and copy to another db.

And let all these SYSDBAs play with it to their hearts' content.

>Just wanted to know if anyone else had any other ideas or been in a similar situation.

Just about everyone has to deal with this, from little shops to defence installations. Fix what's broken now and use roles. Give every user a login account and enforce login, i.e., remove "automatic login" that you may have been trying to avoid by encoding SYSDBA login in your applications. Restrict SYSDBA access to the one human in each place who has responsibility for securing data.

Address unauthorised access to databases AND backup files as an issue of extreme importance. Be aware of the exposure from keeping backups around - get them off the local servers and onto portable media that can be locked up, preferably off site and out of reach. It is extremely easy to steal databases and backups from an unprotected LAN.

>PS at the moment using FB 2.0, however planning on upgrading our clients to FB 2.5. I have been looking at the trace logging in FB2.5 but don't see that usefull for this problem

True. Especially not useful if all your users are SYSDBA. In any case, a trace can only tell you what happened, not what's going to happen. But SQL roles and user access control have been around Firebird since long before Firebird was born.

./heLen