Storing user access level in a database.

Question

I am storing a list of "Users" in a table. The business logic of the application will have a reference to an object with all the data in this table for the currently logged-in user. And be able to allow the user to perform operations if they have the correct access.

I'm wondering what is the best way to store "access levels?"

One way I'm thinking of storing the access level is as an integer, and using C# "flags" to combine multiple access levels without requiring a bunch of fields, is this wise?

Create  = 1
Read    = 2
Update  = 4
Delete  = 8
FullAcc = 16

The other option I'm thinking of, feels less elegent, but I've seen it done a lot:

Read/Write  = 1
R/W + Delete= 2
Full Access = 3

The reason I'm wondering, is that it seems like it would be more simple to add additional items to the second method, but at some point, it would become a pain in the ass to maintain. What are your thoughts?

Answer 1

I would go with Option #1 because it gives me individual flags for each type of access.

I would also recommend that you store history of changes with timestamps.

Answer 2

I've always preferred the first approach using flags. The danger is that you get too many levels of permissions and you have to keep extending your enum and start using huge numbers and therefor maybe have to change the data type in your database to a large int. However, for something like permissions the number of options should be fairly limited. The one suggestion I would make is to have FullAcc defined as the sum of Create, Read, Update and Delete instead of as a separate entity. That way you won't have to check if a user has Update OR FullAcc permissions when they are trying to update something.

Answer 3

I'd go the enum route. Its strongly typed, transfers reasonably well between the db and code (ints and enums cast well), you can use the FlagsAttribute to combine security rights, and enums are pretty flexible when it comes to versioning issues (as long as you don't remove or rename previously defined enum values).

Answer 4

Your 'flags' idea is more flexible, allowing you any combination of rights if that ever becomes necessary. The 'FullAcc' item should not be defined as a specific number in your enum, however - it should be a combination of other flags or'd together (like this, with a few left out):

enum Rights { Create, read, Update, FullAcc = Create | Read | Update }

The only pain I see with this is if you add more items to the enum, you have to modify the FullAcc item, and then identify your FullAcc records in the db and update the flag value.