Subject Re: [firebird-support] Advice requested on design pattern
Author Alan J Davies
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
update rdb$procedures
set rdb$procedure_source = null
where ((rdb$system_flag = 0) or (rdb$system_flag is null));

Alan J Davies

On 20/05/2015 10:24, Mike Ro miker169@... [firebird-support] wrote:
> Thank you for this very useful response.
> On 20/05/2015 08:17, 'Louis van Alphen' louis@...
> [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.