Re: [firebird-support] Advice requested on design pattern
On 20/05/15 21:15, Alexandre Benson Smith ibl...@thorsoftware.com.br [firebird-support] wrote: In this way wouldn't the SP have the same problem that was mentioned by Louis, namely that only the index from the first table in the query would be used? I did not read it in full details... But the case of select on views be able to use the index on the first table I believe that's because of using of outer joins, with inner joins that would not be the case... I am very sorry for my slow response, but thank you for explaining this. I don't know anything about how Firebird treats indexes in selectable SP, so I have some learning to do in this area.
Re: [firebird-support] Advice requested on design pattern
Once again thank you. I am very sorry it has taken me so long to reply. On 20/05/15 21:19, Andrea Raimondi andrea.raimo...@gmail.com [firebird-support] wrote: 1) Re the separate database: nobody said you shouild do it from your own. I'd do it in the client, once the original data has been inserted. If I understand correctly it will become basically a logging database. That could be fine as I have a data access layer anyway but I would need to make sure that a audit log is only written if a transaction is successfully committed and not rolled back. 3) How many blobs *per user* are you going to have? Maximum 1000, typically 50 - 100. 6) The only real remaining problem is encryptuion - if you want that for some of your data. Provided that I would strongly discouragre that especially on Firebird because of the eaae of peeking inside the DB which equates roughly to no ecryption, I would go for something slightly off the ordinary: what you can do is encrypt your data in the app and then apply it to your tables. There are several strategies you could employ, but I think you first need to determine whether that's what you really want. Up until now I wasn't really worried about encrypting the data, however the more that I look into this the more it seems like an almost necessity. If I understand correctly the downside of encrypting data in the app is obviously that Firebird can't index the contents. I have been looking at Firebird 3 in this regard and it seems to be possible, at least to include reasonably obfuscated data using a closed source (my own or third party) plug-in. Thank you again for your advice!
RE: [firebird-support] Advice requested on design pattern
Q1: We have an application with around 400 table and we use this structure for all tables. I.e. apart from the payload columns, all tables have these housekeeping fields. However our app framework populates these tables as the app logs in with it’s own single user. Due to a complex security and permissions model, the app has it’s own user model that the DB does not know about. The advantage of this is that we have a base class in the app domain (C#) that implements these properties. All DataObjects then derive from this. Our app is OLTP and the advantage of having the app populate the date fields is that for testing, we can centrally alter the timebase the app runs at without having to mess with server time and the consequences of that. Yes FB is a RDBMS and not OO DB. In a previous project I used ‘inheritance’ where I put common fields in a ‘base’ table and other fields in a ‘derived’ table and then a view on top. The 2 tables are then linked via a PK with same value. The problem I had was when I queried the view, it only used indexes of one table, depending on wich one was first in the select in the view. It ignored any indexes from the other table. So this fell flat. This was in FB 2.1. I would not advise doing this. Q2: Our approach is to have as few triggers / SPs in the DB. All updates and exchanges with the DB through our generic data access layer. The only place where we use triggers is to manage a RowVersion column after insert and update and reject concurrent row updates. I.e. where the users or system updates stale data. In our experience, SPs and triggers become messy very quickly and is hard to debug. Been bitten once too many times. You also simply don’t grant access to casual users to the tables. The app uses a user with all grants in place. Our convention is to use a table name such as CUSTOMER_. Then we on top of that we put a view called CUSTOMER. The view also brings in some columns from ‘lookup’ table such as CURRENCY, etc. Normal users using reporting tools etc only get granted select access on the view and not the underlying table. Q3: You can clear the RDB$PROCEDURE_SOURCE field in the RDB$PROCEDURE system table containing the SP and trigger definition. But take care to keep the scripts somewhere for backup. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 19 May 2015 11:35 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Advice requested on design pattern I am creating a database that consists of 30 - 40 tables. Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example: * UID (from generator) * Name * Description * DateTime Created (audit field) * DateTime Modified (audit field) * User Created (audit field) * User Modified (audit field) Most tables will contain a few thousands of records, some of them may be largish blobs such as photos but mostly it will be plain text and HTML. Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP. Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table? The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation? I appreciate that the answer may be Depends ... but I would appreciate general guidance or opinions where it isn't possible to provide a definite answer. Many thanks for the help!! [Non-text portions of this message have been removed]
Re: [firebird-support] Advice requested on design pattern
Another way of doing what you want would be to use SPs instead of Views. Then if you select * from SP and hide it as below, no-one can see what you are doing with the data. This works well and I drop this into every new database I work on. To repeat the warning though - make sure you have a copy of all your procedures before running this, because they will be blank. create or alter procedure z_sp_hide as begin update rdb$procedures set rdb$procedure_source = null where ((rdb$system_flag = 0) or (rdb$system_flag is null)); end Alan J Davies Aldis On 20/05/2015 10:24, Mike Ro miker...@gmail.com [firebird-support] wrote: Thank you for this very useful response. On 20/05/2015 08:17, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] wrote: Yes FB is a RDBMS and not OO DB. In a previous project I used ‘inheritance’ where I put common fields in a ‘base’ table and other fields in a ‘derived’ table and then a view on top. The 2 tables are then linked via a PK with same value. The problem I had was when I queried the view, it only used indexes of one table, depending on wich one was first in the select in the view. It ignored any indexes from the other table. So this fell flat. This was in FB 2.1. I would not advise doing this. Ah, this is very valuable. I will take your advice. You also simply don’t grant access to casual users to the tables. The app uses a user with all grants in place. Our convention is to use a table name such as CUSTOMER_. Then we on top of that we put a view called CUSTOMER. The view also brings in some columns from ‘lookup’ table such as CURRENCY, etc. Normal users using reporting tools etc only get granted select access on the view and not the underlying table. This makes sense, and will probably do something similar. I am thinking about having a table that shadows Firebird user accounts. When the user logs in from the app they would actually access the database with a less restricted account but the business logic would enforce restrictions (i.e. no user access to the audit fields). If the user logs in from a reporting tool they will use their 'real' (Firebird) user account which will have Firebird access control limitations. Q3: You can clear the RDB$PROCEDURE_SOURCE field in the RDB$PROCEDURE system table containing the SP and trigger definition. But take care to keep the scripts somewhere for backup. Thank you for this tip, I have never tried this so will do some experimentation.
Re: [firebird-support] Advice requested on design pattern
Hi! Here are my opinions; please feel free to point out any inaccuracies. On Tue, May 19, 2015 at 11:35 PM, Mike Ro miker...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: I am creating a database that consists of 30 - 40 tables. Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example: - UID (from generator) - Name - Description - DateTime Created (audit field) - DateTime Modified (audit field) - User Created (audit field) - User Modified (audit field) Most tables will contain a few thousands of records, some of them may be largish blobs such as photos but mostly it will be plain text and HTML. Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP. Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table? I think it really depends on what you are trying to do and what are your performance expectations. You might, for instance, only need the description or the date created occasionally (for example, only when checking the details of a photo) in which case having a base table and correlated ones could be beneficial if we are talking about thousands or millions of users. The downside of this approach is that you kind of lose referential integrity and may end up having bugs which insert the photo but not its details. I do realise it is fairly uncommon, but it may still happen. Would that be ok to run the risk of this happening? On the other hand, having all fields in all tables means you do not have to do a join, which means you can have a richer and more complete overview of something directly on your main screen without affecting the performance too much, especially if you have some form of caching enabled. The downside of this is that you really need caching especially if we are talking about blobs whose size is, so to speak, accomplished. Also, speaking about blobs: I do believe that - really - you should avoid putting them inside the tables as much as possible. What you could do - at least for pictures... I am not entirely sure for other media - is to have a thumbnail directly stored in the DB and then the full-size image stored on the HD. This is slightly slower in retrieval but it is mitigated by the fact that networks still lag relatively a lot, hence any delay in loading and transmission is not really noticeable especially if you leave enough throttle on the server (which you have to do anyway because you are transmitting blobs). This will also pose a problem with backup, but I do not believe that to be a major problem quite frankly. Another alternative (and one I would highly endorse in this day and age) would be to be smart and store the blobs into the users' dropbox accounts and then use an URL reference to pick it up, having such reference stored in the DB). The advantage of this solution would be to split the band requirements on your original server machine and also avoid having backup problems. As an additional upside, the user can be comforted in knowing that he still has full access to his own data which, if you ask me, surely is going to be a powerful advantage in the coming years as data ownership and privacy issues slowly gain the front page of users' concerns. The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. No, Just no. Each table its own trigger. Do not do that. Trust me, that's a *REALLY* bad idea. If anything, use numeric constants that cannot be hijacked as easily. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? Yes, you screw up the security. Doing that means you would need to use Dynamic SQL and that's 99% evil. Do not do that unless you are really obliged to for some reason. It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. In my opinion, the only way to do that is to have them backed up on a different - inaccessible - database. This would also open up a business opportunity if your
Re: [firebird-support] Advice requested on design pattern
Thank you for this very useful response. On 20/05/2015 08:17, 'Louis van Alphen' lo...@nucleo.co.za [firebird-support] wrote: Yes FB is a RDBMS and not OO DB. In a previous project I used ‘inheritance’ where I put common fields in a ‘base’ table and other fields in a ‘derived’ table and then a view on top. The 2 tables are then linked via a PK with same value. The problem I had was when I queried the view, it only used indexes of one table, depending on wich one was first in the select in the view. It ignored any indexes from the other table. So this fell flat. This was in FB 2.1. I would not advise doing this. Ah, this is very valuable. I will take your advice. You also simply don’t grant access to casual users to the tables. The app uses a user with all grants in place. Our convention is to use a table name such as CUSTOMER_. Then we on top of that we put a view called CUSTOMER. The view also brings in some columns from ‘lookup’ table such as CURRENCY, etc. Normal users using reporting tools etc only get granted select access on the view and not the underlying table. This makes sense, and will probably do something similar. I am thinking about having a table that shadows Firebird user accounts. When the user logs in from the app they would actually access the database with a less restricted account but the business logic would enforce restrictions (i.e. no user access to the audit fields). If the user logs in from a reporting tool they will use their 'real' (Firebird) user account which will have Firebird access control limitations. Q3: You can clear the RDB$PROCEDURE_SOURCE field in the RDB$PROCEDURE system table containing the SP and trigger definition. But take care to keep the scripts somewhere for backup. Thank you for this tip, I have never tried this so will do some experimentation.
Re: [firebird-support] Advice requested on design pattern
Thank you for your response. On 20/05/15 11:25, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] wrote: Another way of doing what you want would be to use SPs instead of Views. Then if you select * from SP and hide it as below, no-one can see what you are doing with the data. In this way wouldn't the SP have the same problem that was mentioned by Louis, namely that only the index from the first table in the query would be used? Sorry if I misunderstood or my knowledge of SP is lacking.
Re: [firebird-support] Advice requested on design pattern
Thank you for your reply! On 20/05/15 11:12, Andrea Raimondi andrea.raimo...@gmail.com [firebird-support] wrote: On the other hand, having all fields in all tables means you do not have to do a join, which means you can have a richer and more complete overview of something directly on your main screen without affecting the performance too much, especially if you have some form of caching enabled. The downside of this is that you really need caching especially if we are talking about blobs whose size is, so to speak, accomplished. I am not anticipating there to be many blobs and they are only in a few of the tables. It's difficult to explain the exact use but imagine a document that is mostly text and a few illustrations like an academic paper or similar. There is one exception though and that will consist almost completely of blobs (actually PDF files). Also, speaking about blobs: I do believe that - really - you should avoid putting them inside the tables as much as possible. Interesting you should say that, but I also heard of users with terrabytes of blobs in tables? I am expecting 500 Mb - 1Gb at most. Another alternative (and one I would highly endorse in this day and age) would be to be smart and store the blobs into the users' dropbox accounts and then use an URL reference to pick it up, having such reference stored in the DB). Yes, this would be an option but I would also to like the option to be able to read the documents 'offline' using the embedded server on a laptop for example. There will only be a handful of users (50 or so - it's an internal project) so the scale is relatively small. Sorry I should have explained that in my original email. The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. No, Just no. Each table its own trigger. Do not do that. Trust me, that's a *REALLY* bad idea. If anything, use numeric constants that cannot be hijacked as easily. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? Yes, you screw up the security. Doing that means you would need to use Dynamic SQL and that's 99% evil. Do not do that unless you are really obliged to for some reason. Thank you, point taken!! It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. In my opinion, the only way to do that is to have them backed up on a different - inaccessible - database. This would also open up a business opportunity if your product is commercial, i.e, be able to retrieve the last audit details if you can't guarantee that the db has not been fiddled with (such as with an intrusion of some sort or suspicious internal activity). This is an interesting idea. I thought about having the 'audit' table in a different database and inserting a table_id, record_id, action, date and time from the trigger. However reflecting on your earlier comments this probably isn't a good idea because I think it would require an EXECUTE STATEMENT :(. Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation? UDFs are not an obfuscation solution. Consider the following scenario: you use an open source UDF for encryption. Now, a malicious user turns off the FB service and substitute your legittimate version of the UDF with one which also contains a keyboard hook or worse, a global hook registering all calls made on a Windows server. Now you are in serious trouble. I am hoping my users are that that malicious, but it is a good point. I wouldn't want it to end up as a back door in case the database was ever deployed somewhere that I haven't really considered. Does it help? YES!! Thank you!
Re: [firebird-support] Advice requested on design pattern
You welcone :) A few points: 1) Re the separate database: nobody said you shouild do it from your own. I'd do it in the client, once the original data has been inserted. 2) Security: rule 1 is *TRUST NO-ONE*. 3) How many blobs *per user* are you going to have? 4) Once you have downloaded the blob, you can keep it in a local cache (using some sort of memory table with dates, you should be able to determine whether you need the blob or not from the remote DB. This would also help lighten the load on the server). This way you have the best of both worlds. 5) Re blob in tables: the fact you can do something doesn't mean you should. Blobs were never really meant - in my opinion - to pollute a database. Someone thought it was a jolly good idea (God knows why...) and the others followed suit. Be smart. 6) The only real remaining problem is encryptuion - if you want that for some of your data. Provided that I would strongly discouragre that especially on Firebird because of the eaae of peeking inside the DB which equates roughly to no ecryption, I would go for something slightly off the ordinary: what you can do is encrypt your data in the app and then apply it to your tables. There are several strategies you could employ, but I think you first need to determine whether that's what you really want. Hopefully, this will help you a bit more :) A On Wed, May 20, 2015 at 8:53 PM, Mike Ro miker...@gmail.com [firebird-support] firebird-support@yahoogroups.com wrote: Thank you for your reply! On 20/05/15 11:12, Andrea Raimondi andrea.raimo...@gmail.com [firebird-support] wrote: On the other hand, having all fields in all tables means you do not have to do a join, which means you can have a richer and more complete overview of something directly on your main screen without affecting the performance too much, especially if you have some form of caching enabled. The downside of this is that you really need caching especially if we are talking about blobs whose size is, so to speak, accomplished. I am not anticipating there to be many blobs and they are only in a few of the tables. It's difficult to explain the exact use but imagine a document that is mostly text and a few illustrations like an academic paper or similar. There is one exception though and that will consist almost completely of blobs (actually PDF files). Also, speaking about blobs: I do believe that - really - you should avoid putting them inside the tables as much as possible. Interesting you should say that, but I also heard of users with terrabytes of blobs in tables? I am expecting 500 Mb - 1Gb at most. Another alternative (and one I would highly endorse in this day and age) would be to be smart and store the blobs into the users' dropbox accounts and then use an URL reference to pick it up, having such reference stored in the DB). Yes, this would be an option but I would also to like the option to be able to read the documents 'offline' using the embedded server on a laptop for example. There will only be a handful of users (50 or so - it's an internal project) so the scale is relatively small. Sorry I should have explained that in my original email. The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. No, Just no. Each table its own trigger. Do not do that. Trust me, that's a *REALLY* bad idea. If anything, use numeric constants that cannot be hijacked as easily. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? Yes, you screw up the security. Doing that means you would need to use Dynamic SQL and that's 99% evil. Do not do that unless you are really obliged to for some reason. Thank you, point taken!! It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. In my opinion, the only way to do that is to have them backed up on a different - inaccessible - database. This would also open up a business opportunity if your product is commercial, i.e, be able to retrieve the last audit details if you can't guarantee that the db has not been fiddled with (such as with an intrusion of some sort or suspicious internal activity). This is an interesting idea. I thought about having the 'audit' table in a different database and inserting a
Re: [firebird-support] Advice requested on design pattern
On 20/5/2015 16:03, Mike Ro miker...@gmail.com [firebird-support] wrote: Thank you for your response. On 20/05/15 11:25, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] wrote: Another way of doing what you want would be to use SPs instead of Views. Then if you select * from SP and hide it as below, no-one can see what you are doing with the data. In this way wouldn't the SP have the same problem that was mentioned by Louis, namely that only the index from the first table in the query would be used? Sorry if I misunderstood or my knowledge of SP is lacking. I did not read it in full details... But the case of select on views be able to use the index on the first table I believe that's because of using of outer joins, with inner joins that would not be the case... see you ! --- Este email está limpo de vírus e malwares porque a proteção do avast! Antivírus está ativa. http://www.avast.com
[firebird-support] Advice requested on design pattern
I am creating a database that consists of 30 - 40 tables. Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example: * UID (from generator) * Name * Description * DateTime Created (audit field) * DateTime Modified (audit field) * User Created (audit field) * User Modified (audit field) Most tables will contain a few thousands of records, some of them may be largish blobs such as photos but mostly it will be plain text and HTML. Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP. Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table? The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation? I appreciate that the answer may be Depends ... but I would appreciate general guidance or opinions where it isn't possible to provide a definite answer. Many thanks for the help!!
