The single worst-performing SQL query that I've ever written was one whose job was to propagate permission rules to figure out exactly who could do what, when. It scaled like O(n^5). I broke it into several pieces and it improved to O(n^4). It got slow again and we moved it to a better machine.

It was a lot more flexible than the one you describe, was simple to use, and was very fine-grained. But the logic behind the scenes to make it work was a bear. But it did work well for a few thousand users with hundreds of permissions each.

The reason that I needed a complex scheme was that I needed to grant access to a lot of people based on the kind of data they were accessing, the kind of company they were associated with, the terms of their subscription (when your term expires, so does your permission), and our decisions about when to change specific parts of our site between being premium and standard. And I had to keep it simple enough for our sales reps to be able to use the system. "Why don't I give you a 30 day free trial..."

Cheers,
Ben