July 1, 2008 at 5:58 pm
Hello All,
For many years I have utilise a very simple menthod of authentication for the smaller web sites I've been developing; for example, each user record would have several columns with single tinyint flags determining the users access permissions or each user linked to another 'status' table with tinyint flags determining a group of users access permissions.
I'm presently wanting to develop a couple of newer sites which have a need for a more granulated type of authentication based on roles or group memeberships with the possibility of user-based overrides.
Now, before you all say: use the security built into SQL server to handle it; I cannot, it doesn't exactly fit what I'm after and I don't want 'users' to be able to modify SQL security.
So; without further a-do, here is what I'm trying to achieve given hypothetically:
A website is created which has both "Users" and "Administrators". Each have different classes of "Users" and "Administrators". eg:
User class 1 - Can view all data but not modify it
User class 2 - Can view all data but edit only a selected portion of it
User class 3 - Can perform all data functions except delete
User class n....
Administrator class 1 - Can only handle service requests and have read-only access to user data.
Admin class 2 - Can do all of the above but are read-write
Admin class 3 - Are system administrators and can perform all functions.
Admin class n....
Ideally what I would like to do is have controls in both (users and admins) which allow them to create new "classes" on the fly and customize the permissions of either. I would like a user or admin to be able to be a member of multiple classes.
So far I have come up with two ways of handling this situation:
Solution 1)
- Have a users table whcih has several varchar(50) columns appended to it titled as "AccessAreax" where x is a number starting at 0. Each access area would be values as the "Class" overrider for the individual user.
- Then have a 'classes' table which also has several varchar(50) columns titled the same. Each record in the classes table would be a determining class.
- Then have a 'memberships' table which links users to one or many classes.
- A special stored procedure (here on as "acc-sp") would then be able to be called from other business logic sp's ("bl-sp") specifying the user's id, the area of the calling "bl-sp", and the index of the calling "bl-sp". The "area" and "index" would have to be manually coded into the "bl-sp" by the developer with special attention being paid to ensure that the same "area" and "index" isn't used elsewhere. The "acc-sp" would then check the users individual overrides and class memberships to determine if they are permitted to use it.
Pros:
This solution allows for permissions (in the 'area' strings) to be modified on the fly and for users to be able to join groups quickly and easily.
Cons:
This solutions does not easily allow for new "areas" to be created and also relies on the SQL developer to ensure they don't use the same permission "area" and "index" twice.
Solution 2)
Using the same table structure as the one above, but instead of having "areas" and "indexes" in varchar(50) columns, utilizing some other table strucuture to link the "acc-sp" to the classes to allow for a more flexible development and not relying on the developer to get it right (eliminating possible mistakes).
I obviously haven't thought as much on the second solution 🙂
So basically what I'm looking for are some comments on how I could structure the tables to achieve my goals?
Thanks in advance to any assistance you can give.
July 1, 2008 at 6:46 pm
Hi,
You can Design DB like tblUser(UserID,UserName,Password),tblRole(RoleID,RoleName),tblGroup(GroupId,GroupName), tblPrivilege(PrivlID,PrivName) and finally tblMenuItem(MenuID,Menuname,Mentype).
Make Mapping tables Between user and role tblUserole ,tblRoleGoup , tblGroupPrivileges and tblmenupriviliges like that then it will solve your problem.
Gupta
July 1, 2008 at 11:54 pm
Hello,
Thanks for your quick response. Could I trouble you to expand a little more on your solution? I cannot clearly see the way in which you've structured the relationships to accomplish the goals?
Thanks
Steve.
July 2, 2008 at 5:34 am
First, I think we are missing some of the requirements. One of the problems with a security model that has users in multiple classes or groups is what to do with overlap. For instance, if a user is in class 1 and class 2 in your example, can they edit? Class 1 indicates they cannot and class 2 indicates they can. You will have to make a decision - do you take the "least" or "most" restrictive of the permissions groups that pertain in a particular situation.
Next, I like to break security into three categories:
Functional - this is something that is an action. Can the user delete? Can the user create users?
Access - what can the user see? This would usually be "entity" or "field" level. I think you want to have grandular control at a field level, but keep in mind that sometimes context can impact security.
Data - which records can the user see? This is typically the most difficult to handle. A user can see order information only for their accounts.
Now that I have gotten through that, the security models for these three groups do not typically carry over to the other groups. You may want to consider having the security for these stored a little differently.
July 2, 2008 at 5:30 pm
Hello Michael
Thank you very much for your reply; those were exactly the types of questions and comments that I was hoping for.
I apologize for some parts of my initial post being contradicting but I had that many thoughts about it running through my mind at the time it was hard to compile logicially.
With regards to the multiple permissions (being a member of class1 and class2) to determine precedence I would use a MAX() function on the access codes. I'm intending to use these flags.
0 = Default permission (denied access)
1 = Access permitted
2 = Access denied (* see explanation below)
3 = Access permitted (this status is only available on the user's individual override record)
4 = Access denied (this status is only available on the user's individual override record)
* This status would be used in a case where if you are a member of a certain class, and a member of a possibly conflicting or partly conflicting class, this status can be used to nullify permissions of another class.
I also might not have explained too well how the classes define what the user can do.
The 'acc-sp' which checks the users permissions would return a status of 'permitted' or 'denied'. This 'acc-sp' would be placed usually at the top of a stored procedure and could also be placed elsewhere within the stored procedure. For example:
CREATE PROCEDURE dbo.Test (@datavariables int)
AS
-- CHECK ACCESS PERMISSIONS
EXEC dbo.CheckPermissions @Result = @Result OUTPUT, @ID = @ID, @Area = 1, @AreaIndex = 13
IF (@Result <> 1)
BEGIN
-- THROW ERROR
END
-- more business logic code
-- CHECK ACCESS PERMISSION FOR ANOTHER PART OF THIS PROC
EXEC dbo.CheckPermissions @Result = @Result OUTPUT, @ID = @ID, @Area = 1, @AreaIndex = 14
IF (@Result <> 1)
BEGIN
-- THROW ERROR
END
-- more business logic code
-- end sp
GO
In the example above I've used "Area" and "AreaIndex" to determine which access permission is required for that authentication. The thing I'm trying to do I guess is make this more fool-proof, ie: is there a way that I can ensure that the SQL developer hasn't used the same area and index elsewhere so as not to accidentially give someone permission to something they're not supposed to?
Further to this, in the table structure I gave in my first post (solution 1) developers would manually have to add a new 'area' column in the classes table each time they wanted to create a new access area. I'm trying to find a solution around this.
In your reply Michael, you focused a little on who can read only and who can write, this isn't really the focus that I was after, sorry if it sounded like it. The idea I'm after with the classes is that one class can be set up as "Customer Service" where they can read/write everything for customer service (customer service "area"), another class is set up as "Payment Admin" again where they can read/write everything to do with payments (payment "area"), but then classes can also be created to be a comination of the two areas or more.
I hope that explains this a little better.
Thanks so much for your input.
July 3, 2008 at 5:27 am
Ok, so your current planned model basically allows permissions to be set to basically permit or deny the execution of a stored procedure. So, rather than an AreaIndex, why wouldn't you use the procedure name? Area/AreaIndex looks suspiciously like it would fit into Schema/ObjectName pretty well - you are not going to have millions of these, so using a SysName column in your security table would work fine. It could really ease confusion.
I see a couple of possible issues. The first is that your procedure is going to error when the user cannot access it. This may be ok, but you will have to program to deal with the errors. This is going to make using bound controls (if you ever plan to) a bit complicated. That moy not be a problem for you, but it is worth thinking about.
The other issue is you are going to have to completely restrict access to your database to only accessing it through your stored procedures. This may be ok for your application, but it can add complication for reporting tools and other applications that get put together later. Lots of people design and build security into applications with the idea that the application will be the only way users get to data, but that simply is rarely the case. Users will want reports and your support staff will need access through Management Studio, etc. Although these may not be problems you need to solve, they should always be considered and planned for - if nothing else just to have a response ready for when you have to tell someone they cannot have the report they want.
As an alternative that solves this issue, using views can allow you to join to your security table and have views return no data if the user does not have permission to view that data. This can really help with the use of third party tools later. Of course, this can create another set of issues, but I'm just trying to get you thinking.
July 3, 2008 at 2:56 pm
This may seem to be a little brash on my part or my natual denseness, and hope you will pardon me for asking but have you really examined the use of schemas and roles available in SQL 2005 to insure yourself that these will not meet your requirements?
July 3, 2008 at 7:21 pm
Hello Michael,
Thank you again for your reply.
You have got it basically correct. I'm not actually using the authentication to deny the EXEC ability of the sp, but rather throwing a specific type of response (which to the application reading it) would appear as an error and be handled appropriately. This way, if a permission allows one portion of an SP but not a subsequent portion this can be handled successfully. This is the main reason why sql server's internal security is not really an option; but further to this some of the applications are on shared servers which only allow me to create a limited number of sql users.
My applications are designed to be operated specificlly with the web-interface created for them, one sp is designed to handle all the data requirements of a specific page/action. This way not only keeps the data neatly contained but also allows new developers coming in a much easier integration into the design team.
If a third-party application was to be added later, these could be added and would not be affected by the authentication restrictions in place in the sp's.
Since I posted this question I've been experimenting with a test database to try and nut out a good solution. So far I've come up with something which does include the sysobjects/object name reference.
Here is the structure so far:
dbo.Users (UserID, UserName....)
dbo.UsersMemberships (UserID, GroupID)
dbo.Groups (GroupID, GroupName...)
dbo.GroupsPermissions (GroupID, AreaID, IndexID, PermissionFlag)
dbo.Areas (AreaID, AreaName)
dbo.AreasIndexes (AreaID, IndexID, IndexName, ObjectID)
dbo.UsersMenberships (links to) dbo.Users & dbo.Groups
dbo.GroupsPermissions (links to) dbo.Groups & dbo.AreasIndexes
dbo.AreasIndexes (links to) dbo.Areas & dbo.SysObjects
Using the above, it is 'still' up to the sql developer to make sure they are using the correct area and index but this can be somewhat controlled using a simple check to ensure the entry for ObjectID in "AreasIndexes" matches that of the calling SP; if not throw a hard-error that the sql-dev *should* pick up when debugging their development. New groups can be created on the fly by simply adding an entry into the dbo.Groups table and then creating the required records in the dbo.GroupsPermissions table; if a record doesn't exist then it's simply denied. An extra table dbo.UsersOverrides could be implemented to provide overriding of certain groups if necessary.
So I guess this 'so far' answeres my question but I'm still interested if you have any comments which could further enhance this.
Thanks again.
Steve.
July 7, 2008 at 5:41 am
I'll start with the simple one. I would not use ID's from Sysindexes for figuring out objects. Use the object schema / name. If you use object ID's, you will not be able to add an object in a development environment and then script the same thing in a production environment. You would have to rely on the ID's ending up the same and this can become a real problem. Schema / Object is unique and not likely to change, so keep things simple and avoid object id's.
As far as the plan for returning all of the data for an entire web page in a single procedure and handling permissions by not returning parts of the data, I think you are creating something that is going to be pretty complicated to maintain and ultimately very difficult to deal with for modifications. First, you probably have procedures returning multiple recordsets and you are dealing with those recordsets in your application. That can become pretty ugly to deal with, especially if you are relying on the order of the recordsets, the fields they return, and error messaging to figure out which recordset is which. What if you have two recordsets being returned that have the same fields? How do you tell which is which if one is restricted by your security?
Having the web page call multiple procedures will make this easier to manage and will also make your code much easier to manage. You could speed thngs up later by making AJAX calls, but with a single procedure, you would never be able to make this happen. It will also be more difficult to add something onto the web page or re-use part of the code. in your model, if you want to display an address on your contact page and an address on your company page, you could not re-use the procedure - or better yet create a user control and re-use the entire piece of code.
What you are suggesting will probably be do-able, and I am sure you can make it work. It is just outside of the traditional box and I think you are doing development that does not need this level of innovation. I would step back and look for an approach that has been more commonly used and proven. As architects, we have to take a step back from our plans and make sure they do not conflict with the technology. I think you may be getting into that area. When you find yourself having to build an object security model because the one that comes with your database platform is not grandular enough, it may simply be because you need to package your code in a way that makes it fit.
In your case, I would not drill a round hole, I would tend to shave the corners off of the peg just a bit. It will probably be less work and ultimately more stable.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply