- Chapter 12. Link Your Scopes and Security Contexts (STEP 5)
- 12.1. Create Foreign Key Links
- 12.2. Create Insert Triggers
- 12.3. Create Update Triggers
- 12.4. Copy Existing Scopes Into The Links Table
- 12.5. Update the all_accessor_roles View
- 12.6. On Caches
|Chapter 11. Link Your Users To
||Home||Chapter 13. Define Your Scope Hierarchy (STEP 6)|
Veil2 scopes table is an
important link between
Veil2 and the database
that it is protecting. It provides the semantic link between
scopes as understood by the security
model, and the security
contexts provided or implied by the data model: it maps
logical scopes in the protected database, to
Veil2's physical scopes, keyed by
Look for STEP 5 in the file
You will start by creating a table that inherits from
veil2.scopes. To this
you will add columns that reference your tables.
For instance if you have a project context based on a user's
membership of projects, and your
table has an integer
project_id field as its
primary key, you would do the following:
create table veil2.scope_links ( party_id integer, project_id integer ) inherits (veil2.scopes); -- Set PK and FKs to match those in the parent scopes table alter table veil2.scope_links add constraint scope_link__pk primary key(scope_type_id, scope_id); alter table veil2.scope_links add constraint scope_link__type_fk foreign key(scope_type_id) references veil2.scope_types; alter table veil2.scope_links add constraint scope_link__party_fk foreign key (party_id) references demo.parties_tbl(party_id) on delete cascade; alter table veil2.scope_links add constraint scope_link__project_fk foreign key (project_id) references demo.projects(project_id) on on delete cascade;
Using the cascade options ensures that deletions of record in
projects table are propagated to
scopes. This saves us from having to
implement our own triggers for deletions.
It is wise to also ensure that project_id fields are only used with the appropriate scope type. A check constraint like the following should be used:
alter table veil2.scope_links add constraint scope_link__check_fk_type check (case when scope_type_id in (3, 4) then party_id is not null when scope_type_id = 5 then project_id is not null else true end);
Where, we assume, a
5 means project scope,
means org scope and
3 means corp scope.
If the linked scope in your protected database uses a simple
integer primary key, this can safely be used as the
scope_id in the links table as the primary
key of a scope is the combination of
If your source table's primary key is of some other form you
could simply allocate a
scope_id for the
link table from a sequence, however each call to i_have_priv_in_scope()
will now need to map to the
Veil2 scope key
through our links table. This is likely to badly affect
In such a case, we recommend refactoring your source table to
include a new unique integer key which can be used in the link
table as both the foreign key to the source scope, and as part
of the primary key to
We need to ensure that new scopes (in the demo these are projects, orgs and corps) created in the underlying tables are automatically propagated to the scopes tables. Triggers on insert to those tables should be created do do this.
There should be no need for update or delete triggers as we have defined our foreign key constraint to cascade updates and deletes.
Next, we want to ensure that the keys used by scopes do not change, or that if they do, we handle it.
Ideally our on-update trigger function will ensure that the scope's key fields do not change and cause an error if an attempt is made to do so.
If, for some reason, key changes must be allowed, then we must propagate such changes into our scope_links table, and we must clear all caches and refresh all materialized views that may be affected.
Now we need to copy our existing scope records into our new
projects. Something like this (for projects):
insert into veil2.scope_links (scope_type_id, scope_id, project_id) select 5, project_id, project_id from demo.projects;
If you have role assignments that are not managed solely through
veil2.accessor_roles table, you will need
to modify the
view. For example, in the demo, roles are assigned in project
context using the
To enable the creation of custom security contexts,
Veil2 determines an accessor's roles and the
contexts in which they apply from the
veil2.all_accessor_roles view, which you can
modify by providing your own
In the demo we make this view return results from both the
veil2.accessor_roles and the
For performance reasons
Veil2 caches a lot of scope-related privilege
data. Some steps in your
implementation will require the explicit clearing or refreshing
of some or all of those caches.
Changes to scopes do not require the refresh of materialized views or the clearing of caches. The rationale for this is as follows:
In the event of a new scope being created, there can exist no records relating to that scope (assuming that you cannot create descendent records of your scopes before creating the scope itself - ie we assume proper referential integrity is being maintained in the source database), particularly records relating to role assignments, so there can be no new privileges or roles assigned to any users and therefore no need to update the caches.
In the event of scope deletion, there will be no records remaining in that scope to which access must be controlled, so temporarily retaining redundant privileges for dead scopes can have no security implications.
Updates to the scope keys are the only situation where we may need to clear caches and refresh materialized views, and we strongly recommend that triggers are put in place to prevent such updates.
All changes to role assignments must result in
entries being cleared for the accessor in question.
This should be done by adding triggers to the tables
underlying your version of the
all_accessor_roles view. These triggers