Access rights database design

Question

Which one would be a better pattern for designing a database for storing access rights?

1. actor {actorid, name, password, canpost, cancomment,canremoveuser, candothis, candothat}
2. actor {actorid, name, password} rights {actorid, action, isallowed}

Answer 1

Your first option is not as flexible as it could be and your second option is not as managable as it could be.

The standard pattern for access control is called Role Based Security. As both the number of users and the number of different types of permissions you need grows, the management of your user-to-permissions links can become increasingly difficult.

For example, if you have five administrators and fifty users, how do you keep the permissions of each group in synch? When one of your users is promoted to an administrator, how many edits do you need to make? The answer is to create two intersections: users-to-roles and roles-to-permissions.