Introduction

The section introduces a number of key concepts, and shows the basic components of a Veil-protected system:

Secured Views and Access Functions

Access controls are implemented using secured views and instead-of triggers. Users connect to an account that has access only to the secured views. For a table defined thus:

create table persons (
    person_id       integer not null,
    person_name     varchar(80) not null
);

The secured view would be defined something like this:

create view persons(
       person_id, person_name) as
select person_id, person_name
  from persons
 where i_have_personal_priv(10013, person_id);

A query performed on the view will return rows only for those persons where the current user has privilege 10013 (SELECT_PERSONS). We call the function i_have_personal_priv(), an access function. Such functions are user-defined, and are used to determine whether the connected user has a specific privilege in any of a number of security contexts (see Security Contexts). The example above is taken from the Veil demo application (The Veil Demo Application) and checks for privilege in the global and personal contexts.

The Connected User and Connection Functions

To determine a user's privileges, we have to know who that user is. At the start of each database session the user must be identified, and their privileges must be determined. This is done by calling a connection function, eg:

select connect_person('Wilma', 'AuthenticationTokenForWilma');

The connection function performs authentication, and stores the user's access privileges in Veil state variables. These variables are then interrogated by the access functions used in the secured views.

Prior to connection, or in the event of the connection failing, the session will have no privileges and will probably be unable to see any data. Like access functions, connection functions are user-defined and may be written in any language supported by PostgreSQL.

Privileges

Veil-based systems define access rights in terms of privileges. A privilege is a named thing with a numerical value (actually, the name is kind of optional).

An example will probably help. Here is a definition of a privileges table and a subset of its data:

create table privileges (
    privilege_id    integer not null,
    privilege_name  varchar(80) not null
);

copy privileges (privilege_id, privilege_name) from stdin;
10001   select_privileges
10002   insert_privileges
10003   update_privileges
10004   delete_privileges
. . .
10013   select_persons
10014   insert_persons
10015   update_persons
10016   delete_persons
10017   select_projects
10018   insert_projects
10019   update_projects
10020   delete_projects
. . .
10100   can_connect
\.

Each privilege describes something that a user can do. It is up to the access and connection functions to make use of these privileges; the name of the privilege is only a clue to its intended usage. In the example we might expect that a user that has not been given the can_connect privilege would not be able to authenticate using a connection function but this is entirely dependent on the implementation.

Security Contexts

Users may be assigned privileges in a number of different ways. They may be assigned directly, indirectly through various relationships, or may be inferred by some means. To aid in the discussion and design of a Veil-based security model we introduce the concept of security contexts, and we say that a user has a given set of privileges in a given context. There are three types of security context:

Global Context. This refers to privileges that a user has been given globally. If a user has select_persons privilege in the global context, they will be able to select every record in the persons table. Privileges in global context are exactly like database-level privileges: there is no row-level element to them.
Personal Context. This context provides privileges on data that you may be said to own. If you have select_persons privilege in only the personal context, you will only be able to select your own persons record. Assignment of privileges in the personal context is often defined implicitly or globally, for all users, rather than granted explicitly to each user. It is likely that everyone should have the same level of access to their own data so it makes little sense to have to explicitly assign the privileges for each individual user.
Relational Contexts. These are the key to most row-level access controls. Privileges assigned in a relational context are assigned through relationships between the connected user and the data to be accessed. Examples of relational contexts include: assignments to projects, in which a user will gain access to project data only if they have been assigned to the project; and the management hierarchy within a business, in which a manager may have specific access to data about a staff member. Note that determining a user's access rights in a relational context may require extra queries to be performed for each function call. Your design should aim to minimise this. Some applications may require several distinct relational contexts.

Access Functions and Security Contexts

Each access function will operate on privileges for a specific set of contexts. For some tables, access will only be through global context. For others, it may be through global and personal as well as a number of different relational contexts. Here, from the demo application, are a number of view definitions, each using a different access function that checks different contexts.

create view privileges(
       privilege_id,
       privilege_name) as
select privilege_id,
       privilege_name
from   privileges
where  i_have_global_priv(10001);

. . .

create view persons(
       person_id,
       person_name) as
select person_id,
       person_name
from   persons
where  i_have_personal_priv(10013, person_id);

. . .

create view projects(
       project_id,
       project_name) as
select project_id,
       project_name
from   projects
where  i_have_project_priv(10017, project_id);

. . .

create view assignments (
       project_id,
       person_id,
       role_id) as
select project_id,
       person_id,
       role_id
from   assignments
where  i_have_proj_or_pers_priv(10025, project_id, person_id);

In the privileges view, we only check for privilege in the global context. This is a look-up view, and should be visible to all authenticated users.

The persons view checks for privilege in both the global and personal contexts. It takes an extra parameter identifying the person who owns the record. If that person is the same as the connected user, then privileges in the personal context may be checked. If not, only the global context applies.

The projects view checks global and project contexts. The project context is a relational context. In the demo application, a user gains privileges in the project context through assignments. An assignment is a relationship between a person and a project. Each assignment record has a role. This role describes the set of privileges the assignee (person) has within the project context.

The assignments view checks all three contexts (global, personal and project). An assignment contains data about a person and a project so privileges may be acquired in either of the relational contexts, or globally.

Grouping Privileges by Roles

Privileges operate at a very low-level. In a database of 100 tables, there are likely to be 500 to 1,000 privileges in use. Managing users access at the privilege level is, at best, tedious. Instead, we tend to group privileges into roles, and assign only roles to individual users. Roles act as function-level collections of privileges. For example, the role project-readonly might contain all of the select_xxx privileges required to read all project data.

A further refinement allows roles to be collections of sub-roles. Defining suitable roles for a system is left as an exercise for the reader.

Next: The Veil API