Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
giovi2002
Starting Member
46 Posts |
 Posted - 2005-07-31 : 16:32:57
|
| Goal : Certain users cannot edit records of other users. The permission model tells who can edit others recordsHow: many ways to rome but only few are code effective and administratively workableimplementation : I think of one stored procedure with the permission hierarchy and of course usage of insert_user column per table which stores the user nameProblems(1):What if the user name vanishes because the employee gets fired and a new employee has the same name? that new user could edit all old records because of the same name. Problems (2):to execute the stored procedure I need to define a trigger at each table which will test the userid against the permission hierarchy in the sp. Will I be able to handle recordsets with such a trigger invoking a sp? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-31 : 16:42:48
|
| Are you sure you want a hierarchy?We use Groups for this type of thing. A user can be a member of multiple groups, in order to Edit Record_A then must be a member of the group "FOO". So we just check that they are a member of "FOO" in the SProc when they attempt to edit Record_A.We use an IDENTITY for the user. If they leave no new user will get that Identity - but for good measure we CASCADE delete the User record and all its permission records, so IF another user got the same ID they would NOT automatically get all the original permission groups.If you actually want to set a LIST of UserID who can edit a specific record then I think you will have two tables:MyDataRecordTable - RecordID + other columnsMyEditPermissionTable - RecordID + UserIDWhen a User Record is deleted then it will be important that all the MyEditPermissionTable records where UserID matches are also deleted.Kristen |
 |
|
|
giovi2002
Starting Member
46 Posts |
Posted - 2005-07-31 : 16:55:09
|
| Hi Kristen, I'm not really going to use a hierarchy but a simple name or id check. Some users are super-users, in the business only one or 2 people are allowed to change records of others. So i can keep my permission check simple within my sp. Problem is the trigger as i don't rely trigger and sp will be able to handle recordset inserts and updates. Record level permission must be an old very old problem from the dark ages of sqlserver till now, so please tell me what's the easiest way to implement? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-31 : 17:53:20
|
We tend to do it in the SProcs that "Get" the data; we have a whole layer of security, and as part of that each Sproc call is passed a "Session ID" which it can use to discover who the logged-on user is for that seesion, and from there what their permissions are etc.I've seen other people who do it using a VIEW that uses USER_NAME() to reference the currently-logged on user - works well if each user uses a SQL Login (rather than a single application login to SQL)That would use something like:CREATE VIEW MyViewASSELECT *FROM MyTable T JOIN MyPermissions P ON P.RecordID = T.RecordID AND P.UserID = USER_NAME() You could probably also have tables/other resources OWNED by the user that logs on - but I reckon that would be anightmare to maintain.That would use KRISTEN.MyTable and GIOVI.MyTable instead of just plain dbo.MyTableKristen |
|
|
|
giovi2002
Starting Member
46 Posts |
Posted - 2005-08-01 : 17:18:20
|
| Ok Kristen these were some helpful hints!The administrator has asked me to put my permission in a separate table so they don't have to administer accounts (i was quite stunned when i heard it but many apps seem to use it). He advised me to tackle the problem in the application interface. Probably one general function in my access adp project which needs to be recalled for each update on a dataset. Extra work for me as it's the first time i'm going to use permission tables. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 00:32:40
|
| Our application has all its permissions stuff built in to our own tables. We just have one "application" login to the database, which all users use. I agree its much easier on maintenance than Server logins - a designated "SysAdmin" of our application can set up user accounts, and permissions, within our application without interfering with the server level stuff.However, anyone who is allowed direct database access (because they use some reportwriter or whatever) has their own personal login, and SQL Server permissions for the tables etc. that they are allowed to query.Kristen |
|
|
|
giovi2002
Starting Member
46 Posts |
Posted - 2005-08-02 : 03:36:58
|
quote:
Originally posted by Kristen
Our application has all its permissions stuff built in to our own tables. We just have one "application" login to the database, which all users use. I agree its much easier on maintenance than Server logins - a designated "SysAdmin" of our application can set up user accounts, and permissions, within our application without interfering with the server level stuff.
I don't agree on that one. It looks easier in maintenance to have all your users in sql server. Certainly when you're using reporting tools. Besides of that you will always have more datasets in your application which need application permission code than tables in your database. At first view I'm quite sad I have to rebuild permissions in my database |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 04:21:42
|
| Sorry, I should have mentioned the caveats relevant to our application.Our applications are IIS / ASP, so individual annonymous users having SQL logins is a bit of a stretch!Also, apart from any "reporting" users who may have ad-hoc access for reporting, all our database interaction goes through SProcs - so the "application login" has no rights directly to any table.Kristen |
|
|
|
giovi2002
Starting Member
46 Posts |
Posted - 2005-08-05 : 22:34:16
|
quote:
Originally posted by Kristen
Sorry, I should have mentioned the caveats relevant to our application.Our applications are IIS / ASP, so individual annonymous users having SQL logins is a bit of a stretch!
Yep in that case you're right |
|
|
|
|
|
|
|
|
record level permission