Thank you for this very useful response.
On 20/05/2015 08:17, 'Louis van Alphen' [email protected]
[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.
