How to design table for role-based security with special Update actions 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
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.