This is a moot question as I'm not on this project any more, but it continues to bug me. I wonder if anyone has a better idea for future reference and general good programming practices.

The textbook approach to security is "role-based security". Every screen, report, or other task is attached to one or more roles; every user is assigned to one or more roles; and then each user can exercise the screens, etc. that match his roles and nothing else. Right?

A few years ago I led a team developing a system to manage military technical manuals. Each manual had a "technical content manager", the person responsible for writing or editing it; a "stock manager", responsible for keeping track of copies and getting them shipped out; and an "administrative manager", responsible for the budget and who therefore decided how often the book would be revised, how many copies would be printed, and so on. Of course every book had a bunch of people who would order copies and read it. (As this was the military, you had to be authorized to get your hands on a book, security clearances and all that.) We didn't normally worry about the actual readers, but rather about the people at each base who managed the libraries, but that's not really relevant here.

So ... these are obvious "roles", but a role was tied to a particular book. One person might be the technical content manager for book A, the administrative manager for book B, and a reader of 50 other books. So we couldn't really say that a user had "a role". Each user had different roles for each book.

In addition to this there were more routine system-level privileges: We had a couple of system administrators authorized to update anything in the systeem, help desk people who could see almost any data but not update, etc.

I ended up creating a database like this. (To avoid getting into some of our strange terminology I'll change some field and table names here, the idea is the same.)

Person (person\_id, name, etc)

Technical\_Manual (manual\_id, title, admin\_manager\_person\_id, stock\_manager\_person\_id, content\_manager\_person\_id, etc)

Authorized\_Reader (manual\_id, person\_id, etc)

User (user\_id, admin\_role, etc)

I was not really happy with this scheme, as it meant that security was split across three tables: the technical\_manual table, authorized\_reader table, and the user table. But ... was there a cleaner way we could have done it? Any better ideas?