|Chapter 12. Link Your Scopes and Security Contexts (STEP 5)
|Chapter 14. Create Initial Privileges (STEP 7)
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:
- for scope promotions;
- for determining access rights to data within inferior scopes.
Determining which scopes may be promoted to which other scopes is
done through the
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.
You will need to create a custom version of the
view. This is done (as described here) by creating an over-riding
custom view named
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
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
update the base definition and refresh the materialized views
that depend on it.
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
view returning different data should result in a full refresh of
all materialized views. For this you should call the trigger
It is assumed that such changes will be rare but if this is not
the case, and you encounter a performance penalty from having
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.