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 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!
>
>
>
>
>
>
--
Mr. Andrea Raimondi
Senior Software Analyst&Developer
