Row-Based Security Decoration

Tables have rows, and there are plenty of scenarios imaginable where different rows in those tables should have different security settings. It is nonetheless exceptional for databases to support anything along the lines of row-based security.

The Problem With Middle-Tier Solutions

To mitigate this lack on the side of the databases attempts have been made to build tools that step in and fill that gap. Filtering and other enforcing of security policies is done in the middle-tier. The problem with this approach is efficiency. Too much data has to be retrieved from the database prior to the middle-tier doing the filtering based on security policies. In effect the middle-tier is burdened with doing what actually the database is good for, resulting in performance loss that can be crippling for an application.

That is a wholly undesirable situation. The database's strengths should not be discarded.

To get rid of that problem WYSE offers unique row-based security capabilities. With it, all the work is done by the database; optimizing performance.

The Framework demo hosts a section with a couple examples demonstrating WYSE's security decoration.

SQL Statement Decoration

In WYSE, tables that have rows that should be individually securable get a 'Rights' table attached to them in which all the security information is stored. That table is updated or queried during security decoration.

Selects

At runtime, when querying the original table, WYSE decorates Select statements in such a way that the security policies are taken into account, based on for which user or group the information is intended. This way, you can build your Select statement using WYSE as if row-based security were not a thing, and then have WYSE decorate it with security, prior to execution. And so all the complexity of row-based security is encapsulated and thus something you don't have to concern yourself with.

Inserts

When a row in inserted in the secured table, WYSE turns the original Insert into a script that adds a second Insert that stores the new security information in the accompanying 'Rights' table. Again, based on user or group, but also on the rights assigned.

Deletes

Like with Select statements, decoration is done here. This however relies on a database's native update capabilities. So it is not universally available.

With deletes, the 'Rights' table is kept consistent using the cascading deletes supported by foreign keys. This is done by the database itself, so no decoration is done here.

Updates

The same as with Deletes decoration holds true for Updates.

Support

Currently there are two database types that support WYSE's row-based security...

Also, because of their fixed nature, entities like views and stored procedures can't be decorated.