Tiered permissions to generate conditional data access

Question

I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on access-level, and the other based on physical location.

For instance, an engineer will have greater complexity and more access than someone within HR. While at the same time users in China will be able to review information specific to them, and will not be able to see information for Australia. I thought about creating a permissions table, with all the possible combinations stored in it, and each unique combination would have a number associated with it, but I would like administrators to have the ability to add new permissions at will and in a manner that is fairly painless. I have also toyed with the idea of a single character for company and another for group-level, but this would give a limit of 62 companies (assuming for simplicity case-sensitive alphanumeric values) and 62 regions. Is there an obvious solution that I'm just not seeing?

Answer 1

It isn't too hard to create table-driven access rules if you can base the rules on something that is in the data already, like a country code.

You want to beware the power of multiplication. Don't try to create access rules based on the cross-product of two or three or more factors. The management of such a rule set will quickly become impractical due to the shear amount of data.

Instead, keep each access rule separate (i.e. country, department) and treat each as a gate that the user has to get through before they get access. This will be a bit of work when setting up your data access code, but it is the best way to operate the system in the long term.

Answer 2

Some time ago I did this, but it was a number of years, back before I switched to PostgreSQL.

What I did was I created a roles table, a table for granting roles to users, and a table for granting tables to roles. Then I had a a part of the program that would "compile" these to MySQL grants for individual users. It worked. I didn't support nested role grants, though. It basically works by allowing the permissions managemnet to be done from the viewpoint of the roles defined in the catalogs that I created, and on the other side, the users get their permissions from their role memberships, so you can look at the join back that way. It is a many -> many join naturally.