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
