> Have you considered what it will take to provide row/column level controls?
FWIW I did this for a client application and it wasn't real fun. Took a few tries to come up with something that was reasonable at column-level resolution.
Figure you need at least read and write permissions, you need to have table-level granularity, you want to have column-level granularity, you want to make it easy to manage groups of permissions (roles), but you also want it to be easy to create users with exceptions to those roles. For example, how would you create permissions for a group that gives you access to all of a table except a specific column?
You need, basically, ACLs, and afaik there isn't a standard recommendation for that yet (that I could find).
I settled on a roles table and a permissions table, with descriptors with wildcards for different operations (like, "read-client-address", "write-client-notes", "∗-client-∗"), and allow/disallow/inherit options. For instance, a "demo" account has "disallow ∗, allow read-client-∗, disallow read-client-name", etc. On login the permissions are loaded for the user signing in and then each DB operation checks to see if the operation is allowed() in a function that resolves wildcards and priority and does a few other tricky things.
There are probably better ways to do it, but anyway, the moral is that there should be a good reason for needing more granularity in permissions before someone decides to implement it.
FWIW I did this for a client application and it wasn't real fun. Took a few tries to come up with something that was reasonable at column-level resolution.
Figure you need at least read and write permissions, you need to have table-level granularity, you want to have column-level granularity, you want to make it easy to manage groups of permissions (roles), but you also want it to be easy to create users with exceptions to those roles. For example, how would you create permissions for a group that gives you access to all of a table except a specific column?
You need, basically, ACLs, and afaik there isn't a standard recommendation for that yet (that I could find).
I settled on a roles table and a permissions table, with descriptors with wildcards for different operations (like, "read-client-address", "write-client-notes", "∗-client-∗"), and allow/disallow/inherit options. For instance, a "demo" account has "disallow ∗, allow read-client-∗, disallow read-client-name", etc. On login the permissions are loaded for the user signing in and then each DB operation checks to see if the operation is allowed() in a function that resolves wildcards and priority and does a few other tricky things.
There are probably better ways to do it, but anyway, the moral is that there should be a good reason for needing more granularity in permissions before someone decides to implement it.