I posted on this a while back, but I want to revisit it. I am looking at the way I handle application security. I have created a set SQL tables and stored procedures, and I also have a C# class that work with this. This would most often be used for applications where the users are not SQL savvy....

Here's the data:

Table: Applications
AppKey      AppName           AppCode
----------- ----------------- -------
1           My Application    map


Table: Users
UserKey     AppKey      RoleKey     FirstName   LastName    UserName                  Password
----------- ----------- ----------- ----------- ----------- -------------------------- --------------------------
1           1           2           Admin       Admin       1/hsSDeiNwdfYSKiLAgRtQ==  1/hsSDeiNwdfYSKiLAgRtQ==



Table: Roles
RoleKey     AppKey      RoleName        RoleCode Description
----------- ----------- --------------- -------- ---------------------
1           1           User            user     User
2           1           Administrator   admin    System administrator


Table: Rights
RightKey    AppKey      RoleKey     UserKey     RightName      RightCode        AllowDeny   Category
----------- ----------- ----------- ----------- -------------- ---------------- ----------- ----------
1           1           NULL        NULL        Add User       sys_add_user     1           System
2           1           NULL        NULL        Edit User      sys_edit_user    1           System
3           1           NULL        NULL        Remove User    sys_remove_user  1           System
4           1           NULL        NULL        Lock User      sys_lock_user    1           System
5           1           2           NULL        Add User       sys_add_user     1           System
6           1           2           NULL        Edit User      sys_edit_user    1           System
7           1           2           NULL        Remove User    sys_remove_user  1           System
8           1           2           NULL        Lock User      sys_lock_user    1           System

Some problems:
1) With the exception of the UserName and Password, the data is plain text. There's nothing stopping someone from updating the rights table's AllowDeny column to all 1's & they're in. Any issues with encrypting the data in the tables, assuming stored procs to decrypt?

2) Each table has a 'code' column. This is so that in your application you can use that right. So I can hard code into my app a check for 'sys_edit_user' to see of the user has rights to edit users. Not sure if I like this, but how else would you do it?


I don't want to create a maitenance nightmare, but I'm open to suggestions on this