Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

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 questionthis question and this questionthis 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.

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.

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.

Source Link
Joel Brown
  • 12.6k
  • 2
  • 32
  • 46

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.