Re: [firebird-support] Re: Protecting data from end users
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 Borriehelebor@... 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
[firebird-support] Is Firebird working On Android or Iphone?
Is firebird embed working On Android or Iphone?
Re: [firebird-support] Re: How can I get a list of firebird db users?
Trond wrote: If you use Firebird version = 2.1 you can use something like this: SELECT ma.MON$USER, ma.MON$REMOTE_ADDRESS,ma.MON$TIMESTAMP, ma.MON$REMOTE_PROCESS, ma.mon$attachment_id, ma.mon$attachment_name, ma.mon$role, ma.mon$state, ma.mon$stat_id, ma.mon$server_pid,ma.mon$remote_pid FROM MON$ATTACHMENTS MA WHERE ma.MON$ATTACHMENT_ID CURRENT_CONNECTION Those are only connected users. He wants to get a list of all user accounts on the server. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Protecting data from end users
On Sun, 15 Jan 2012 20:37:37 -, richwiz11 richard.cro...@wizbang.co.nz 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) Next to the suggestions given by others, you could also considering adding a hash or (better yet) a cryptographic message digest of all other records fields to every record (the hash or message digest would need to be calculated by your application, not by the database!). This can be used to identify tampered records. De keep in mind that a determined person can - with some trouble - still produce a valid hash or message digest if he has access to the application. Mark
Re: [firebird-support] Re: Time Field: Trouble updateing in FB 2.1 from Delphi 2007
Hi Homer, nice to hear from you again, I have not try it now but I'm pretty sure I have done this in the pass. It depends on the way IBDac deals with variants and timestamp because you are using an implicit casting. In that case you should use encodedatetime or encodetime, you can find it in the help. Regards, Al De: homerjones1941 ho...@agencybusys.com Para: firebird-support@yahoogroups.com Enviado: Domingo, enero 15, 2012 9:10 P.M. Asunto: [firebird-support] Re: Time Field: Trouble updateing in FB 2.1 from Delphi 2007 --- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: At 05:19 PM 16/01/2012, homerjones1941 wrote: I've searched past posts, but haven't been able to find solution. I have a Time FieldType in a FB 2.1 DB and have been unable to insert a time value into that field. I've tried the following (Note: I've resulted to hard coding the values to eliminate possible formatting issues caused by me): FieldByName('AlarmTime').Value := '4:30:00 PM'; FieldByName('AlarmTime').Value := '11:30:00 PM'; Not a valid TIME literal. The format uses the 24-hr clock and takes the form hh:nn:ss. or hh:nn:ss.nnn If you don't care about sub-seconds, use zeroes there. Error message is '0.0 is not a valid Timestamp.' If you are absolutely sure that column is TIME and not TIMESTAMP then I would guess you are using the (broken) old BDE. The last-ever was v.5.2 and the Dialect 3 date/time support still didn't work properly. If I omit the PM the error message is similar. FieldByName('AlarmTime').Value := '4:30:00'; It's a gamble assigning any date or time values to the Value property of a VCL parameter but in this case it's obviously not working because your interface (whatever it is) doesn't recognise that as time literal. If you want to persevere, I suggest you try one of the following: FieldByName('AlarmTime').Value := QuotedStr ('04:30:00'); FieldByName('AlarmTime').Value := ''04:30:00''; // doubled apostrophes NOT double quotes Error Message is '693594. -1073258496 is not a valid Timestamp.' And that's not a valid Firebird message, either. I've tried using TDateTime values with similar results. Using AsDateTime??? ./heLen Wow. Thanks for the quick response. I tried the QuotedStr solution but got yet another error: 'Could not convert variant of type (String) into type (Date).' I try to stay as far away from BDE as possible. I'm using IBDac components (the latest Devart stuff). I haven't tried to create an INSERT INTO query, but that's next. Perhaps DSQL will handle the time field properly. [Non-text portions of this message have been removed]
[firebird-support] Re: fb server crash
Nick Upson On 14 January 2012 14:39, Nick Upson n...@telensa.com wrote: Hi, using 2.1.4 (epel) on centos 5.5, We had a firebird crash with this message: Fatal lock manager error: invalid lock id (0), errno: 4 The system was under heavy load and it happened just after nbackup level 2 started (which may be related) We need to work out how to avoid it happening again but currently don't know why it happened. any pointers or suggestions gratefully received. Nick Upson anyone else able to comment, just some ideas about the cause would be helpful? [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Fwd: How to avoid deadlock ?
I don't get the point, sorry. If you perform SELECT in the same transaction as INSERT, then neither SELECT WITH LOCK nor UPDATE should wait. If the transactions are different, then both should wait. In this latter case, SELECT WITH LOCK will not wait only if you have already updated the record in the same (second) transaction, but again, the same applies to the regular UPDATE. It's ok thanks it's was just my understanding of the With lock was not too good. i just want to say that the select with lock work ONLY if their is record returned by the select :) but it's evident :) [Non-text portions of this message have been removed]
[firebird-support] Re: fb server crash
16.01.2012 18:42, Nick Upson wrote: I've just found http://tracker.firebirdsql.org/browse/CORE-2630 which leads to http://tracker.firebirdsql.org/browse/CORE-2993 which says its fixed in 2.1.4 They're unrelated, I believe. And once again, I suggest you to avoid using nbackup. Dmitry ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] selecting concatenated records
hello!! I have this situation, and don't know how to solve it... My table: ID ORIGINAL_ID 1NULL 21 32 4NULL 51 So, the ID 1 has two children (2 and 5) and 2 has one children (3). Of course, ID 3 could have N children and so on... What I need is to select all the descendants of a given record. For instance, I want to select all the records related to ID 1, I would have: ID ORIGINAL_ID 1NULL 21 32 51 It sounds that I should use recursion, but I can't figure out how... Thank you very much Sergio. [Non-text portions of this message have been removed]
[firebird-support] Re: selecting concatenated records
If you are using Firebird 2.1 or later, then the easiest way is to use a recursive common table expression. I hope I got it right, but I think you get the idea: with recursive r(id, original_id, hierarchy) as ( select id , original_id , 1 as hierarchy from region where id = 1 union all select r2.id , r2.original_id , r.hierarchy + 1 from region r2, r where r.id = r2.original_id ) select * from r; THANKS Thomas !!! Yes, I use FB 2.5 May I ask you a very big favor? I'd like to undestand how recursive selects are constructed. Could you explaing me a bit the select you made? Or probably there's somewhere in the net a paper about recursion in FB?? I'd really like to andestand this!! It's a very powerfull tool to use!!! Thank you very much again for your help. Very apreciated!! -s
[firebird-support] Re: Is Firebird working On Android or Iphone?
Or is there any plan for that? --- In firebird-support@yahoogroups.com, Ahmet Nuri D ahmetdenize@... wrote: Is firebird embed working On Android or Iphone?