CODEWITHSUNDEEP
c#sqlvb.net
Peter
Peter

Reputation: 803

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

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.

Upvotes: 3

Views: 2170

Answers (3)

Marco Forberg
Marco Forberg

Reputation: 2644

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 ;)

Upvotes: 2

Matthieu
Matthieu

Reputation: 4620

You should take advantage of the Membership module of the .Net Framework. It is a standard way to manage users and roles in web applications, and there are even bridges to manage users in desktop applications with this module.

As for ASP.NET MVC, you can find projects that implements standard screens and functionnalities on top of the membership module like the MembershipStarterKit (available on Nuget) .

Don't reinvent the wheel if you can avoid it.

Upvotes: 1

GJKH
GJKH

Reputation: 1725

You cold roll your own custom membership provider. Although that might be overkill for your requirements.

What you've got seems fine so far, just create a user class and store this when the user logs in with their appropriate privileges, then create a couple of functions like isAllowedUpdate(roleID) as Boolean, isAllowedCreate(roleID) as Boolean etc.

I've done this a couple of times for simple web apps that require privileges. I usually just have a role and user table though with very specific roles created - it depends how granular you want the privileges to be.

Upvotes: 0

Related Questions