23. Tips and Guidelines

This section provides whatever tips and guidelines have occurred to the author. We can hope that the section expands over time.

23.1. Write Queries That Work Without Veil2

Generally, with a well-behaved application and well-implemented database design, Veil2 adds little performance overhead. This is because it, mostly, only applies permission checks on the records that the user sees. If the user is only going to see a few records, the overhead of checking those few records is going to be small.

However, if you make Veil2 do your query filtering for you, instead of writing properly crafted where-clauses, performance is going to suck. Consider the following queries:

select stuff
  from parties
 where org_id = 20
   and party_name like '%Bob%';

select stuff
 from parties
where party_name like '%Bob%';
      

Assume that we are in a Veil2-protected database and that we have select access only to org_id 4.

Both queries will return the same number of rows. But in a database with lots of parties, the second query will be slow.

In the second query, a full-table scan will be performed, and many Bobs from other orgs might be returned, only to be discarded by the Veil2-based security policy.

The first query will, most likely, use an index and return a much smaller set of records, which will then be filtered looking for Bob, and finally checked against the security policy. In this case the security policy has only had to check records that the user was allowed to see anyway.

23.2. Consider Reporting Blocked Accesses

For most applications, any time that a security policy blocks something, the application has done something wrong. You should consider this a bug in the application. Although no harm will be done, this is an indication of one of two things:

  • the application has not been properly implemented;
  • your security policy is over-restrictive.

In either case, it makes sense to note the occurrence and investigate. Note that in a reporting environment where ad-hoc reports can be run, this may prove less useful.

23.3. Consider Testing With and Without Security

If you have automated tests, you should run them against both secured and unsecured databases. If the tests pass in both instances, then your application and the security implementation are in harmony, and you should be very pleased with yourselves. Encourage your employer to provide handsome bonuses.

23.4. Denormalize Around Your Scopes

If you find that your security policies require joins to other tables (possibly through function calls), then your security system's performance may suffer. Consider adding scope_id columns to some tables to improve the performance of security tests. You might also consider doing this just to simplify those tests.

23.5. Use Secured Views To Implement Complex Queries

You may find that multi-table queries encounter performance issues as the security policy has to be applied for some tables on more rows than are ultimately returned by the query. If you cannot find a way to improve the query by rewriting, then consider replacing it with a secured view instead.

The secured view would implement the joins and whatever filtering can be written in to the view, and would then apply a security policy only to the resulting rows, rather than to intermediate rows that are discarded by subsequent joins in the original query.