A relational security implementation differs from the typical access controls built into an application in a number of ways:
how access rights are assigned;
where the controls are implemented;
how access is restricted;
the ease of being able to reason about the access restrictions;
the comprehensiveness of the access restrictions.
For the purpose of subsequent discussion, we are going to define a Traditional Application Security approach below. The terminology in this section is necessarily somewhat vague as it is a fairly gross generalization of typical practices. Many existing systems will do better than is suggested below, but the underlying mechanisms described should be easily recognizable and familiar.
In traditionally implemented database systems:
The security implementation is entirely in the application layer.
Since all access to the database is through the application this is the obvious place to put all access controls. Application servers provide functionally-rich environments which would seem to make this the obviously correct, and possibly the only viable approach.
Access controls are applied to functionality.
Most of the access limitations in a typical system are managed by simply preventing access to certain functions. Often menu entries will be greyed-out when you don’t have access rights.
Some access controls will be contextual: there will be explicit checks to determine whether in the context of this team/department/organization/customer/whatever you have the right to perform a specific action.
This is typically still a functional rather than a relational
access control as there is the implied function
do-x-if-I-am-a-member-of-something, rather
than the more general do-x. Often the
general do-x function will be reserved for
administrator use only.
Sometimes such functions will do some moderately sophisticated relational testing to see whether the user should be allowed to continue, and this should be considered a relational access control. That said, this is often an ad-hoc thing, and each function may have its own rules and its own implementation.
Most access rights are assigned to users explicitly, through the assignment of roles.
Roles are collections of low-level access rights (privileges), and each role will typically allow a user to perform a number of distinct tasks.
Users will typically be explicitly assigned roles, such as Customer Service Rep (CSR), Product Manager, Senior CSR, Administrator, etc.
Sometimes these are called rights or privileges but they are essentially roles.
A user gains access to functionality by being assigned the appropriate roles.
Implicit access rights are often managed by a lack of implementation.
There may be some implicit access rights that all users get, but this default position is often achieved by simply not implementing any controls at all in certain parts of the application. For instance if every user is expected to be able to see all lookup tables, then the traditional system will simply not apply any checks on lookup table access.
In our traditional system, almost all access is determined from explicit assignments of roles. With a relational security approach we will still use explicit role assignments, but these will only be part of the story.
In a relational security model, access rights are managed in multiple ways:
default role assignments;
Users can be given default roles. This is a little like the default access rights in our traditional system, but:
implicit role assignments;
It may be that we wish to record a user’s membership of a team as part of our application. We can choose to make that membership the source of a role assignment. This means that we do not need to explicitly assign roles to a user: they can instead be inferred from existing relationships in the data.
explicit role assignments;
This is similar to the traditional system,
but more sophisticated as assignments may be in specific
contexts. What this means is that you may be given the
CSR role in the context of one client, but
not in the context of others, or you may be given that role in
a department context which might give you
access to all clients managed by your department.
inherited role assignments;
If a hierarchy of scopes can be defined, roles assigned in a
given context can also apply in inferior contexts. For
example, if you have been assigned the
Administrator role in the context of a
division within your company, that role may be inherited to
apply at the department level of all departments within the
division.
These mechanisms can be as sophisticated/complex as you need them to be, but from the user’s perspective they will be unsurprising and they should therefore be simple to administer.
In our traditional system, all access controls are managed by the application. Although this may be a data-driven process, the decision to allow access or not will often involve factors that are external to the database: the application will have to make decisions based upon rules, and those rules may be, at least partly, hard-coded into the application.
With a relational security mechanism all of the decision making is made within the database. There will be no external factors such as hard-coded access-control rules. There are two distinct types of access controls that we implement:
Access controls on functions are similar to the access controls of the traditional system. The difference is that the database directly provides a decision to the application, rather than providing some of the base data for the application to make that decision based on whatever rules were implemented for the specific case in the specific application.
An application using a relational security system could
determine which menu entries should be greyed-out for a
user in the Project Management menu of
Project X, by executing a query something
like:
select i_have_priv_in_scope('exec pm-menu-item1', 'Project X'),
i_have_priv_in_scope('exec pm-menu-item2', 'Project X'),
i_have_priv_in_scope('exec pm-menu-item3', 'Project X'),
i_have_priv_in_scope('exec pm-menu-item4', 'Project X'),
i_have_priv_in_scope('exec pm-menu-item5', 'Project X'),
i_have_priv_in_scope('exec pm-menu-item6', 'Project X');
This would return true or
false for each menu entry that we are
interested in. Note that the permission tests are contextual:
whether you have permission to execute some project management
function depends on which project you want to apply it to. You
may be able to access different functions in different
projects.
Access controls on relations (tables and views) limit the data that you may see or manipulate. Just as with access to functions, this is contextual so that you may see data for some projects and not for others. What this means is that even if the application fails to properly limit your access, the database will act as a backstop. If you have not been given access to a record, then no matter how you try you will not be able to see or manipulate it. The fact that different users may see different data from the same query is why this is sometimes called a Virtual Private Database (VPD) implementation.
The application is not off the hook though. The application should not allow the user to try to do things that will not be allowed by the database. It should limit the records it tries to retrieve for the user using well-crafted queries with where-clauses that request only the data that the user should be able to see. But if it gets it wrong, little harm will be done.
In our traditional system,
access controls are primarily managed by limiting the user’s
access to specific functions, and the restrictions are primarily
implemented in code that runs on the application server. Where
privileges are required for access to specific data,
where-clauses may be dynamically added to
queries based on those privileges or on flags whose values are
based on those privileges.
With a relational security model, access to data is limited primarily by the database server itself. No application code is required to limit a user's access to data, the database just does it. Furthermore the application does not need the smarts to determine what a user can do. It can simply ask the database what is allowed. No complex application code; no surprising access control rules; and no external factors.
What this means is that all of the access control rules are built from a common framework using a single set of guiding principles. Although it is possible to implement special cases there is rarely any need. This is because the access control mechanisms are based on the highly flexible mechanism of privileges with scope.
Traditional access control systems are often built piecemeal, or as an afterthought. Even when there are guiding principles behind the implementation there are often corner cases that require special treatment. This means that the security mechanisms of traditional applications are often difficult to completely describe. Furthermore since privileges in the traditional system often imply conditionality, a simple understanding of privileges is not enough.
By placing all of your access control definitions into a relational security model, it becomes much easier to describe and reason about your security implementation. And your implementation, by virtue of its consistency, is much more likely to match what your designer intended and your customer wanted.
Earlier we stated that implicit access rights, in traditional applications, are often managed by simply not implementing them. In a relational security system, every piece of data is protected by the same underlying mechanism of privilege and scope, which means is that if something which once was viewed as public data subsequently needs to be secured, it is simply a matter of changing some privilege assignments. No code needs to be touched and once the change has been tested we can be sure that it will apply throughout the application: there will not be some obscure screen somewhere that someone created by copy and paste that gets forgotten and that does not get updated to implement the new access control.
By having only one fundamental security mechanism, and by linking it with the data that it is intended to protect, a relational security system will be more complete and more robust, and by building this into the database we can ensure that the same security model applies to all applications including reports.