Chapter 12. Link Your Scopes and Security Contexts (STEP 5)

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.

12.1. Create Foreign Key Links

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.

12.1.1. A Note on Keys

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.

12.2. Create Insert Triggers

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.

12.3. Create Update Triggers

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.

12.4. Copy Existing Scopes Into The Links Table

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;
      

12.5. Update the all_accessor_roles View

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.

12.6. On Caches

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.

12.6.1. Changes To Scopes

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.

12.6.2. Changes To Role Assignments

(Triggers for Maintaining our Accessor Privileges Cache)

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().