Below is what I use for permissions
All the actions that need to be restricted...
actions
id varchar(50)
description varchar(255)
+-------------+--------------------------------------------------------------+
| id | description |
+-------------+--------------------------------------------------------------+
| VIEW_SSN | Allow a user to view a complete Social Security Number (SSN) |
| DELETE_USER | Admin: Allow a user to delete another user |
| ........... | ............................................................ |
+-------------+--------------------------------------------------------------+
The roles a user can be associated with. Generally speaking these are usually (but not always) strongly associated with the department a user is in.
roles
id unsigned int(P)
description varchar(50)
+----+-------------------+
| id | description |
+----+-------------------+
| 1 | HR: Clerk |
| 2 | HR: Manager |
| 3 | HR: Director |
| 4 | IT: Administrator |
| .. | ................. |
+----+-------------------+
Associate actions with roles. Both columns are foreign keys to their respective table and together they form the primary key. In my example data you see that the HR Clerk, HR Manager and HR Director roles can all view social security numbers while the IT Administrator can delete user accounts.
roles_actions
role_id unsigned int(F roles.id)----\__(P)
action_id varchar(255)(F actions.id)--/
+---------+-------------+
| role_id | action_id |
+---------+-------------+
| 1 | VIEW_SSN |
| 2 | VIEW_SSN |
| 3 | VIEW_SSN |
| 4 | DELETE_USER |
| ....... | ........... |
+---------+-------------+
Of course you've got your users...
users
id unsigned int(P)
username varchar(32)(U)
...
+----+----------+-----+
| id | username | ... |
+----+----------+-----+
| 1 | bob | ... |
| 2 | mary | ... |
| 3 | john | ... |
| .. | ........ | ... |
+----+----------+-----+
And finally we have to associate users with roles. Like the roles_actions table each column is a foreign key to it's respective table and together they form the primary key.
In my example data we see that bob is an HR: Manager, mary is an HR: Director and john has both HR: Director and IT: Administrator roles (so he could view social security numbers as well as delete user accounts).
users_roles
user_id unsigned int(F users.id)--\__(P)
role_id unsigned int(F roles.id)--/
+---------+---------+
| user_id | role_id |
+---------+---------+
| 1 | 2 |
| 2 | 3 |
| 3 | 3 |
| 3 | 4 |
| ....... | ....... |
+---------+---------+
To find out if a user has a particular permission do something like:
SELECT COUNT(r.action_id)
FROM users_roles u
LEFT JOIN roles_actions r ON u.role_id = r.role_id
WHERE u.user_id = :user_id and r.action_id = :action_id
Having 10K active users shouldn't be a problem. Managing 5K roles could be a huge pain but my guess is you'd never have anywhere near that many roles. I'm currently logged into a client's system and they have 2,500+ users and are only using ~35 roles. That's lower than I expected but even for an organization of 10K users I wouldn't think you'd need more than a few hundred roles.
I chose to implement permissions this way rather than through inheriting groups like you're considering because I felt this gave me more flexibility. Every role can have an unlimited number of actions and every user can have an unlimited number of roles. For the clients I've worked with the idea of a role has always coincided very closely with a job function like "Payroll Clerk" or "Accounts Receivable Manager". So if you have "Payroll Clerk", "Accountant" and "Tax Specialist" roles in the accounting department and you have a new Director of Accounting, you just give him/her those three roles and they have permission to do everything his/her employees can do - takes about 10 seconds using a good jQuery interface.
If you really do end up with too many roles to manage effectively you could add another column, group_id, and associate individual roles with particular groups. This would make it easier to find all the roles associated with the "Accounting" group for instance.
Obviously this was far too much to put in a comment :-) This kind of design may or may not work for you in your current environment - it's worked well for me in about a dozen very different businesses.
