Do I just need to make a field for account type in my user account table? In a technical way, if(account type = admin) then the account would log as admin; otherwise, a user with less privileges.

Edited 5 Years Ago by AngelicOne: n/a

Without know the rest I can't say for sure, but usually the way to go is to have a "Permissions" table.
With the permissions table you can add a row for each user which the permissions set that you require. So you could add a field for "Read Access", "Write Access", "Administrator", "Can Control Toaster" etc.

Then if a normal user logs in, a different main menu would show to the user with few privileges.

It's however you want. If you aren't being told what is available and you have free reign to do what you want, then you can go as far as you want to with it. You can have a field for "IsAdmin" in your user table, or you can have a permissions table, you can do anything.

I would always opt for a permissions table. It's just more expandable.

Could you please site an example for a permission table to be more expandable than a field "isAdmin"

Well you'd have a table with a user id, this would link it to your user table. You would then create fields for each permission you want to give. When you read that information back into the program, you decide what you want each permission field to represent in your program.

I can't really make it any more simple than that.

What if the permission for all non-admin are the same? Do I still need to make a permission table?

Short Answer:
Yes, you need a row for every user account on your system.

Long Answer:
Not necessarily. If you choose, you can assign a standard set of permissions to each user by using a permissions link table.
This link table contains the user id and a permissions id. You can then assign a single permission set to multiple users.

eg.
User Table = User ID, Username, Password
Permission Table = Permission ID, IsAdmin, CanMakeCoffee, CanMakeToast
UserPermission Table = UserPermission ID, User ID, Permission ID

If I make a permission set of:
Permission ID = 1, IsAdmin = 0, CanMakeCoffee = 1, CanMakeToast = 0
Permission ID = 2, IsAdmin = 1, CanMakeCoffee = 1, CanMakeToast = 1
Permission ID = 3, IsAdmin = 0, CanMakeCoffee = 0, CanMakeToast = 0

I can use those with my following users:
User ID = 1, Username = Tom, Password = 1234
User ID = 2, Username = Richard, Password = 5678
User ID = 3, Username = Harry, Password = 9012
User ID = 4, Username = Admin, Password = ahAHJhjkDsAh^&7

So I make a UserPermission table with the following rows:
UserPermission ID = 1, User ID = 1, Permission ID = 1
UserPermission ID = 2, User ID = 2, Permission ID = 3
UserPermission ID = 3, User ID = 3, Permission ID = 1
UserPermission ID = 4, User ID = 4, Permission ID = 2


This means:
Tom can Make Coffee, but cannot make toast and is not an admin.
Richard is not an admin, cannot make coffee and cannot make toast.
Harry is not an admin, cannot make toast but can make coffee.
Admin is an admin and can make both coffee and toast.

Hope this helps

Edited 5 Years Ago by Ketsuekiame: n/a

I understand it very well. In a more technical way, how could a form determine what objects should be disabled? Making copies of a form, i think, is a bad approach.

I have done this

create table tblUser (userID int identity(1,1) primary key, lastName nvarchar(50), firstName nvarchar(50), position nvarchar(50), gender char(1), "address" nvarchar(100), nationality nvarchar(100), country nvarchar(100), contactNumber nvarchar(50), username nvarchar(20), "password" nvarchar(20))
create table tblPermission (permissionID int identity(1,1) primary key, isAdmin char(1), canAddVisitor char(1), canEditVisitor char(1), canDeleteVisitor char(1), canSearchVisitor char(1), canAddUserAccount char(1), canEditUserAccount char(1), canDeleteUserAccount char(1), canSearchUserAccount char(1))
create table tblUserPermission (userPermissionID int i,dentity(1,1), userID int, permissionID int)

Edited 5 Years Ago by AngelicOne: n/a

This is what you have to decide for yourself. Based on your permission set, you should enable or disable certain controls.

So maybe using my example, your form has a CanMakeCoffee(Boolean isAllowed) Inside are all the controls represented for coffee making. You would enable/disable all the controls in this section based on the isAllowed variable.

This isn't the best way but it is quicker.

The correct way, would be to decide which controls are represented by each permission. So for example, making toast and coffee require electricity. So if you have a "Turn on power" button, this is required for both coffee and toast makers. If both permissions are disabled, you disable the button (or hide it, whichever). If one OR the other is allowed, then you enable (or show) the button.

Edited 5 Years Ago by Ketsuekiame: n/a

Comments
good and clear explanation

Just for confirmation, when a linker table is present, do I still need to call a field as foreign key?

It depends on what you're using. For "proper" database set-up, each reference to another table should have a foreign key assigned to it.

Am I the one who will provide the data for tblPermission or the application when a user has been created?

When you create the user, you should assign the user permissions. But the great thing about this is, you can change the permissions at any point by altering the database. The next time they log in, the new permissions will be installed. =)

This article has been dead for over six months. Start a new discussion instead.