Search code examples
delphiviewfirebirduser-rolesfirebird2.5

Using view firebird with filter by role

I have table tbl_projects with a field "role". I want to use this field as parameter to filter data that each user have access. Example: I am employee from HR(my role) and I can't see projects from IT(other role).

My application use OSAuth. How can I build a view in firebird that filter the data based in all roles?

Example: This view filter only 'HR' role. I need filter for all roles.

CREATE OR ALTER VIEW HR_PROJECTS_VIEW(
    (_other_fields_)
    "ROLE")
AS
select
    P.(_other_fields_)
    P.ROLE
from 
    TBL_PROJECTS P
where 
    P.ROLE = 'HR';
Solution

  • Use the CURRENT_ROLE context variable:

    CREATE OR ALTER VIEW HR_PROJECTS_VIEW(
...
where 
    P.ROLE = CURRENT_ROLE;