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
> Login Form Without Tables, Any Version    
 
   
elgrigo
post Sep 9 2017, 04:25 PM
Post#1



Posts: 4
Joined: 9-September 17



Hi all.

I am new to your forum with some experience in building access databases. I have built a database and I would like to create a login procedure via VBA. I searched the web and the only solutions I found involve a security table that stores user names and passwords and a login form that does the login according to the user. Then I can assign a lever of permissions according to the user logged in.

Is it possible to do the same without storing the user names and password in a table? I know that you can access table data either through the database itself or by other means outside access. How else can I store the user names and passwords? Any help would be appreciated.
Go to the top of the page
 
theDBguy
post Sep 9 2017, 06:13 PM
Post#2


Access Wiki and Forums Moderator
Posts: 70,675
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

You can certainly hard code all user credentials in your VBA code to avoid storing them in a table, but users won't be able to change their password. Or, you can store login credentials in custom properties, which will allow you to let users change their passwords.

However, I don't see any problems storing passwords in a table if you encrypt or hash them before storing, which means even if someone was able to see the data in the table, the user passwords won't be compromised.

The best practice approach is to use Active Directory or your company's network login process to give users permissions to your database.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Sep 9 2017, 08:28 PM
Post#3


UtterAccess VIP
Posts: 5,154
Joined: 30-June 11



Depending on your needs, you can setup the db to automatically recognize the user using a simple function such as Get Login name and then once you know who it logged in you can control what they can do and see. No login required at all! Just another idea to contemplate. Most client love this approach as it make the application much easier to use, no login form to fill out, no extra credentials to remember.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
nvogel
post Sep 10 2017, 04:50 AM
Post#4



Posts: 776
Joined: 26-January 14
From: London, UK


elgrigo,

As the other replies have said, using your network login (Windows or otherwise) is the best way. Do not try to invent your own authentication mechanism.

I would add that if security is concern then your database ought to use a server DBMS such as SQL Server, Oracle, etc.
Go to the top of the page
 
elgrigo
post Sep 10 2017, 10:34 AM
Post#5



Posts: 4
Joined: 9-September 17



Thank you all very much for your prompt replies! I am very much impressed by your speed and efficiency!

I have also thought about putting all user names and passwords into the VBA code but as you suggested, it will be impossible to be changed by the user. The windows login credentials is another method worth considering. As far as the table is concerned, I know that users wont be able to read the passwords if access is secure enough but I know there are programs outside access that they can open access tables and all fields are totally readable to them. As for SQL I do this as a hobby and I don't want to create more trouble than I can handle with the time I have available to me.

But as I was stimulated by your replies I think I thought about a possible solution and I would like your thoughts. It is possible to create some code to encrypt your password, like letter and numbers substitution on the fly. So the user will put the password in and the code will encrypt it and store the encrypted form in the table. When you need to verify the password the opposite procedure will apply. This way if someone has access to the password table will only have the encrypted ones but not the code to translate them. Therefore they will be basically useless. Any ideas? I don't think I have come across something like this.

Thanks again very much for your help.
Go to the top of the page
 
theDBguy
post Sep 10 2017, 11:38 AM
Post#6


Access Wiki and Forums Moderator
Posts: 70,675
Joined: 19-June 07
From: SunnySandyEggo


Hi,

The second paragraph in my reply speaks exactly about this subject, so yes you can encrypt the password before storing it in the table, so if someone managed to get to the table and see the data, they won't understand or know what the passwords are.

However, you haven't told us exactly what you are trying to protect. Encrypting the passwords doesn't do any good if the bad guys can see the main data anyway.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
elgrigo
post Sep 10 2017, 01:10 PM
Post#7



Posts: 4
Joined: 9-September 17



Hi,

Thanks for your answer. I have built a medical database and I want to give to different users different security access levels so that I have full control of the data but the others can only input patient demographic data and nothing else or limit their navigation to some forms but not all of them. So hashing the passwords will do just that. Thanks.
Go to the top of the page
 
nvogel
post Sep 10 2017, 01:35 PM
Post#8



Posts: 776
Joined: 26-January 14
From: London, UK


