Of course, using get_context and set_context is a great way to implement true multi-tenancy at DB level in Firebird. Multi-tenancy can be dangerous if implemented in middleware or SQLs because programmers make mistakes and can forget a where clause. What we want to do is to have the DB abstract that for us.

Create the following table:

CREATE TABLE MY_TABLE (

    ID            BIGINT NOT NULL,

    TENANT_ID     BIGINT NOT NULL,

    OTHER_COLUMN  VARCHAR(64)

);

And trigger

/* Trigger: MY_TABLE_BI0 */

CREATE OR ALTER TRIGGER MY_TABLE_BI0 FOR MY_TABLE

ACTIVE BEFORE INSERT POSITION 0

AS

begin

  NEW.TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from rdb$database);

end

^

SET TERM ; ^

We see that the trigger populates the TENANT_ID for us from a context variable that is valid for the whole session.

Now what we want is to have our view only return rows belonging to the logged-in tenant. I.e.:

create view VW_MY_TABLE as

select *

from MY_TABLE

where TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from rdb$database);

So all you need to do at the beginning of the session is to set the context:

select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',1) from rdb$database

and insert data

insert into MY_TABLE

(ID,OTHER_COLUMN)

values

(1,'Dome value')

Select * from VW_MY_TABLE will return our row

Change to a different tenant

select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',2) from rdb$database

Select * from VW_MY_TABLE will now return empty

Pretty cool… You just jave to watch your indexing