5

Let's assume I have a table of items:

CREATE TABLE items
(
    item serial PRIMARY KEY,
    ...
);

Now I want to introduce the concept of "permissions" for each item (note, I'm not talking about database access permissions here, but business logic permissions for that item). Each item has default permissions and also per-user permissions that may override default permissions.

I tried to think of ways to implement this and came up with the following solutions:

1) The Boolean Solution

Use a boolean column for each permission:

CREATE TABLE items
(
    item serial PRIMARY KEY,

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),

    PRIMARY KEY(item, user),

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

Advantages: Each permission is named.

Disadvantages: There are dozens of permissions which increases the number of columns significantly and you have to define them twice (once in each table).

2) The Integer Solution

Use an integer and treat it as a bitfield (i.e. bit 0 is for can_change_description, bit 1 is for can_change_price, and so on, and use bitwise operations to set or read permissions).

CREATE DOMAIN permissions AS integer;

Advantages: very fast.

Disadvantages: You have to keep track of which bit stands for which permission in both the database and the front-end interface.

3) The Bitfield Solution

Same as 2), but use bit(n). Most likely the same advantages and disadvantages, maybe slightly slower.

4) The Enum Solution

Use an enum type for the permissions:

CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

and then create an extra table for default permissions:

CREATE TABLE item_default_permissions
(
    item int NOT NULL REFERENCES items(item),
    perm permission NOT NULL,

    PRIMARY KEY(item, perm)
);

and change the per-user definition table to:

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),
    perm permission NOT NULL,

    PRIMARY KEY(item, user, perm)    
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).

Disadvantages: Even when just retrieving the default permissions, it requires accessing two additional tables: first, the default permissions table, and second, the system catalog storing the enum values.

Especially because the default permissions must be retrieved for every single page view of that item, the performance impact of the last alternative might be significant.

Can you think of other alternatives?

Which approach should be taken?

Please note: this question has been reposted on DBA.

4
  • Array of enum permissions embedded in the object, where the array contains only permissions the user has. Use GiST array indexing support for membership tests. Test carefully though - not sure GiST indexes can be used for negative tests, i.e. "does not have permission x...". Also, cache the default positions. If you don't use a caching layer like redis or memcached, now's the time. Commented Mar 30, 2014 at 12:25
  • @CraigRinger: That's another solution, but I was taught that arrays should be avoided since they violate the 1st normal form. Also, using an array of enums somehow seems ugly and convoluted to me, although that is certainly subjective. But wouldn't an array of, say, 50 different enum permissions take up a considerable number of bytes in that row? Commented Mar 30, 2014 at 13:17
  • 1
    Yes, if you're expecting that many different rights then an array is unsuitable, and I'd go for a bit(n) and good documentation. With that many permissions bits using a bigint is unsuitable too, as you only have 64 bits to play with. FWIW I agree an array of enums is ugly, but what you're trying to do is an optimisation that's sacrificing normalisation for performance anyway, otherwise you'd just have a child-table. So you're already disregarding the "clean" way (sensibly, IMO). Commented Mar 31, 2014 at 1:01
  • 1
    My suggestion is to have a table that clearly defines each permissions bit as a primary key(object_type, bit_position). You could use an enum instead, but there's not much benefit and enums have a floating point internal value to allow nondestructive intermediate value insertions, so they're not really ideal. Then have a code generator that creates a class from your in-database permissions table. Or creates SQL for the permissions table from the code. Or creates both from an intermediate representation in revision control. Whatever. Commented Mar 31, 2014 at 1:03

1 Answer 1

1

It really will depend on what level you need to go to for permissions

Role Based - Not User

User Based - (harder to maintain)

or Combination of the two

User and Roles...

The following on our systems User Table -> contains UserNames etc

Roles => list of Roles such as Office Support, Mechanic

Security Level -> List of Levels e.g. Read Only, Administrator

Application Function Table -> contains functions that can have permissions applied e.g. Can_Edit_Users

Application Security Table -> Username + Application Function Id , Role and Either bit for yes/no permission - or an integer for a more complex Role Level using the Security Level.

This gives the most flexibility but can be difficult to build the UI for.

In the application we get the users permissions once, and do a function to check if they have permissions

e.g. CurrentUser.HasPermissions("Can_Edit_Users")

public bool HasPermissions(string Permission)
{
return this.Permissions.Any(p=>p.FunctionName==Permission && IsAllowed==true);
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.