You should not be using a Jet/Ace database for confidential patient data under any circumstances in my opinion. Jet/Ace is insecure by design and role-based security is not possible. I suggest you take advice from whoever is responsible for information security in your organisation before you do anything. Many countries have laws and regulations that govern the use of personal data and healthcare data. Most organisations have their own internal standards for such data as well.


This post has been edited by nvogel: Sep 10 2017, 01:36 PM
Go to the top of the page
 
DanielPineault
post Sep 10 2017, 07:29 PM
Post#9


UtterAccess VIP
Posts: 5,154
Joined: 30-June 11



I agree, for any sensitive information, medical being one, you should never be using an Access back-end. You need to switch to SQL Server or another server based RDMS. In many instances, there are strict laws governing storage of this type of information and you truly need to know your stuff to make Access compliant (not impossible though) and even speak with a lawyer possibly.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
elgrigo
post Sep 13 2017, 08:48 AM
Post#10



Posts: 4
Joined: 9-September 17



I agree, in the hospital where I work there is specialized software for the patients data. The one I am talking about is used in my private office with a small number of people using it locally, not over the internet. I think it should be OK. However, it is a good idea to speak to a lawyer about it. Thanks for the advice.
Go to the top of the page
 
PhilS
post Sep 13 2017, 09:57 AM
Post#11



Posts: 365
Joined: 26-May 15
From: The middle of Germany


QUOTE
It is possible to create some code to encrypt your password, like letter and numbers substitution on the fly. So the user will put the password in and the code will encrypt it and store the encrypted form in the table. When you need to verify the password the opposite procedure will apply. This way if someone has access to the password table will only have the encrypted ones but not the code to translate them.

It is perfectly sufficient to create a irreversible hash of the password an store that. You never need to decrypt passwords. You only need to check if the presumed password entered by the user matches the stored hash value by applying the same hash algorithm to the entered text.

--------------------
The shocking revelation about digital signatures for accdb files.
Go to the top of the page
 
nvogel
post Sep 13 2017, 03:11 PM
Post#12



Posts: 776
Joined: 26-January 14
From: London, UK


That would be a very unwise thing to do. Since the table of passwords by definition has to be accessible from the desktop it's an utterly trivial task to overwrite the password with a hash of an intruder's choosing - or just bypass the login altogether and access the data directly without logging in. Fake security like that just deceives honest customers/users. It may cause them to trust the system more than they should and put their data more at risk than if you had just been honest and transparent about security (or lack of).
This post has been edited by nvogel: Sep 13 2017, 03:11 PM
Go to the top of the page
 
PhilS
post Sep 14 2017, 03:22 AM
Post#13



Posts: 365
Joined: 26-May 15
From: The middle of Germany


@nvogel, just to make sure we are on the same page. I fully agree with your statement
QUOTE
Jet/Ace is insecure by design and role-based security is not possible.
and anything else said in that regard.

QUOTE
That would be a very unwise thing to do.

If you are refering to the suggestion of storing hash values, I profoundly disagree!

It is always better to store a hash instead of the password itself. - All general security problems still apply though.

Compared to just storing a plain text password, it is better to store a hash because an intruder would need write access to the passwords table to overwrite it instead of just reading the password.
Adding some salt to the hash input will make this approach equally (in)secure to writing your own, simple encryption algorithm (don't do this!).

If you compare the hashing to "real" professional encryption, you need to combine the two. Either encrypt the hash or digitally sign it, so it cannot be simply replaced with a known value. The hashing approach is still better, because it will prevent the intruder from getting all the passwords, in case the encryption is broken.

Still, you are absolutely right about this beeing only fake security. In context with an Access backend database this does not much to protect the data.
This post has been edited by PhilS: Sep 14 2017, 03:24 AM

--------------------
The shocking revelation about digital signatures for accdb files.
Go to the top of the page
 
nvogel
post Sep 14 2017, 03:47 AM
Post#14



Posts: 776
Joined: 26-January 14
From: London, UK


I was referring to fake security (AKA "security theatre") being unwise. It's a bad idea to put up a login page in an Access application that runs on the users desktop and authenticates itself against a database. Use Windows logins or a similar server-based authentication mechanism instead.

Password hashing and salting using a secure algorithm is a good thing of course. Passwords should always be hashed and never encrypted.


Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2017 - 07:46 PM