Logan Buesching

I have been working on the MyEPICS framework for a few things here and there, and we have this Role Based Access Control (RBAC) system in place. Now, one thing that has caught my attention is the need to find _every_ action that a user is able to perform, and what object he can perform them on. The query is pretty gnarly:

SELECT Object.name, Action.name FROM UserRole
INNER JOIN DomainPrivilege ON DomainPrivilege.roleid=UserRole.roleid
INNER JOIN Role ON UserRole.roleid=Role.id
INNER JOIN Domain ON DomainPrivilege.domainid=Domain.id
INNER JOIN DomainObject ON Domain.id=DomainObject.domainid
INNER JOIN Object ON DomainObject.objectid=Object.id
INNER JOIN Privilege ON DomainPrivilege.privilegeid=Privilege.id
INNER JOIN PrivilegeAction ON Privilege.id=PrivilegeAction.privilegeid
INNER JOIN Action ON PrivilegeAction.actionid=Action.id
WHERE userid=?

Is there some other way to accomplish the same task, or do I just have to join 9 different tables to get this information, and what are the performance risks for doing this?

Trackback this Post | Feed on comments to this Post