Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Well, you could switch to a database management system that supports role-based security (PostgreSQL, Firebird, Oracle, DB2, SQL Server, etc ;-) ) –  Chris Travers Sep 6 '12 at 14:29
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.