SQL - VB.NET Store Application Permissions

[wakawaka]

Hello,

I looking into adding permissions into one of my programs. I am looking for the best way to store these in a SQL database. My inital thought is columns define the permissions (EX: AddUsers(BIT)) but that kind of limits the database if I add more functionality and it needs a setting to define the permissions for access to it.

What would be a good way to go about the database design and incorporate it into the database?

[sapator]

That does not help much.
You have to explain further.
There is no problem in adding additional permission column in the database if you fix it properly.
As for a setting to define the permission.What exactly do you mean?If you mean a unique id for each program running then it will be complicated in winforms and very easy to incorporate in asp.net.
In winforms you have to either create a login system that will be used parallel with the database for identification or store certain characteristics of the pc used (such as mac address or hd id).In asp.net it's very easy to use a login system and add the user to permission groups.

[wakawaka]

Sorry, I didn't mean for it to be vague.

I don't have much experience with login systems. I am imagining how one would work really. My thoughts are I have a table with the columns: RecordID, UserID, AddUsers, DeleteUsers, EditUsers, etc... When the user logs in, it loads their permissions set, or it queries the database as it needs the permission check.

EX:
Table UserPermissions
RecordID (Identity)
UserID (Numeric(15,0))
AddUser (Bit)
EditUser (Bit)
DeleteUsers (Bit)
etc...

Is this a natural way to do it? Does this make it more difficult to add additional permissions if I add new functionality to a program? This is opposed to a one table having the user permission set and another table that defines them.

EX:
Table UserPermissions
RecordID (Identity)
UserID (Numeric(15,0))
PermissionID (Int)

Table Permissions_Ref
PermissionID (Identity)
Description (varChar(100))

If they have a entry in the UserPermissions, it means they have the access to that item.


I have seen someone using a common delimited varChar field, but it seems unnatural to me to use it in that way.

[sapator]

I can't tell you what is correct, it depends on your specs but you can create and have a look at the microsoft asp.net membership tables, they are defining roles but it is somewhat similar.Tables are split there.
Common delimited varChar field?You mean comma delimited?Avoid like the plague!
A question i must ask again is:Have you determined how you will create your login system?Is this on Active Directory?Better be sure on how your login system will work and then worry about permissions.

[wakawaka]

This app is an asp.net. I want it to be independant of AD.

[sapator]

Ah, you should have said that from the beginning.
You can assign roles if you use the membership database and handle your users.
I don't think permissions will "play" as the standard winforms permissions you have in mind.
Better be specific of what these permissions is used for and write in the asp.net forum.
If you want p.e. certain users to have access to a write folder you can't assign each user to the folder you have to assign all asp.net users to the folder and then manipulate with code on the server.
If you want certain users to be able to manipulate other users then you will assign roles to users (p.e. admin) and then you will check if a user is in that role so he can modify another.If that is what you want then go 100% with asp membership database.
So better just to asp.net forum