How to assign .net membership roles to individual database records

Question

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
)

Answer 1

You don't mention what (if any) database you're using, but assuming SQL Server then if you connect using Windows Authentication you could create views or stored procedures that filtered the data based on the SYSTEM_USER function.

Answer 2

We do something similar, Our solution was to use a table valued function to join against. ie.

select * from events e inner join [dbo].fn_AvailableEvents(@User_ID) a on e.id = a.id

the function only returns the event id of the events the user is allowed to see.

Answer 3

Even if you don't use it, that being a shame, you can at least learn the ins and outs of the concept from Rhino.Security.

Answer 4

I did something similar whilst building a CMS ...

Essentially i created a few tables in the db: Users Roles UsersInRoles Objects ObjectPermissions

Ok it works something like this ...

The first 3 are pretty self explanitory, users, roles and the links between them. A user is granted permissions based on their membership to roles.

The next thing i do is define the "objects" i want to control the permissions to and the permission levels i want to assign them ...

So objects contains a list of the object definitions which are then extended in other tables that are related by object id and the ObjectPermissions table basically links an object to a role.

Now at this point it might be worth explaining something about roles ...

A role as i see it is a list of permissions, nothing more and nothing less.

So if i create a role called guest and set the role to allow the read permission then create a role called admin which has global rights to do everything i can then do something like this ...

Add user 1 Admin role. Add Object 1 to Admin role.

User 1 would now have full access to object 1 and the important thing here is that permissions are inherited so any child objects (think file and folder permissions) would also be in the same role set unless overridden recursively.

so by default i assign the root level object to every key system role. I then selectively add users to various roles at different points in the tree.

Does this make sense?

Essentially I can choose any object and any user and grant a varying degree of permissions to the user on that object and its children by adding the user to the same role as the object.

Now there's some things to note about this ....

If i wanted a user to have admin permissions on a deeply nested object but not at root level I would have to create a new role that grants all permissions and add both the user and the object to it.

The reason being that if i added the user to the main admin role the user would be given those rights from root level up not from my nested object.

theres more to it than this but essentially this is how file system permissions work.