I believe my problem is something similar to this but I am not really sure. I am not even sure my title is on topic. Using mysql. I have the following ACL design for a website:
### tools id name ### tasks id tool_id references tools.id name ### roles id tool_id references tools.id name ### access id role_id references roles.id task_id references tasks.id has_access
Website has several tools, each tool can do several tasks. Each tool has several roles (ex: admin, mod, user) and each role is allowed to perform specific set of tasks based on access table.
Problem: how do I achieve that access.task_id belongs to the correct tool and not some random tool? For example, if we have role "editor" in tool "articles" which has tasks "delete", "edit" and "publish", how do we prevent someone inserting into access table a task id belonging to "execute_shell_script" which is from a totally different tool? So roles.tool_id=tasks.tool_id for each row.
I suspect that my design is flawed in some way so feel free to point me to a better solution.
And a more general question, when db constraints get really complicated is it generally better to do them only programatically?