> Thanks for feedback, I see the first thing need to do is fix the sysdba user access, then maybe looking at other methods..

Different things have been discussed. Just another view on your question(s):

1) Get rid of SYSDBA being the database owner. Send me an email if you
want to get a hand on the "FB OwnerMigrator" tool, which helps you to
migrate to another user without pumping data etc.

2) Get rid of using SYSDBA for your client connections. Either use
separated Firebird users or if you are running Windows, you might
investigate Firebird's Windows Authentication capabilities

3) Install an auditing/monitoring approach. Depending on your Firebird
version and your requirements, there are:

a) Trigger-based (since Firebird 1.0): DML triggers for logging data
changes.
b) Trigger-based (since Firebird 2.1): Database triggers e.g. ON CONNECT
/ ON DISCONNECT for logging/disallow attachment scenarios.
c) Trigger-based (with Firebird 3.0): DDL triggers for logging changes
on database objects
d) Monitoring tables (since Firebird 2.1): Get an actual snapshot of
current database activities
e) Trace API (since Firebird 2.5): Continuous stream of executed events.
f) Proxy application like FBScanner for intercepting the Firebird
network protocol

ad a): Tool-support: IB LogManager
ad b) and c): Can be easily by-passed by the various "no_db_triggers"
switch in isql, gbak etc ...
ad d): Tool support: Sinatica Monitor
ad e): Tool support: FB TraceManager

In general, with a trigger solution you can pro-actively react on stuff
when it happens, e.g. disallow certain actions by throwing an exception
in the PSQL code. For d/e/f, you see what already happened. For a
continuous monitoring approach with the Trace API, you can use collected
data for later analysis to detect fraud usage, e.g. abnormal
failed/unauthorized access or database usage outside normal office hours
or database users shared among different client machines etc.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

> --- In firebird-support@yahoogroups.com, Helen Borrie<helebor@...> wrote:
>>
>> 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
>>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>