User Table design for user management with multiple roles/privileges

Trying to setup a user login and management...

I was wondering what table setup you would use for user management that can have complex user roles.

I'd like to have several levels of user rights/roles, and within each level have more detailed controls if necessary.

I picture the set of privileges as a table. Rows are the resources, and columns are properties and available actions. I'd like to devise a system where a user can have access to a group of开发者_JAVA百科 resources with certain privileges, with the possibility of having several groups with different privileges.

I'm looking at the way modx is doing the user tables, but that might be a bit more complicated.

---

I have recently approached this problem, and ended up using a solution that approximates role based access control

In broad strokes, the system I implemented was described as

• Subjects: actual users with the ability to authenticate, for instance, with a password
• Permissions: to represent any controlled resource; If a user has a permission, she is authorized to use that resource;
• Roles: The connectives between subjects and permissions; each role is assigned some set of subjects, permissions, and other roles;

The important bit to understand is that a role assignment; the connection from one role to another, is not reflexive; The subjects in a parent role have access to the permissions in a child role, but not the other way around, subjects are not authorized on permissions through the parents of the roles those subjects are assigned to. An added constraint is that a role is not permitted to inherit from itself; although this is really only to keep the resulting graph of roles easy for humans to understand and avoid accidentally granting permissions to large groups of subjects.

With the right combination of permissions, this turns out to be very expressive; You can have roles to grant general access, as well as authorizing more specific verbs or objects; and then your application checks to make sure that the subject has all of the needed permissions (can access, can do this action, can modify that object)

The structure the roles ends up taking is a partial order; and a simple adjacency list of roles turns out to be an inconvenient way of querying for a subject's permissions (on MySQL, where it would be running), So I also had to devise a set of triggers to keep a second relation up to date to express the transitive closure of the role assignments.

Update:

It seems the recursive structure of roles added more complication than it solved, at least for this particular application, since the roles are largely managed by customer support, instead of by application design itself. The way I've implemented it currently, users can be in groups, and each group has a set of permissions assigned to it. Each "assignment" has a priority, and can either grant or refuse the corresponding permission. the permission with the highest priority wins. the schema is roughly:

CREATE TABLE user (
    username VARCHAR PRIMARY KEY,
    hashedpassword VARCHAR);

CREATE TABLE groups (
    groupname VARCHAR PRIMARY KEY );

CREATE TABLE user_groups (
    username VARCHAR PRIMARY KEY REFERENCES user(username),
    groupname VARCHAR PRIMARY KEY REFERENCES groups(groupname),

CREATE TABLE permission (
    permissionname VARCHAR PRIMARY KEY,
    defaultpolicy BOOLEAN );

CREATE TABLE group_permission (
    permissionname VARCHAR PRIMARY KEY REFERENCES permission(permissionname),
    groupname VARCHAR PRIMARY KEY REFERENCES groups(groupname),
    priority INTEGER,
    policy BOOLEAN );

The non-recursive structure simplifies the heck out of resolving a permission, effectively;

(
SELECT policy, priority 
FROM group_permission
JOIN user_groups ON user_groups.groupname = group_permissions.groupname
WHERE username = :username
AND permissionname = :perm

UNION

SELECT 
    defaultpolicy as policy,
    (SELECT min(priority) - 1 FROM group_permission) as priority
FROM permission
WHERE permissionname = :perm
)
ORDER BY priority DESC
LIMIT 1