The 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
scope_type_id and scope_id.
Look for STEP 5 in the file
veil2_demo--<version>.sql.
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 project
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
the 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 scope_type_id of
5 means project scope, 4
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
scope_type_id and
scope_id.
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
performance.
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 Veil2's scope.
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
scope_links table.
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
the veil2.accessor_roles table, you will need
to modify the all_accessor_roles
view. For example, in the demo, roles are assigned in project
context using the demo.project_assignments
table.
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
veil2.my_all_accessor_roles.
In the demo we make this view return results from both the
veil2.accessor_roles and the
demo.project_assignments tables.
For performance reasons Veil2 caches a lot of scope-related privilege
data. Some steps in your Veil2 VPD
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 accessor_privileges_cache
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
should call veil2.clear_accessor_privs_cache_entry()
or veil2.clear_accessor_privs_cache().