Permissions per Record - best practice?
-
What do you think is best practice to implement permissions per record?
For example some users should be allowed to edit certain address-recordsets, but on others they should only have read-permission. The permissions per record may be different for each user.
THX
Markus
-
You really need to build a control system into your application and database to do this. There was a similar thread a while back and I meant to put out the concept we use, but basically we create and audit level system with what levels that audit can affect, then the table has a column for the value that allows specific auditors to do specific types of work. I will try to lay otu and post in the next few weeks but I am behind on a few things right now.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
We have a system in one app that is not bad. Keep all the authorized users in one table, it has a series of bit columns called RoleThis, RoleThat. If its 1 they are allowed to do whatever we code for in the app for that role. For example, we have a RoleManager role, they get access to some things standard users dont, plus in some cases they cant do things you can only do if connected as a standard user.
I know a "better" implementation would be to separate those permissions out into a separate table, but for what we needed and can forecast, this is good enough!
Andy
-
Like the idea of Andy. We have implemented it in a very similar way. Only three changes :
We put in an additional level : Group. Makes life (and administration) a lot easier
.Like Andy suggested, we put the privileges in a separate table. A user can have both individual privileges, or he can inherit privileges from the group(s) he belongs to.
We have put all userright checking in the DB. All users have access to execute stored procedures. Each SP has parameters (user, password) that get checked before executing the SP. Return value indicates Error, No Access, Success.
-
If you put the information in a separate table, but you don't want to have to worry about users dealing with complicated joins, you can expose views which handle the joins. Give the appropriate rights to the various views and not the tables and so long as the ownership chain isn't broken, they have only the permissions they should. It's a basic implementation of row-level security since SQL Server doesn't have such built in, but it works.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
I hate to post a "Me too," but the method Brian mentioned is how we do it as well... I have a data table, a users table, a permissions table, and a users/data/permissions table, and use views to simplify the permissions and the selects.
- Troy King
- Troy King -
Won't the users/data/persmissions table become VERY large? As I see you'll get one record per user/group per record in every other table. So if you have 2,000,000 records in all tables an only 10 users you'll get 20,000,000 records ...
(OK 20,000,000 is not too much for sql-server, but you'll get the idea of what I mean 😉 )
-
Depends on how you setup checking permissions. Personally when the users app connects I get their security permissions and store them to avoid eaxtra table reads. Then when I connect to a record I check their permissions against the record to make sure they rights on the record or what those rights are. However when reviewing mutliple records I am just providing their credentials for verification. I don't see a good reason to join all records myself as, like you state this could potentially make a long/large dataset which does add some overhead to queries.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
quote:
Won't the users/data/persmissions table become VERY large? ... 20,000,000 records ...Well how else are you going to set per-record, per-user permissions? Do you really have per-record requirements? Maybe there's some way to reduce it to just certain classes of requirements, like User 1 can edit all of A-type records, read-only on B-type records etc.. If you truly need per-record, per-user permissions, then you don't have a ton of choice.
- Troy King
- Troy King -
To piggy-back on Troy, if you need each row to be completely custom with respect to security, then yes, there's going to be a lot of records. There's no way around this. Take a look at the syspermissions on any database with a good number of objects and you'll see a lot of rows.
As far as using the application to track security, that works so long as all access is controlled through the application. However, a savvy user could bypass the app unless you are using app roles. This is why row-level security and views are sometimes the only option.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
2nd vote for Brian's explanation.
Steve Jones
-
THX to you all for your Help.
Markus
-
quote:
a users/data/permissions table, and use views to simplify the permissions and the selects.- Troy King
The concept: 'users/data/permissions table' does this mean we require one of these tables for every data table we want to apply row security to ?
-
It depends. A Common case for only one permissions table is where you might have a table for employee (contains the employee login id and the division the employee belongs to), a table for customers, and a table for sales. You could make sure that a given employee only was able to touch the records for his or her division. You could use the employee table joined to either sales or customers to ensure the employee only accessed the records which were applicable to his or her division.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Thank you, I did understand this neat implementation. This is what I think you mentioned in the first post on this discussion, right?
The employee table and the customer or sales tables would constitute the user and data tables respectively. However Troy mentioned two other objects: the 'permissions table' and the 'user/data/permssions' table? This is what threw me a little.
.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply