Need help with bitmasking permissions (1 Viewer)

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
I'm trying to save the permission flags of a lot of controls as bits in a byte and use bitwise operators to show/hide or enable/disable these controls for different users.

A single long integer can manage 32 controls. What if I need to manage more controls, lets say 50 controls?
What options do I have?

Thanks for any kind of advice.


PS:At present, I have a table that holds each control and a yes/no for it:
ID,ControlName,UserID,Permission

In each form's on open event, I open a query and set the permission according to permission flag.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:33
Joined
Oct 29, 2018
Messages
21,358
Hi. If you need to manage more than 32 controls, you may have to switch to 64-bit Access and use Large Number data type.
 

Cronk

Registered User.
Local time
Tomorrow, 09:33
Joined
Jul 4, 2013
Messages
2,770
Or use 2 or more 32 bit Long integers.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:33
Joined
Sep 21, 2011
Messages
14,048
Tera,
Aren't the controls going to be grouped in some way?
 

isladogs

MVP / VIP
Local time
Today, 22:33
Joined
Jan 14, 2017
Messages
18,186
Hi. If you need to manage more than 32 controls, you may have to switch to 64-bit Access and use Large Number data type.

For info, the large number (bigint) datatype is available in both 32-bit and 64-bit Access providing you have A2016 (with updates), A2019 or A365.
Having said that, the double number datatype would also work.
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
Hi. If you need to manage more than 32 controls, you may have to switch to 64-bit Access and use Large Number data type.

Thanks. I'll check it to see how many options can I handle.
I actually have to manage 155 bits for different controls in 64 forms.
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
Or use 2 or more 32 bit Long integers.

It will be my last option because it makes it hard pick a bit.
Let's say I set 4 long integer to control the objects on 64 forms.
I have to write a long SELECT CASE to pick which one of these long integers should be chosen to control the objects on a form when it opens.
 
Last edited:

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
Tera,
Aren't the controls going to be grouped in some way?

Unfortunately I wasn't able to group them because of the wide range of user levels. For example controlX should be accessible for userA in shop floor, but locked for userB. Although they are working side by side. The boss needs a solution to set the options per individuals and objects on the forms.
At present I'm doing this with a lot of yes/no options in a table that will be read in on-open action of forms.

I was hopping to set a tempvar when a user logs into the application and control hid level of accessing the data with it.

Thanks for the suggestion.
 
Last edited:

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
For info, the large number (bigint) datatype is available in both 32-bit and 64-bit Access providing you have A2016 (with updates), A2019 or A365.
Having said that, the double number datatype would also work.

Thanks for the info. Since we use only A2016 & A365, I think I'll be safe to use bigint.
I also look into double data type. I wonder why I didn't think of it at all.
Sometimes I think if I live another 100 years, still my brain can't hold half of your knowledge in Access.
Thanks again.
 

isladogs

MVP / VIP
Local time
Today, 22:33
Joined
Jan 14, 2017
Messages
18,186
I wish my brain still held all it did a couple of years ago ... ;)
But its often easier to suggest something when you're looking at it from outside
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:33
Joined
Feb 28, 2001
Messages
27,001
My question is, why do you think you need bit masking? Oh, I know you can do some bit twiddling in Access, but since it is not really a "native" operation, I wonder why you think you need that much compression? I have some ideas for you to consider as better ways than binary bit-twiddling.

First, if you have an SQL Server for the Back End then there are binary (bit oriented) data modes that do this automatically and that handle a Boolean bit field. If and only if that applies, you can just look up the details of the Boolean data type.

For native Access, however, bit twiddling is far more cumbersome. I recommend that you avoid it in favor of some other alternatives...

How many different users are we talking about and how much else do you keep in the user info table? Because there is always the idea that you could store 155 flags as the digit 0 or the digit 1 (and you have 8 more digits other than that, plus the rest of the alphabet) using a 155-character STRING and the MID(string, start, length) function to insert or extract a specific flag. Obviously, the length field would be 1 so you could have one character for each control.

The correct way (with respect to normalization concepts) to do that is to have a table of flags for which the user ID is one key and the flag ID (or control ID) is the other, then record TRUE or FALSE as appropriate. I actually don't think this is right either, though.

If you have 155 controls, I'm going to bet you don't have that many users. So maybe you should consider what roles each user plays and see if maybe you have no more than four or five roles. Then you can assign a SELECT statement to enable or disable controls based on role selection rather than user selection. How many UNIQUE control combinations do you anticipate? I would be totally surprised to find that you had more than a dozen roles, and at least slightly surprised to find more than half a dozen.

I cannot tell you that you mustn't do what you asked, but I can suggest that by using a properly normalized flag table that is a child of a user table record, you can minimize the handling complexity by quite a bit. By doing a role-based approach rather than a user-based approach, you can also cut back on the complexity of what you are doing.

If you need to ask more about this, ask away. Many of us use techniques like this and can advise you.

By the way, what I did was to presume that ALL controls were enabled, but then based on user roles, go back and turn off or otherwise alter the states of controls that the user shouldn't touch. For a given form, you could do that in the Form_Load routine because the user role wouldn't change for the duration of that form being open.
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
Doc I sincerely appreciate your time for this extensive reply.

My question is, why do you think you need bit masking? Oh, I know you can do some bit twiddling in Access, but since it is not really a "native" operation, I wonder why you think you need that much compression?
With my limited knowledge of Access, I simply thought it may be faster. As I explained earlier this is what I'm doing now;

tblUserRights:
ID : int (primary Key)
formName_FK : int
User_FK : int
ControlName : varChar(50)
permission: bit

On-Open event of each form opens a query from this table filtered by userID and formID, then enables/disables different controls according to permission field.

I was thinking it may be faster to set a long datatype tempvar on user login that holds the permissions as bits, then on every on-open use this tempvar instead of using a query.


First, if you have an SQL Server for the Back End then there are binary (bit oriented) data modes that do this automatically and that handle a Boolean bit field. If and only if that applies, you can just look up the details of the Boolean data type.
Yes, BE is a SQL Server that is running on a server machine for this purpose. As I explained above I'm using bit datatype at present. But I simply thought bit masking is faster than a series of Dlookups or opening a query. I maybe wrong though.


For native Access, however, bit twiddling is far more cumbersome. I recommend that you avoid it in favor of some other alternatives...
Any alternative solution is much appreciated as long as it's faster than my current approach (query)


The correct way (with respect to normalization concepts) to do that is to have a table of flags for which the user ID is one key and the flag ID (or control ID) is the other, then record TRUE or FALSE as appropriate. I actually don't think this is right either, though.
Is it different with what I'm doing now? And why you don't think this is right either?


If you have 155 controls, I'm going to bet you don't have that many users. So maybe you should consider what roles each user plays and see if maybe you have no more than four or five roles. Then you can assign a SELECT statement to enable or disable controls based on role selection rather than user selection. How many UNIQUE control combinations do you anticipate? I would be totally surprised to find that you had more than a dozen roles, and at least slightly surprised to find more than half a dozen.
We are working hard to add users to groups and as you explained control the previliges per groups. We are talking about 20 users and since each one is responsible for a specific job, we may end up to 17 or 18 roles (groups) that makes no much difference.
This is a database to register all incoming orders to a company, controlling all the purchased parts for manufacturing the orders, manufactruing process and steps for each order, Delivery situation of orders and several more.


How many different users are we talking about and how much else do you keep in the user info table? Because there is always the idea that you could store 155 flags as the digit 0 or the digit 1 (and you have 8 more digits other than that, plus the rest of the alphabet) using a 155-character STRING and the MID(string, start, length) function to insert or extract a specific flag. Obviously, the length field would be 1 so you could have one character for each control.
Are you suggesting to save a set of 155 character of ones and zeros, each character specified to a control name and parse the string to get the permission? something like 1100101101010101 and save it as a string?
By the way we are talking about 20 users (at present). The user tables holds ID,Name,MailAddress,Encrypted password and several other fields.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:33
Joined
Feb 28, 2001
Messages
27,001
I have to selectively answer this.

Any alternative solution is much appreciated as long as it's faster than my current approach (query)

Bit twiddling takes more steps than directly accessing something, particularly since you have more than 32 of the things you need to twiddle. First you have to find the container field then you have to identify the correct masking position then you have to perform the Boolean operation to get the result. Or, you could just directly access it using the native storage element of the computer's addressing hardware - the byte. That is why I am saying the speed/space trade-off here favors using extra space to make for extra speed.

Are you suggesting to save a set of 155 character of ones and zeros, each character specified to a control name and parse the string to get the permission? something like 1100101101010101 and save it as a string?

Similar to that, anyway. You would have either a set of declared constants or a lookup table so that you might have tuples <"CheckBoxMarried", 13>, <"CheckBoxMale", 2>, etc. etc. so that you can run through a memory structure and find the position of the digit for that field. Parsing would strictly be: Find the name of the control in the table, get the number, decide what to do based on Mid$(TheString, ThePosition, 1) returning 0 or 1. And of course, if you have this lookup table then you can have a form that uses the same information so that you can decide what everyone can or can't see and can set the records up initially.

We are working hard to add users to groups and as you explained control the previliges per groups. We are talking about 20 users and since each one is responsible for a specific job, we may end up to 17 or 18 roles (groups) that makes no much difference.

Forgive me if this comes across a bit harsh, but you need to work smarter, not harder. Look for commonalities. Find those things that EVERYONE sees and don't store flags for those cases. Only store flags for the things that are variable.

It is not mathematically possible to have that much variation in 20 users unless the roles are such that your 20 people only see an average of 7.75 controls out of 155. If they see more than that number then there MUST be some overlap in what they see. Identify the common, focus on controlling the differences. You can't possibly need flags for all 155 controls unless the roles are seriously constricted.

Is it different with what I'm doing now? And why you don't think this is right either?

It is different in that the correct way for Access is to have narrow but tall structures, whereas keeping a lot of flag data in a single record is thinking wide but short. Access efficiency comes about from allowing SQL to look at things vertically.

I don't think it is right based on my previous comments about looking for similarities and differences, and only controlling the differences. My earlier comments about "role" also play into this. I don't think you can possibly need 155 flags because I don't think there is that much non-overlap possible.

In essence, if everyone really is THAT separated in function, you might do better to just give them 20 different forms with just a couple of controls on each. Trying to manage a form with a myriad of possible control choices is nightmarish. You have to reduce the complexity and that is why I said "155 records per user, 1 for each control" is also not the right approach.

You are implementing a complex design - but from a mechanical standpoint, is that design the best for your problem?

One of the incredible strengths of this forum is not that we can answer technical questions. Even some of the schlumpy forums can do that much. We have enough people to give you ideas you hadn't thought about. I appreciate that you are new to this arena, so don't have a lot of experience from which to draw designs and ideas. But we do. Step away from the details and ask folks for approaches at the design level. THEN ask for guidelines on how to implement those designs.
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
Forgive me if this comes across a bit harsh, but you need to work smarter, not harder. Look for commonalities. Find those things that EVERYONE sees and don't store flags for those cases. Only store flags for the things that are variable.

It is not mathematically possible to have that much variation in 20 users unless the roles are such that your 20 people only see an average of 7.75 controls out of 155. If they see more than that number then there MUST be some overlap in what they see. Identify the common, focus on controlling the differences. You can't possibly need flags for all 155 controls unless the roles are seriously constricted.

In essence, if everyone really is THAT separated in function, you might do better to just give them 20 different forms with just a couple of controls on each. Trying to manage a form with a myriad of possible control choices is nightmarish.

I never think of any comment harsh or anything. I'm here to learn and will appreciate any advice.

Everyone is that separated, but needs to see everything. And being able to edit only a portion that belongs to himself.
I will try to explain the situation. Hope you or anybody else can show me if any other solution exists.

The database belongs to a manufacturing company. An order arrives and is registered in the database. The design team start working on it, creating necessary drawings, NC data, a map that guides the shop floor where to start, which steps is necessary, the sequence of manufacturing etc.
When it's ready, somebody orders the necessary materials/parts/tools etc, registering what has been ordered, when it's going to be delivered.
Later, the shop floor uses mechanical machines (laser cut, Tallet Punch, Lathe, Benders,...) to manufacture the order.
After all is done, there are people to handle delivering the manufactured part to the customer and set the delivery date, where it's been delivered and a lot more.

Everyone MUST see the orders, and at which step it is. Is the drawings ready? Is the material ordered? When the previous steps are going to be finished and when his own process will start. They view the drawings, they calculate the time their machines need to do the necessary process and register it in the database.
So a look at the situation of an order looks like a time table, a schedule book with all necessary data where, when, by whom and how. It gives an all-over image of an order.

That's why everyone must see everything but being able to edit his own part. I can't have simplified forms per users.

I will talk about your suggestions above with my colleagues to see how it fits us better.
Thanks again for your time and advice.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:33
Joined
Feb 28, 2001
Messages
27,001
You were worried about speed. Here is my take:

If everyone sees everything but not everyone edits everything, then a commonality is that EVERY CONTROL will be visible and populated. So the only thing you need to do is choose how to enable or disable controls. Look to this question: How many controls will need to be enabled? (Or disabled, depending on most common state.)

Now my viewpoint (known to sometimes be a little off-kilter) would be to have a table of users for which there is a UserID. You put stuff about the user in the user table - but do NOT put control info. If you choose "role" then the user role goes in this table. If you are going to make controls per-person then nothing else goes here.

You also have a table of control names and this table also has unique numeric IDs for the entries. You use this table to look up the control ID OR the control name depending on which way the query of the moment needs it. This does imply that both fields will be indexed. Which one is the PK? I would make the numeric side the PK but that's force of habit.

Now you have a table that contains UserID, ControlID, and perhaps nothing else, though this depends on whether you can use the idea that you have two groups of controls. Group 1 is always enabled for every user. It will probably be small. Group 2 is the set of controls that vary from user to user. ALL controls in group 1 start enabled and stay that way. All controls in group 2 start disabled but are subject to change. So what you do is your user/control table has tuples <UserID, ControlID> meaning that this user will be able to update this control. The catch is that you only have ControlID numbers from Group 2 because you never change Group 1 controls. OK, that makes it a little shorter.

But now here is where you can get really fast. The User/Control table is SPARSE. It only has entries for things you want enabled. By default everything else is DISABLED. And it only applies to group 2 controls. So you open a query for this user that reads the entries in the User/Control table and loops through it looking for records representing controls to be enabled. By your description, this list will be smaller than the list of controls to be DISABLED because you suggest that enabling a control is typically the less likely case. (At least that is what I infer.)

When do you do this? When you launch the form, the Form_Load event has all controls in it and you can also write code to identify the user. (That's a separate topic.) So you step through this recordset, enabling the controls based on the ControlID and the fact that you have a table that associates Control Names and Control IDs. Does that make sense?

By the way, if you ever decide that that you actually CAN group this into a few roles, then your User/Control table becomes a Role/Group table and you still get the same result. You enable controls based on the table that enumerates the things to be turned on. And it works this way because you DON'T make an entry in that table if the control is to be left in the default situation - disabled. Hope I didn't lose you in any of that, but I get carried away sometimes.
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
You were worried about speed. Here is my take:

If everyone sees everything but not everyone edits everything, then a commonality is that EVERY CONTROL will be visible and populated. So the only thing you need to do is choose how to enable or disable controls. Look to this question: How many controls will need to be enabled? (Or disabled, depending on most common state.)

Now my viewpoint (known to sometimes be a little off-kilter) would be to have a table of users for which there is a UserID. You put stuff about the user in the user table - but do NOT put control info. If you choose "role" then the user role goes in this table. If you are going to make controls per-person then nothing else goes here.

You also have a table of control names and this table also has unique numeric IDs for the entries. You use this table to look up the control ID OR the control name depending on which way the query of the moment needs it. This does imply that both fields will be indexed. Which one is the PK? I would make the numeric side the PK but that's force of habit.

Now you have a table that contains UserID, ControlID, and perhaps nothing else, though this depends on whether you can use the idea that you have two groups of controls. Group 1 is always enabled for every user. It will probably be small. Group 2 is the set of controls that vary from user to user. ALL controls in group 1 start enabled and stay that way. All controls in group 2 start disabled but are subject to change. So what you do is your user/control table has tuples <UserID, ControlID> meaning that this user will be able to update this control. The catch is that you only have ControlID numbers from Group 2 because you never change Group 1 controls. OK, that makes it a little shorter.

But now here is where you can get really fast. The User/Control table is SPARSE. It only has entries for things you want enabled. By default everything else is DISABLED. And it only applies to group 2 controls. So you open a query for this user that reads the entries in the User/Control table and loops through it looking for records representing controls to be enabled. By your description, this list will be smaller than the list of controls to be DISABLED because you suggest that enabling a control is typically the less likely case. (At least that is what I infer.)

When do you do this? When you launch the form, the Form_Load event has all controls in it and you can also write code to identify the user. (That's a separate topic.) So you step through this recordset, enabling the controls based on the ControlID and the fact that you have a table that associates Control Names and Control IDs. Does that make sense?

By the way, if you ever decide that that you actually CAN group this into a few roles, then your User/Control table becomes a Role/Group table and you still get the same result. You enable controls based on the table that enumerates the things to be turned on. And it works this way because you DON'T make an entry in that table if the control is to be left in the default situation - disabled. Hope I didn't lose you in any of that, but I get carried away sometimes.

Doc, I don't really know how to thank you for this details. I'll follow your instruction and will ask here if I need more help.

Million thanks
 

isladogs

MVP / VIP
Local time
Today, 22:33
Joined
Jan 14, 2017
Messages
18,186
Tera
Just to observe that using the approach suggested by the Doc, you can easily identify groups of controls to enable/disable, show/hide, lock/unlock depending on user roles.
These can easily be handled as groups of controls using the Tag property. See https://www.access-programmers.co.uk/forums/showthread.php?t=293439

BTW - I'd also like to point out that until Doc got involved, I had no real idea of what you were trying to achieve with this thread!
 

deletedT

Guest
Local time
Today, 22:33
Joined
Feb 2, 2019
Messages
1,218
BTW - I'd also like to point out that until Doc got involved, I had no real idea of what you were trying to achieve with this thread!

Wasn't my question clear enough?
I just wanted to know how I can use bit masking when the number of Booleans that are going to be managed are more than 32. I was using long data type in my tests and hit the wall when the number of Boolean exceeded 32.

The DBGuy suggested using Large Number data and then you suggested Double data tape.

Later DOC convinced me since Bitmasking is not native in Access, it may causes more steps and headache and maybe better to use a table.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:33
Joined
Jan 14, 2017
Messages
18,186
Perhaps I just didn't read it properly.
However I did understand that what you originally needed was a very big number - hence my suggestion. ;)
 

Users who are viewing this thread

Top Bottom