How do I manage user roles and priviledges using C# / VB (web form or MVC) with ms sql server?

Question

I'm developing and application with ms sql server and C# in need of user privileges and roles. Currently, I created tables like this.

 'privileges_table

  PrivilegeID | PrivilegeName
       1      |  Create
       2      |  Update
       3      |  Delete
       4      |  View

  Roles_Table

  RolesID     | RolesName  | PrivilegeID  | RolesGroup
     1           Admin          1         |   1
     2           Admin          2         |   1
     3           Admin          3         |   1
     4           Secretary      2         |   4
     5           Remote User    4         |   5

  User_Table

  UserID   |  UserName   | UserPass  | RoleGroup
    1      |  Wale       | m%^#@     |     1
    2      |  Jane       | k*&%$#    |     5

  '

what do I do to manage this in a better manner to reduce redundancies and still avoid too much coding with my c# / VB ? All helps is appreciated.

Answer 1

you got some redundancy in your Roles table

privilege table is okay (although this is most commonly called permission)

Roles:

ID | Name
---+----------
1  | Admin
2  | Secretary
3  | Remote User

Since a single role can have multiple prvileges and a single permission can be assigned to multiple roles we have a n:m (or Many-to-Many) relation which is resolved by a join table

RolePermissionTable

ID | RoleID | PrivilegeID
---+--------+-------------
1  | 1      | 1
2  | 1      | 2
3  | 1      | 3
4  | 2      | 2
5  | 3      | 4

One more thing concerning naming style: choose for yourself if your name your table with singular or plural names - not one in plural the other in singular the next... well you get the picture i hope ;)