Content Management Design >

5: Permissions

Is Bob allowed to edit this item's url?

Which users can make this item go live?

Which users are allowed to add writers to the Sports department?

These are permissions questions. At the high-level, we need a way to ask the system "Is user X allowed to do action Y on item Z?", or "Is user X allowed to do action Y in department Z?"

Each action will have a unique name -- to edit a url for an item, a user will need the 'edit_url' permission. Our system starts with these actions:

• add_item: create a content item in a department, or move a content item into that department
• edit_url: choose the urls for items in the department
• manage_staff: add or remove users to the list of staff members in the department
• publish: publish an 'approved' item or remove a live item

Many of our core plsql procedures will include security checks. cm_item_set_url starts with this call:

cm_item_verify_allowed (
    p_item_id   => p_item_id,
    p_user_id   => p_user_id,
    p_action    => 'edit_url',
    p_error     => 'You''re not allowed to change this item''s url'
);

cm_item_verify_allowed takes four arguments: the user, the item, the action, and the error to throw if the user doesn't have permission to do this action on this item. If the user has permission, the procedure passes peacefully.

Some procedures will contain more than one security check. To move an item from one department to another, the user needs the 'manage_item' privilege in that department and the 'add_item' privilege in the new department.

create or replace procedure cm_item_department_update (
    p_item_id               in integer,
    p_department_id         in integer,
    p_user_id               in integer
)
as
    v_old_department_id     integer;
begin

    cm_item_verify_allowed (
        p_item_id => p_item_id,
        p_user_id => p_user_id,
        p_action  => 'manage_item',
        p_error   => 'You''re not allowed to change this item''s department'
    );

    select department_id into v_old_department_id
    from cm_items
    where item_id = p_item_id;

    if (p_department_id <> v_old_department_id) then
        -- They're moving the item to a new department. Be sure
        -- they're allowed to put content into this department

        cm_department_verify_allowed (
            p_department_id => p_department_id,
            p_user_id       => p_user_id,
            p_action        => 'add_item',
            p_error         => 'You''re not allowed to add content to this department'
        );

    end if;

    update cm_items set
        department_id = p_department_id
    where item_id = p_item_id;

end;

cm_department_verify_allowed is similar to cm_item_verify_allowed, except it takes a department_id instead of an item_id. We use this only when there is no item_id to use (for example, when adding a staff member to a department).

Inside cm_item_verify_allowed is a query against the view cm_v_action_dept_user_map, which contains one row for every allowed combination of item, action, and user. To determine if someone is allowed to perform an action on an item, the query is just:

select count(*) into v_allowed_p
from cm_v_action_item_user_map
where user_id = p_user_id
and item_id = p_item_id
and action = p_action;

The source to cm_department_verify_allowed is similar, except it queries against the view cm_v_action_dept_user_map, which is the same as cm_v_action_item_user_map except the item_id column is replaced with department_id.

Planning for change

There are an unlimited number of ways to design permissions, but here are three designs that make sense:

• Design 1: Staff members are attached to one or more departments, and for each department they're either an editor or a general staff member. Editors are the only ones who have the 'publish' privilege. All other actions are available to everyone.

• Design 2: Each user is assigned one or more roles in a department -- 'writer', 'editor', 'designer', etc -- and their roles determine their privileges. For example, writers can 'add_item', designers can 'edit_template' and 'edit_url', and editors can do anything.

• Design 3: Privileges are set per-person, per-department. For example, "Bob is allowed to 'add_item' in the Sports department."

When building anything, you're forced to choose between building the simplest thing that will work today or building something that will solve tomorrow's problems (to the extent tomorrow's problems can be predicted). If we want design #1 today but might want design #2 next year, should we take the "simplest thing" approach and write our data model to support only #1, knowing we'll likely need to rewrite it next year, or try to support all 3 from day one?

The question to ask is "What's the cost of moving from #1 to #2 (or to #3, or to some other scheme nobody has thought of yet)?" Permission checks will be scattered throughout the code; if migrating from one design to another would require changing hundreds of queries and updating every file in the system, the "simplest thing" could be expensive in the long run.

The solution is to define an abstraction that can sit between our data model and the rest of the code and can look the same regardless of which design we choose. This is where cm_v_action_item_user_map comes in -- any design capable of answering the question "Is user X allowed to do Y on item Z" can be hidden behind this view. As long as our code base queries only against cm_v_action_item_user_map and cm_v_action_dept_user_map, and never against the underlying tables, and our underlying tables can support these views, our abstraction is air-tight and we can migration from one permissions design to the next by redefining the views.

Implementing design #1

In this design, staff members are attached to departments either as editors or regular staff members.

create table cm_department_members (
  user_id               integer references users,
  department_id         integer references cm_departments, 
  primary key(user_id, department_id),
  editor_p              number(1) default 0 not null 
);

Every action is available either to all staff members in that department, or available only to that department's editors.

create table cm_actions (
  action                varchar(20) primary key,
  only_for_editors_p    number(1) default 0 not null
);

The action/department/user map is:

create or replace view cm_v_action_dept_user_map as
select 
    a.action,
    m.department_id,
    m.user_id
from cm_actions a, cm_department_members m
where (a.only_for_editors_p = 0 or m.editor_p = 1);

We want to add one feature: a list of super-users who can act like editors in any department. We'll never say "no" to these people. We create a one-column table to store their ids:

create table cm_admins (
  user_id               integer references users primary key
);

Then create a view that's a drop-in replacement for cm_department_members that makes each admin look like an editor in every department.

create or replace view cm_v_department_members as
select 
    user_id,
    department_id,
    editor_p
from cm_department_members
UNION
-- Every admin is an editor of every department
select 
    a.user_id, 
    d.department_id,
    1
from cm_admins a, cm_departments d;

And cm_v_action_dept_user_map is updated to use the view instead of the table:

create or replace view cm_v_action_dept_user_map as
select 
    a.action,
    m.department_id,
    m.user_id
from cm_actions a, cm_v_department_members m
where (a.only_for_editors_p = 0 or m.editor_p = 1);

cm_v_action_item_user_map is:

create or replace view cm_v_action_item_user_map as
select 
    i.item_id,
    m.action,
    m.user_id
from cm_items i, cm_v_action_dept_user_map m
where i.department_id = m.department_id;

Implementing design #2

In this design, staff members are given roles like 'editor', 'writer', or 'designer', and the privileges they have are a function of their role. For example, people in the 'designer' role have the 'edit_url' privilege, people in the 'writer' role can 'add_item', etc. A user can have different roles in different departments, and can have multiple roles in a department.

-- The roles

create table cm_staff_roles (
  role_key              varchar(20) primary key,
  role_name             varchar(100) not null
);

-- Which users fill which rules in each department

create table cm_staff_role_map (
  user_id               integer references users,
  department_id         integer references cm_departments,
  role_key              varchar(20) references cm_staff_roles,
  primary key(user_id, department_id, role_key)
);

-- The privileges for each roles

create table cm_action_role_map (
  action                varchar(20) reference cm_actions,
  role_key              varchar(20) references cm_staff_roles
  primary key(action, role)
);

We want to continue letting site admins look like editors of all departments, so we create a view that is a drop-in replacement for cm_staff_role_map:

create or replace view cm_v_staff_role_map as
select 
    user_id,
    department_id,
    role_key
from cm_staff_role_map
UNION
select 
    a.user_id
    d.department_id,
    'editor'
from cm_admins a, cm_departments d;

Then cm_v_action_dept_user_map becomes:

create or replace view cm_v_action_dept_user_map as
select 
    unique ar.action,
    sr.department_id,
    sr.user_id
from cm_v_staff_role_map sr, cm_action_role_map ar
where sr.role_key = ar.role_key;

cm_v_action_item_user_map is the same as before.

Implementing design #3

In this design, roles disappear and privileges are set per-person, per-department -- for example, "Bob is allowed to 'add_item' in the Sports department."

We need only one table:

create table cm_action_dept_user_map (
  action                varchar(20) references cm_actions,
  department_id         integer references cm_departments,
  user_id               integer references users,
  primary key(action, department_id, user_id)
);

Like before, admins should be allowed to do all action, so cm_v_action_dept_user_map becomes:

select 
    action,
    department_id,
    user_id
from cm_action_dept_user_map
UNION
select
    ac.action,
    d.department_id,
    ad.user_id
from cm_admins ad, cm_departments d, cm_actions ac;

cm_v_action_item_user_map is the same as before.

Using permissions in the UI

A staff member editing a story should see links to forms for the fields he is allowed to edit.

Each of those "edit" buttons is backed with a permission or workflow check.

• Links to edit the department are available if the user passes a 'manage_item' permission check. (Once the user reaches the "edit department", they'll be able to choose from the departments for which they have the 'add_item' permission.)
• The link to edit the url is available if the user passes an 'edit_url' permission check.
• The "edit state" link is available if cm_item_editable_by_user_p returns 1 (covered in next chapter)

To make our queries more readable, we create two plsql functions:

• cm_action_dept_user_allowed_p (action, department_id, user_id)
  returns 1 if this user is allowed to perform this action in this department, returns 0 otherwise.

• cm_action_item_user_allowed_p (action, department_id, user_id)
  returns 1 if this user is allowed to perform this action on this item, returns 0 otherwise

The query that makes the checks above is:

select
    ...
    cm_item_editable_by_user_p(
        item_id, :user_id) as can_change_state_p,
    cm_item_user_allowed_p(
        'manage_item', item_id, :user_id) as can_manage_item_p,
    cm_item_user_allowed_p(
        'edit_url', item_id, :user_id) as can_edit_url_p
from cm_v_content
where revision_id = :revision_id

Next: Workflow

---

Send questions/comments/errors to dvr@dvrodriguez.com