UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Log In Based User Role And Access Privledges, Access 2016    
 
   
Psycoperl
post Jul 12 2018, 02:11 PM
Post#1



Posts: 184
Joined: 11-March 15
From: Somewhere lost in the NY Subways


I am working on a project for a new appointment tool, what I would like to do is restrict access to functions based on a users permissions.

In a previous project where I set this up I had a table which contained the userinfo (see tblUsers below)


-----------------------
| tblUsers |
------------------------
| ID - Autonumber PK
| userID - ShortText(50) UK
| userName - ShortText(75)
| userDepartment - ShortText(50)
| userTitle - ShortText(50)
| userPassword - LongText (stored as a Hash via
| userPasswordForceChange - boolean yes/no
| userPasswordChangeDate - date
| userPasswordMustChangeDate - date
| userActiveDate - date
| userInactiveDate - date
|

The table also contained a series of boolean yes/no fields that represented the "role" that a user could be granted. Users can have one or more roles to do certain tasks or view certain forms/reports. (see a small sample below)

| ....
| roleDeveloper - boolean yes/no
| roleStaff
| roleSupervisor
| rolePrintResults
| roleCreateAppointments
| roleReporting
| roleReport_Attendance
| roleReport_Eligible
| roleAdmin
| roleAdmin_UserAdmin
| roleAdmin_ConfigurePeriod
| roleAdmin_BatchLoad
| roleAdmin_ExportReports
| ....

What I was then doing was checking if the user was logged in (via a Global Variable), and if they had the required "role..." field set to true to test if they are able to do something and either enable the button or hide the button as needed.

It also determined if they were able to open the form or not.

The users table is in a password protected back-end database.

I am trying to see if there is a better way to implement feature access control. As when we add new features, functionality or need to subdivide restrictions, we are adding a new field and to keep them grouped together we end up having to re-order the fields.

To address some concerns that I have read here... the reason that we are using a secondary password instead of just using windows logon is because users do share workstations and on certain computers where there are common account logins for Windows as those computers control our comp lab monitoring software and if we switch users it resets all of the client computers -- something we have to avoid. To this end, I will check during the login process, we will be checking the username and password against the users table and also checking if the computer is logged into windows using one of the authorized accounts (which we will have a list, that Admins can change as team members come and go)
Go to the top of the page
 
isladogs
post Jul 12 2018, 02:39 PM
Post#2



Posts: 474
Joined: 4-June 18



This topic came up in a recent thread.
In that I suggested it was ok to use a Boolean field where just one permission level was required.
I got criticised for doing so as it was going against normalisation rules

Where you have multiple Boolean fields like this, it's definitely a very bad idea.
This data needs to be hived off into a separate table tblUserPermissions or similar with fields UserID and userLevel.
As many users would have several records, this would have a one to many join to the main user table.

It is also generally a bad idea to store passwords and just hiding them using a password input mask is totally inadequate as it is trivial to remove.
If passwords get hacked, there could be huge issues for which you would be held responsible.
If you MUST store them, I urge you to encrypt the passwords using a strong cipher.
Better still, get rid of them, use active directory and require users to logout before leaving a workstation.

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
nuclear_nick
post Jul 12 2018, 02:51 PM
Post#3



Posts: 1,586
Joined: 5-February 06
From: Ohio, USA


Ugh.

Sorry, but I think you need real help with your design.

For instance... what happens when a new access level is implemented? From the looks of things, you'd have to add a new field to your table, and then update that for everyone. In some cases, changing fields in an active database can be a chore, sometimes requiring you to have exclusive access, which, in a multi-user environment, gets really … uh... fun.

My design, explained simply, is a users table, a permissions table, and a junction table of user permissions. The way it works, simply, is the permissions table has forms and reports that the user can access, so that when a user does try, a check is made against the 'UserPermissions' table to see if the particular user has access, and if so, allow them in.

In later expansions, I created 'groups' and update queries that would add all permissions of a group to a user, when things got really large. But I did it without making changes to the original 'users', 'permissions'. and 'user permission' tables. Just a few more tables and forms and queries to hold 'group permissions' and apply to the user (via update/delete queries) those group permissions.

Does that help any?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Psycoperl
post Jul 12 2018, 03:06 PM
Post#4



Posts: 184
Joined: 11-March 15
From: Somewhere lost in the NY Subways


@isladog --
This is what happens when you do too many edits... I had in a previous draft details about the password storage -- but it got edited out by accident when i was trying to clean up my thoughts to make it easier to understand. we store the passwords using a SHA1 hash. When we check the passwords we actually are comparing the hashes not the actual passwords. I.e SHA1("passwordtext") = userPasswordHash. The actual non hashed value is NEVER kept.
Go to the top of the page
 
Psycoperl
post Jul 12 2018, 03:43 PM
Post#5



Posts: 184
Joined: 11-March 15
From: Somewhere lost in the NY Subways


@isladog --

The mandatory logout is not an option as I mentioned
QUOTE
... some of the computers that would be using this system are also the same ones that are the "control station" for our comp lab monitoring software and if we switch users it resets all of the client computers -- something we have to avoid
as we cannot have tests be restarted in the middle. There are other reasons for preventing the logouts which are due to other limitations of our testing labs.

I will look into the three table option....
Go to the top of the page
 
Psycoperl
post Jul 12 2018, 04:05 PM
Post#6



Posts: 184
Joined: 11-March 15
From: Somewhere lost in the NY Subways


@nuclearnick --

Thank you for your feedback. Yes you hit some of "my favorite" problems tongue.gif , which is why I was looking for advice on how to proceed.
The previous project was not used by as many people at one time, nor was it used EVERY single day so there were times where maintenance could be done or I would stay after the department closed for the day or work remotely at night or on the weekend to deploy the updates.
This newer system will (a) have a lot more functions and (b) will be in use daily so the ability to demand exclusivity is less -- and my availability to stay late and do weekends is negligible for the next six months.... so I wanted to have a better option.

I am trying to think of how you would show on a form how you manage the users roles? I.E. how do you display the roles they have and how do you add/remove the roles.

Does your UsersPermissions (or UsersRoles) table use effective dating to maintain history or does it just keep a list of what is currently active?

....


I think I am going to need a few wine.gif as part of this development project.
Go to the top of the page
 
isladogs
post Jul 12 2018, 05:23 PM
Post#7



Posts: 474
Joined: 4-June 18



QUOTE
I am trying to think of how you would show on a form how you manage the users roles? I.E. how do you display the roles they have and how do you add/remove the roles.
Does your UsersPermissions (or UsersRoles) table use effective dating to maintain history or does it just keep a list of what is currently active?


I use a listbox to display the active roles
You could keep a history but I've never seen a reason to do so

Suggest you use a stronger form of encryption than SHA-1

For info, a quote from Wikipedia
QUOTE
Since 2005 SHA-1 has not been considered secure against well-funded opponents, and since 2010 many organizations have recommended its replacement by SHA-2 or SHA-3
Microsoft, Google, Apple and Mozilla have all announced that their respective browsers will stop accepting SHA-1 SSL certificates by 2017.







--------------------
nil illigetimi carborundem est
Go to the top of the page
 
Psycoperl
post Jul 12 2018, 05:52 PM
Post#8



Posts: 184
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Are there any open source SHA2 or 3 modules that are available that you would recommend?
Go to the top of the page
 
isladogs
post Jul 12 2018, 06:23 PM
Post#9



Posts: 474
Joined: 4-June 18



QUOTE
Are there any open source SHA2 or 3 modules that are available that you would recommend?


Sorry I don't know the answer to that.
Try Wikipedia and look at the references

The only database I've distributed where passwords are stored uses a different encryption method but according to Wikipedia is probably not much more secure than SHA-1.
its used in a number of schools despite my advice to the contrary
Active Directory really is the way to go here if its possible to modify your systems

This is the same fairly short text string encoded in various formats:
SHA-1 : 8a68ee4eb9a3c81cf4cfe5a759ab253688f309c1
SHA-256: 36f112e255dcdc66a10ed8b545dc6eed397b6762fff4f07dfe11e33f8fe6e4ba
SHA-512: 6a5191f34624f27fbd0deded8c4a5c2e25040ed26e1c90e2935b69a47c1961fc88cf9dc9e00eee4b
e947908ce897778135fe8a05be75df872f08a12bd5369d98

All these and more are taken from SHA1 generator etc

My encryption method does however allow me to modify the cipher to whatever I want & the results are harder for humans to read
e.g. using the same string as above and 3 different ciphers, I get shorter but more complex outputs
Cipher1: ˆ…v’ƒÈ'¦
Cipher2: žÂ+{1d¹+´
Cipher3: K__M1ù
The encrypted passwords have never been broken in over 15 years despite a small number of determined unauthorised (student) hackers combining to try & break the system.
I was very pleased/relieved that they failed especially as they cracked some other databases that were nothing to do with me

Good luck with your project
This post has been edited by isladogs: Jul 12 2018, 06:23 PM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
JonSmith
post Jul 13 2018, 02:38 AM
Post#10



Posts: 3,831
Joined: 19-October 10



Here's the thing though guys.
Its so easy to still fake your way into this even with hashed passwords.
One of the first external databases I encounted (which was a program that cost 6 figures) had plain text passwords stored in a table in the SQL server.
I was trying to learn from a 'professional' as I was still in my early days. I read the login details and connected to the SQL server and could read everyone's user name and password, I could add myself too.
The FE they deployed was also uncompiled so I could see all the code. I can't remember how they deployed it to be honest, I think manual installation on each machine.
Suffice to say I reported this breach to the head of our division as in that kind of organisation if we lost some passwords like that it could need to have been reported as a data breach on a national level. Luckily it didn't get that far.

They quickly changed it so the access to the SQL server was done by AD group. This closed down the loophole and I couldn't find a way to break in anymore. I still had the old user name and password but I couldn't add myself to a user table anymore and change access levels.
Anyhoo, my point is, if security and user levels is really such a high concern for you then you pretty much require a SQL BE. I can think of some ways of doing it with a secondary, passworded Access BE that was only connected to in code which should be hard to crack if the database is compiled.

So yeah, is this to deter people or because you have a legal compliance issue here? Your approach should affect that.
In my current organisation the sharing of passwords etc is apalling, I came across another application made by a 'professional' that had a username/password set up. Not only was it super easy to just look into the data tables (which weren't even masked) but he'd built in a 'forgot your password' button, this would prompt you to enter an email address and would send you the password using your own email account, the problem is it never bothered to validate if the username you were using matched the email address, so you can just enter anyone's email address, pretend you forgot and then get their password sent to you with your own account.
The guy who made didn't react too well when I contacted him to discuss his security flaws.
Thing is, it doesn't matter too much here so although I'm gonna fix it (I'm gonna dump his whole app, the code doesn't even compile) but its no big deal because of the culture here.
Go to the top of the page
 
isladogs
post Jul 13 2018, 03:24 AM
Post#11



Posts: 474
Joined: 4-June 18



I agree completely with Jon.
Although the encryption in one of my client apps has never been broken ...yet...doesn't mean it never will be.

That's why I recommended that the clients move the BE to SQL Server and the passwords to AD.
The first of these was implemented but they refused to do the second.
I insisted on the above being recorded formally so the responsibility became theirs not mine.

The nearest breach was when a member of staff gave one of his students his network password which then led to a group of students trying to break into my app and other unrelated apps and alter sensitive data related to exclusions. They caused damage elsewhere but the hackers couldn't get into mine. In fact their attempts were tracked by the app and this led to action being taken (exclusions).
Suffice to say the member of staff was also required to leave
This post has been edited by isladogs: Jul 13 2018, 03:26 AM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
JonSmith
post Jul 13 2018, 03:28 AM
Post#12



Posts: 3,831
Joined: 19-October 10



QUOTE
I insisted on the above being recorded formally so the responsibility became theirs not mine.


This is a key thing, if you are an external contractor, particularly self employed. Then legally (at least in some countries) it is possible for an organisation to claim you are responsible for damages should an event like the student hackers occurs and money or reputation loss occurs.
Its very smart of isladogs to protect himself by making his objections on the security flaws clear so the responsibility is no longer his but is the company who hired him.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2018 - 08:42 PM