I'm developing a system where we want to restrict the availability of information displayed to users based on their roles.

e.g. I have a tabled called EventType (ID, EventTypeDescription) which contains the following records:

1, 'Basic Event'
2, 'Intermediate Event'
3, 'Admin Event'

What I need to achieve is to filter the records returned based on the username (and hence role) of the logged-in user. e.g if an advanced user is logged in they will see all the event types, if the standard user is logged in they will only see the basic event type etc.

Ideally id like to do this in a way which can be easily extended to other tables as necessary. So I'd like to avoid simply adding a 'Roles' field to each table where the data is user context sensitive.

One idea I'm thinking of is to create some kind of permissions table like:

PermissionsTable
(
  ID,
  Aspnet_RoleId,
  TableName,
  PrimaryKeyValue
)

this has the drawback of using this is obviously having to use the table name to switch which table to join onto.

Edit: In the absence of any better suggestions, I'm going to go with the last idea I mentioned, but instead of having a TableName field, I'm going to normalise the TableName out to it's own table as follows:

TableNames
(
  ID,
  TableName
)

UserPermissionsTable
(
  ID,
  Aspnet_UserId,
  TableID,
  PrimaryKeyValue
)