Hi,

A few questions I'm afraid:

The purpose of the UserRights table is not clear to me. Do you intend that users should be given rights without being assigned to any roles?

Why are the password details in the UserRoles table rather than the Users table?

How do the RoleRights.AccessLevel and Rights.AllowDeny fit together?

Regards,
Viv

>My design will allow for multiple roles per user. Here a pic of my preliminary design:
>
>http://www.filefactory.com/file/a0f2072/n/design_JPG
>
>I welcome your thoughts.
>
>
>
>
>>Hi,
>>I don't think there is a standard. The simplest pattern, if it will fit for your application, is to only allow a user to be in *one* role/group.
>>If a user can be in more than one group then more logic is needed to determine what permission should apply (usually based on the role with the most freedom)
>>
>>How you assign access levels to roles will also depend on your application. I currently just have two settings for UIItems: Hidden (so they don't appear on the form) or Disabled/ReadOnly.
>>
>>Do you envisage a setup where the end-user (if acting as an 'Administrator') will be able to create and configure roles to suit their own circumstances or will you create fixed roles and only allow them to add and assign users?
>>
>>FWIW, the structure I'm working with is:

Users  <-- UserRoles --> Roles <-- Permissions --> Items

>>
>>UserRoles is just a M-M link table. Permissions ditto but with an extra field specifying the permission level.
>>
>>Regards,
>>Viv
>>
>>>Ok, I see.
>>>
>>>From what I can see there are various design patterns for app security. Any thoughts on what the standard is?
>>>
>>>
>>>
>>>
>>>>Hi,
>>>>
>>>>In my case the 'normal' db will be MS SqlServer. But I've kept all the hash/compare logic out of the data layer and SQLServer itself so nothing would need to be changed if a different back end was used.
>>>>HTH,
>>>>Viv
>>>>
>>>>>>>(a) To handle the encryption in a tier that will make it back-end agnostic.
>>>>>Not sure what you mean by this. Please explain.
>>>>>
>>>>>
>>>>>
>>>>>>>I'm putting together some table to manage users, roles, and rights.
>>>>>>>
>>>>>>>Anyone see any problems with encrypting all the data in all three tables?
>>>>>>
>>>>>>I'm working on a similar thing at the moment. Your needs are probably different but I decided:
>>>>>>(a) To handle the encryption in a tier that will make it back-end agnostic.
>>>>>>(b) To only secure the password - and then by hash value rather than encryption.
>>>>>>
>>>>>>FWIW, here's the class I use for hashing/comparing.

    static public class HashFunctions
>>>>>>    {
>>>>>>        public static Byte[] GetHashValue(string s)
>>>>>>        {
>>>>>>            byte[] source = ASCIIEncoding.ASCII.GetBytes(s);
>>>>>>            return  new MD5CryptoServiceProvider().ComputeHash(source);
>>>>>>        }
>>>>>>
>>>>>>        public static bool CompareHash(byte[] first, byte[] second)
>>>>>>        {
>>>>>>            bool equal = false;
>>>>>>            if (first.Length == second.Length)
>>>>>>            {
>>>>>>                int i=0;
>>>>>>                while (i < first.Length && (first[i] == second[i]))
>>>>>>                    i++;
>>>>>>                equal = i == first.Length;
>>>>>>            }
>>>>>>            return equal;
>>>>>>        }
>>>>>>
>>>>>>        public static bool CompareHash(byte[] b,string s)
>>>>>>        {
>>>>>>            return CompareHash(b, GetHashValue(s));
>>>>>>        }
>>>>>>    }

The password is stored as a byte array (varbinary() in MSSQL)