CODEWITHSUNDEEP
databasedatabase-design
neizan
neizan

Reputation: 2321

Database design - granting access to records

I am designing a database that restricts access to certain objects. My colleague and I have discussed different ways to approach this, with there being to main candidates: 1) Implicit access, and 2) Explicit access

For illustration, let's assume there are the following tables:

User (Id)
ObjectA (Id, ParentId) -- Where ParentId is an ObjectA
ObjectB (Id, ObjectAId)
UserObjectA (UserId, ObjectAId) -- Grants access to an ObjectA
UserObjectB (UserId, ObjectBId) -- Grants access to an ObjectB

Implicit approach:

  1. Because ObjectA serves as a containing entity for ObjectB, if a user has access to an ObjectA that is the container for an ObjectB, then he also has access to the contained ObjectB, even though there is no such explicit record in UserObjectB.
  2. Similarly, if a user has access to a parent ObjectA, then he has access to all ObjectA descendants, even though there is no such record in UserObjectA.
  3. Additionally, if a user has no records in either access-granting table, then implicitly he has access to all records in ObjectA and ObjectB.

Explicit approach:

  1. To have access to either an ObjectA or ObjectB record, a user must have a record in UserObjectA or UserObjectB, respectively.
  2. No record equals no access, period.

The implicit approach has two benefits: 1) Save space when a user has access to many objects implicitly, and 2) a user who implicitly has access to all objects will have access to all objects added in the future without having to trigger inserts or handle the inserts in sprocs when objects are created.

The explicit approach has the benefit that queries are much simpler, maintanable, and more performant.

Initially, we ran with the implicit approach. However, after getting into the implementation of various sprocs, the logic to handle the access is becoming a beast and there are various subtleties that we've run into that make this approach more error-prone than the explicit approach. (Note that the real scenario is somewhat more complicated than the simplified example.) I'm finding myself constantly implementing recursive CTEs to determine access, which doesn't allow me to (when considering performance) abstract away certain parts of the logic in views or inline TVFs. So I have to repeat and tweak the error-prone logic in lots of different sprocs. If anything ever changed, we'd have a big maintenance task on our hands.

So, have we made a mistake going with this implicit access approach? I'm definitely having second thoughts and would appreciate advice form anyone who has experience with similar design decisions.

Upvotes: 2

Views: 71

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48297

If you can wait a month Postgres 9.5 will be out and has Row Security. Oracle has it now if you have ten million bucks kicking around.

For now, or in other dbs, you can mimic row security:

  1. Each protected table gets an "owner" column. By default only the owner can select, update, or delete that row.

  2. Each "child" table also has an owner column, with a cascading foreign key to the parent table. So if change parent.owner, then this changes all children.owners as well

  3. Use updateable CHECK OPTION views to enforce security.

  4. You need to set current_user from your application. Here's how for pg + spring

In Postgres:

create schema protected;

create table protected.foo (
  foo_id int primary key,
  bar text,
  owner name not null default_current user
);

create table protected.foo_children (
  foo_child_id int primary key,
  foo_id int not null references foo(food_id),
  owner name not null default current_user references foo(owner) on update cascade
);

Now some CHECK OPTION views - use security_barrier if postgres:

create view public.foo with (security_barrier) as 
  select
    *
  from protected.foo
  where
    owner = current_user
WITH CHECK OPTION;

create view public.foo_children with (security_barrier) as 
  select
    *
  from protected.foo_children
  where
    owner = current_user
WITH CHECK OPTION;

grant delete, insert, select, update on public.foo to some_users;
grant delete, insert, select, update on public.foo_children to some_users;

For sharing, you need to add some more tables. The important thing is that you can index the right columns so that you don't kill performance:

create schema acl;

create table acl.foo (
  foo_id int primary key references protected.foo(foo_id),
  grantee name not null,
  privilege char(1) not null
);

Update your views:

create or update view public.foo with (security_barrier) as 
  select
    *
  from protected.foo
  where
    owner = current_user
    or exists ( select 1 from acl.foo where privilege in ('s','u','d') and grantee = current_user) );

 --add update trigger that checks for update privilege

 --add delete trigger that checks for delete privilege

Upvotes: 1

Related Questions