How to deal with exploding node_access queries?

Question

I'm working on a pretty complex workflow site, which has a number of interlocking levels of components (content types, really). To summarize and simplify a bit:

• Users can create Companies.
• Companies can have Projects and Members (other users affiliated with the companies).
• Projects can have tasks.

Each of these things have access controls::

• Companies can be public, private, or member-only
• Projects can be public, private, or member-only (but limited by the company specification)
• Tasks are member-only

This gets (more) complicated in a couple of ways:

• Users can have different roles in Companies and Projects (admin, user, visitor, and such)
• The access rights of the higher-level thing can determine and even override the rights of the lower-level things: If a Project is marked member-only, that Project's tasks can only be seen by other members of the Company that owns the Project. In the same way, changes to Company access rights determine the actual access rights of Projects and Tasks. For instance, if a Company's status is changed from public to private, access to all its Projects and Tasks are blocked to everyone but the company's owner, even if they're marked Public.

I've gotten this working by setting up a collection of node_access functions for the various components. These install appropriate access rights into the node_access table based on the components' access settings. A parallel set of node_grants functions then build up comparable sets of grants for each of the users, based on their roles in the organization.

The good news is that this actually works. The bad news is that the queries being run against the database are getting huge, as the node_access mechanism produces a very large number of query clauses, each looking to see how the user's roles line up against the various kinds of access rights. For instance, there's a whole string of clauses testing, for each task, whether the user is a member of the project that contains that task. There's then another string of clauses testing whether the user is an admin of the project, and another string testing whether the user is a visitor to the project. And so on, leading to a future where these queries become larger and larger and worse and worse as the site becomes more successful and people put more projects and tasks into it. Blurgh.

So (finally), my question: Is this just the inevitable cost of living with Drupal and its node_access architecture? Is there another way I can control access to all these pieces of stuff without having the database calls explode on me the way they seem to be? I'm not looking for a free lunch, but I'm getting more and more worried about the complexity of these access routines and the amount of money I'm likely to have to pour into database servers to keep things running. Any advice out there?

Answer 1

I don't know how much of an answer this is, but: the approach I was taking to node_access that led to the original post was almost certainly wrong. In particular, I was setting up separate gids for each item I was writing into node_access, rather than keeping gids associated with user IDs as they are almost surely meant to be. After a bit of work, I was able to restructure this aspect of the site around this more correct approach, and was able to cut down the number of clauses added by node_access by about a factor of 10. Life is a learning experience, I guess; I could probably elaborate a bit if anybody cares...