Chapter 13. Define Your Scope Hierarchy (STEP 6)

In your database, you are likely to have a hierarchy of scopes. What this means is that one scope is contained within another. For instance a project scope might be contained within the department scope of the department that owns the project. This hierarchy is used in 2 ways:

Determining which scopes may be promoted to which other scopes is done through the superior_scopes view. This identifies, for each scope, what directly superior scopes exist. It does not identify global scope, and does not require a recursive query to identify grandparent scopes etc. These are handled elsewhere.

13.1. Customizing The superior_scopesView

You will need to create a custom version of the superior_scopes view. This is done (as described here) by creating an over-riding custom view named my_superior_scopes. The provided Veil2 base version of this view is simply a placeholder.

You will define this view as the union of a number of selects. Each select will represent a single type of superior scope. The following example is from the demo. See STEP 6 in the file demo/veil2_demo--0.9.1 (beta).sql.

create or replace
view veil2.my_superior_scopes (
  scope_type_id, scope_id,
  superior_scope_type_id, superior_scope_id
) as
select 4, party_id,  -- Promote org to corp scope
       3, corp_id
  from demo.parties_tbl -- No join needed to scopes as party_id == scope_id
 where party_type_id = 2
union all
select 4, party_id,  -- Promotion of org to higher org
       4, org_id
  from demo.parties_tbl
 where party_type_id = 2
   and party_id != org_id  -- Cannot promote to self
union all
select 5, s.scope_id,   -- Project to corp promotions
       3, p.corp_id
  from demo.projects p
 inner join veil2.scope_links s
    on s.project_id = p.project_id
union all
select 5, s.scope_id,   -- Project to org promotions
       4, p.org_id
  from demo.projects p
 inner join veil2.scope_links s
    on s.project_id = p.project_id;
      

Note that if you have something like an organizational hierarchy in which you want someone that is assigned a role in an organizational context, to automatically have those rights in all subordinate organizational contexts, you will need to define scope promotions within that hierarchy. This is done by the second query in the above union.

Any time you redefine this view you should call veil2.init() to update the base definition and refresh the materialized views that depend on it.

13.2. Handle Refresh of Materialized Views

If your scope hierarchy changes in a way that could affect the privileges that might be assigned to accessors, you should refresh all materialized views and caches. Since determining what sort of updates might require this is somewhat complicated, we recommend that any change that would result in the veil2.superior_scopes view returning different data should result in a full refresh of all materialized views. For this you should call the trigger function veil2.refresh_scopes_matviews().

It is assumed that such changes will be rare but if this is not the case, and you encounter a performance penalty from having the veil2.accessor_privileges_cache cache table repeatedly cleared, you may be able to, instead of truncating that table, selectively delete records based on the session contexts that are affected by your changes. Our recommendation is not to tackle this until you know that you have a problem. By then you should have enough local Veil2 expertise that you can easily figure out what to do.