Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create a permissions system where everything is going to be stored in MySQL (or some database) and pulled using PHP for a project management system I am building.  I am right now trying to do it is an ACL kind of way.  There are a number key features I want to be able to support:

1.  Being able to assign permissions without being tied to a specific object.

The reason for this is that I want to be able to selectively show/hide elements of the UI based on permissions at a point where I am not directly looking at a domain object instance.  For instance, a button to create a new project should only be shown to users that have the pm.project.create permission but obviously you can assign a create permission to an domain object instance (as it is already created).

2.  Not have to assign permissions for every single object.

Obviously creating permissions entries for every single object (projects, tickets, comments, etc…) would become a nightmare to maintain so I want to have some level of permission inheritance.

3.  Be able to filter queries based on permissions.

This would be a really nice to have but I am not sure if it is possible.  Here's an example: I have a page that lists all projects.  I want the query that pulls all projects to incorporate the ACL so that it would not show projects that the current user does not have pm.project.read access to.  This would have to be incorporated into the main query as if it is a process that is done after that main query (which I know I could do) certain features like pagination become much more difficult.

Right now this is my basic design for the tables:

AclEntities

  • id - the primary key
  • key - the unique identifier for the domain object (usually the primary key of that object)
  • parentId - the parent of the domain object (like the project object if this was a ticket object)
  • aclDomainObjectId - metadata about the domain object

AclDomainObjects

  • id - primary key
  • title - simple string to unique identify the domain object(ie. project, ticket, comment, etc…)
  • fullyQualifiedClassName - the fully qualified class name for use in code (I am using namespaces)

There would also be tables mapping AclEntities to Users and UserGroups. I also have this interface that all acl entity based object have to implement:

IAclEntity

  • getAclKey() - to the the unique key for this specific instance of the acl domain object (generally return the primary key or a concatenated string of a composite primary key)
  • getAclTitle() - to get the unique title for the domain object (generally just returning a static string)
  • getAclDisplayString() - get the string that represents this entity (generally one or more field on the object)
  • getAclParentEntity() - get the parent acl entity object (or null if no parent)
  • getAclEntity() - get the acl enitty object for this instance of the domain object (or null if one has not been created yet)
  • hasPermission($permissionString, $user = null) - whether or not the user has the permission for this instance of the domain object
  • static getFromAclEntityId($aclEntityId) - get a specific instance of the domain object from an acl entity id.

Do any of these features I am looking for seems hard to support or are just way off base? Am I missing or not taking in account anything in my implementation? Is performance something I should keep in mind?

share|improve this question

migrated from programmers.stackexchange.com Mar 23 '12 at 16:23

This question came from our site for professional programmers interested in conceptual questions about software development.

1 Answer 1

I did something like this a long time ago. Yes it is possible. No it isn't too hard on the technical side. I don't think you will have problems in the areas you are asking about. Really this is pretty basic stuff once you figure out how acls are supposed to behave.

The issue you will run into has to do with consistently enforcing the acls. It is very easy to forget it once in a call somewhere and you suddenly have the acls not doing what they are supposed to. Oops, security issue.

If you are on PostgreSQL, you could probably use SQL roles and views to enforce this properly on select statements, and so forth. On MySQL I think this is likely to be more difficult. You will probably want to have some consistent way of adding clauses to your queries so that you are able to ensure they are enforced all the time. Do this programmatically. Do not try to do it manually unless you want security holes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.