How to design table for role-based security with special Update actions

Question

I am trying to design a table that will contain the following information. Basically this is a table to lookup security roles. The catch for this is that for each ObjectType, you might have special actions they can or cannot do.

For example, ObjectType A, you can Assign, Complete, Close, for ObjectType B you can Recieve, Release, and Reject. For each of these special actions they can or cannot do them.

• UserID
• ObjectTypeID
• Create (yes or no)
• Read (yes or no)
• Delete (yes or no)
• Update (yes or no, in addition to a list of ActionID's that are specific to ObjectType)

note: int? = nullable integer

I came up with the following table structure, but the problem is that I would have to add another column if I wanted to I realized I don't have enough columns to contain all possible actions for that specific ObjectType. Also, that just seems kinda of sloppy, and not very Normalized.

How I can make this more Normalized?

UserID|ObjectTypeID|Create|Read|Delete|Update|Action1|Action2|Action3|...
int   |   int      |  bit |bit | bit  | bit  | int?  |  int? |  int? |...  


ObjectTypeID|ObjectType|Description
  int      |    int   |  varchar


ActionID|ObjectTypeID|ActionDescription
  int   |    int     |    varchar

Answer 1

Just in keeping with the KISS principle, first, make a list of all actions:

create table Actions(
  ID     int auto_generate primary key,
  Name   varchar( 16 ) not null -- "Create", "Read", etc.
  ...    -- other data describing the action
);

Now create an intersection table between the user, the object and the actions. Nothing fancy, if a user is tied to an action, they can perform that action. If not, they can't.

create table UserActions(
    UserID   int not null references Users( ID ),
    ObjectID int not null references Objects( ID ),
    ActionID int not null references Actions( ID ),
    constraint PK_UserActions primary key( UserID, ObjectID, ActionID )
);

The PK prevents multiple entries of the same User/Object/Action combination. If there in an entry for a particular user and a particular object with, say, the id of READ, then that user can perform that action on that object. Otherwise, they can't. Simple and scalable. And thoroughly normalized.

Answer 2

It is not advisable to grant/revoke permissions at the user level. Users come and go all the time, but the roles that users fulfill are much slower moving. Using Role-Based Security dramatically reduces the amount of data that needs to be maintained in order to secure your application.

Your first table includes a UserID as well as the permissions for that user. This is not role-based security, it is direct user-based security.

For a general model for role-based security, see my answers to this question and this question.

To answer your question:

How I can make this more Normalized?

What you want to do is turn the various permission columns in your first table into rows. This means that instead of having a table with columns like your first table, you would have first an action lookup table:

create table ProtectedAction
( ActionID    int not null 
, Description nvarchar(100) not null -- Length as needed
, constraint pk_ProtectedAction primary key (ActionID)
)

This table contains a list of actions, including the common ones, like READ, UPDATE, DELETE etc. as well as any uncommon or even unique ones.

Then you would have an intersection table that describes which objects have which actions:

create table PossibleObjectAction 
( ObjectTypeID  int not null
, ActionID      int not null
, constraint pk_PossibleObjectAction primary key (ObjectTypeID, ActionID)
, constraint fk_PossibleObjectAction__ObjectType 
  foreign key ( ObjectTypeID ) 
  references ( ObjectType.ObjectTypeID )
, constraint fk_PossibleObjectAction__ProtectedAction
  foreign key ( ActionID )
  references ( ProtectedAction.ActionID )
)

Lastly, you have a table which pivots your first table into a normalized permission table like so:

create table GrantedPermission
( RoleID        int not null
, ObjectTypeID  int not null
, ActionID      int not null
, constraint pk_PossibleObjectAction primary key (RoleID, ObjectTypeID, ActionID)
, constraint fk_GrantedPermission__Role
  foreign key ( RoleID )
  references ( Role.RoldID )
, constraint fk_GrantedPermission__PossibleObjectAction 
  foreign key ( ObjectTypeID, ActionID ) 
  references ( PossibleObjectAction( ObjectTypeID, ActionID ) )
)

(Note that the above also requires an intersection between user and role in order to be truly role-based security.)

This model is in Third Normal Form (3NF) and gives you a declarative/data-driven way of defining not only what permissions are assignable, but which roles have been assigned which permissions.