Using Form As User/Password Validation? 37

[Spyder757]

Basically I’d like to have a table named “Users”. This table would contain two columns. USER ID and PASSWORD.

A form with two unbound columns would be used to by a user to enter their user ID and password would be validated against the USERS table after a command button was pressed. If they matched they would be allowed into the next form in the database, if not an error message would be displayed and they would still be stuck on the main forum.

Is this possible?

I seem to recall a similar thread some months ago but a lengthy search didn’t turn up anything.

If anyone has a functional example of this please let me know.

Thank you.

Spyder757

 

[Terpsfan]

All future requests for this example should go to this link for a download:

http://www23.brinkster.com/vbprogrammer38/SmartSolutions.htm

 

[jweber]

Omega36:

Thank you for sending us the database...and for posting it for all the others! I really appreciate all the knowledge-sharing that goes on here!

Thanks again!

 

[tothemax]

Thanks, Omega36!! I have reviewed the db you made available and it is great!

 

[kat25]

Thanks, Omega36!!
This works great. Again thank you for sharing your knowledge.

kat25

 

[BillPower]

To drmansoor, KornGeek and RobPotts in particular.

I’ve been debating for a while whether or not to expand on my personal view of “Access Security”. Can’t really ignore multiple requests to do so, so here goes.

Firstly, I don’t have any strong feelings about “Access Security” at all. When I saw Omega36 ‘s posting in thread702-442005 , I was so pleased to see another Member suggesting their own security method, especially after the hammering I’d taken in previous threads, thread181-417795 among others for suggesting my own method. The “joke” remark just came out (a slip of the keyboard), though I do think “Access Security” a Utility to be Ridiculed at, I should have kept that to myself.

Secondly, I think (in my opinion) that Access, itself, is the best programming tool around but only as a Front-End to MSDE, MS SQL and Oracle or similar, that’s why I specialised in it. It’s just a shame about it’s so-called Security System and poor atabase Engine. Beow I’m just going to list any immediate reservations of mine about using “Access Security” that come to mind, I’ve got umpteen, but don’t often think about them, (I don’t Use Access Security or Recommend the Use of Access Security full stop/period to any Member of Tek-Tips.

1. Access Security is not easy for the average person to learn.(RobPotts must have an exceptional IQ). It is extremely “Dense Stuff” coming to grips with using it as other Member’s that advocate using the System have pointed out.
2. In no way is Access Security user friendly almost every day someone comes on to Tek-Tips saying that they can no longer get into a DB that they have attempted to secure using Access Security.
3. Immediately on installing Access Security you increase the chances of your Application corrupting, we all Know how often .mdb Databases corrupt/become unreadable, you will now have an additional .mdw file and workgroup .ini files installed to enable you to access your DB, they can also become corrupted.
4. Using “Access Security” also slows down your “Application”, this relates to 3 above, any action that you tell your “Application” to perform will now firstly have to check the security file and workgroup file to check permissions for the current user. Please believe me, this can have a major impact on the performance on your “Applcation”.
5. Even if you go to the lengths of Securing your Application using Access Security the Application is not secure until it is Encryted. Anyone can easily download AccessRecovery or AccessFix among other utilities from the the web to view your DB. Basically anyone can access your DB no matter what permissions you specify in Access Security unless you Encryt the DB(it’s not been mentioned in any of the Threads I’ve been involved In).
6. I have never in 10 years of being an Access Programmer ever been asked to allow users to edit my Forms, Tables Queries etc, Clients have only ever required a Password to Logon and in certain cases to restrict access to certain records, I wouldn’t want anyone messing about with my programme anyway Omega36 ‘s posting covers this.
7. To secure your App without the need for “Access Security” Encrypt and Password your Back-End DB, use ADO in the Front-End, Save your Front-End as an MDE or ADE.
8. Contact me if an example can help.

 

[markphsd]

In response to all those numbered articles, I would say...

"Let the games begin Billpower!, let the debate commence"

...but, its on like post # 5 billion.

Maybe its time for a Access Security Forum?

I would like to say that access security only seems hard to learn. For all of those detered by it, you haven't put in your 40 hours of work into learning it. As for its security holes, shouldn't we then take Billpower up on his suggestion.. encrypt the database; and then always consider encryption part of the security measures that should be employeed.

Mark P.

Bleh

 

[KornGeek]

billpower,
Thank you for your explanation. While I still feel that Access security is a good system, I can now better understand your postition. I may disagree with your opinions, but at least I can respect your position. I get annoyed at people spouting strong opinions (on any subject) but not giving any reasoning why they feel that way. Considering how many people argue strongly and fiercely in favor of Access security, I appreciate what you have potentially opened yourself up to.

I like Access security because:
A) I already understand the concepts. I may not have every piece down 100%, but I understand it enough to make my applications more secure than they will likely need in the short term.
2) It doesn't rely on any other programs (such as an SQL back-end). Because my entire application is Access-based, I prefer not to add any other pieces in. At the moment, I don't have the tools or experience to be making the larger, more complex SQL back-ends (but I've informed my boss that we will need to move in that direction sometime).
D) There are a number of resources to help me if I get stuck on a security issue. Between books, websites, and these forums, I can find help. If I invent my own system (aside from the likelihood that it will be easier to hack), then I will have to support it myself.

Because of these reasons, I feel that Access security is the best solution for me and for now. In the future, I will probably rely more on SQL when I use it as my back-end.

Everybody, before we get up in arms about our opinions, let's remember that just because we feel our answer is better than somebody else's doesn't mean they need to agree. Sometimes it's just fine to agree to disagree. I may disagree with billpower, but I don't have to develop, maintain, or support his applications, and he doesn't have to develop, maintain, or support mine.

 

[BillPower]

Hi tothemax, I haven't got an example to send to you at the moment, I've asked frantik in thread705-493633
to send me a copy DB to demonstrate this on and get the ball rolling. If you have a small Front and Back-End please send it zipped up to me at billpower@cwcom.net firstly removing any records that you regard as sensitive.

Hi markphsd, I don’t regard trying to advise members on this Forum as a game. The point of my post is to enlighten Members to the fact that they can Secure an Access Application without the need to use Access’s Built-In Security. If you want, do the same as I’ve asked tothemax send me a DB, Front and Back-End .

Hi KornGeek, thanks for your input too, but I think you may have misunderstood me, the demo that I want to perform for the whole “Tek-Tips Access Community” this weekend will be for both an Access Front and Back-End DB. There are lots of “Newbies” that come on to this Site every day and I don’t like the idea that they are currently being advised to use “Access Security” and that there’s no alternative. A single table in the Back-End DB as Omega36 has demonstrated is all that’s needed to control who can access your DB, one additional table will suffice for giving control/access to who you choose to View your Forms, Tables or Records etc. If in the future, like you have said you might upgrade your Back-End to SQL, using Omega36’s or my advice the Security/Controls already built into the Front-End will continue to work without any changes being necessary. Lastly, I bet if you removed “Access Security” that your Application’s performance would noticably improve.

Anyway, whoever sends me their FE and BE DB first will be the one that I use as an example. Will be posted for download at

http://www.wpower.fsbusiness.co.uk/

Thanks to everybody for your input/interest, one thing I’ve learned from this is to in future keep my opinions to myself.

Have a great weekend everybody.

Bill

 

[KornGeek]

billpower,
There is one piece I don't understand about your security method. How do you protect the security table to keep someone from modifying it? Before I learned the Access security basics, I was considering a similar approach, but never figured out how to protect my security table.

Thank you for your patient explanations.

 

[GingerR]

To beetee: i'm positive this can occur. You can link to Omega's tables from another db and alter the data as you wish. Also, tables, etc wont be hidden if user changes Access TOOLS OPTIONS to view system and hidden objects.

I have always employed Omega's type of security (altho my users don't have to enter their ID, it reads their logon ID's with Startup Form OnOpen strID = environ("username&quot and i store those ID's and any 'security levels' in my 'security' table). However that is for homegrown non-IS unofficial databases within my own company where users don't have the skills or time or desire to poke around. Now I have a side-job for another company and am hiring a buddy to do the security for me--don't have much time and I know I can 'get it' some day but don't have time right now so I'll pay him to do it for now. So--yes--it all depends on your needs/time/skill level.

also--one thing Omega you don't mention is disabling the Bypass key so users cannot open the db to the database window by holding down the shift key while launching.

cheers--g

 

[Terpsfan]

Okay, here is what I think is a very good exposition on the failings of Access security.

The major drawback of Access security is that it is file based. All Access workgroup files (mdw's) and database files are quite exposed because read, write, and delete permissions must be granted to all users for the network file shares where the Access files are stored if you want your users to be able to update the data. These files can, therefore, easily be copied, deleted or hacked. Regular backups are your only defense against lost data, and there really is no defense against exposing your Access data to a determined and skilled hacker.

Whereas with SQL Server all data access goes through SQL Server itself and users do not require access to the physical data files. With SQL Server or MSDE security, users have no direct access to the mdf data file or ldf transaction log file - they usually have no clue where it resides either - it's located normally on a dedicated server.

Because Access does not support any kind of integrated security with the operating system, features such as password ageing, account lockout after invalid login attempts, and logging user activities are not supported. At its best, Access security is good for deterrence only.

Access security is also a bottleneck for the front end application. All permissions validations must be passed over the network because with the Access Jet Engine all requests for data, etc are passed over the network in a multi-user environment.

You also can't integrate Access security with the operating system (NT, etc) as you can with SQL Server.

The gist of the argument is that if you want "real" security in an Access database, upsize it to either MSDE or SQL Server.

I have not intended to replace Access security with the sample database I provided. I implement what I have created as a means to keep your average joe from another department logging into the database or accessing certain parts of the application. Obviously this isn't a deterrent for a good hacker or someone more well-versed in Access. Fortunately my users here aren't of that sort. I'm actually in the process of upsizing all my applications to a SQL Server or MSDE environment.

 

[wreded]

Omega36,
Thanks for Your time and trouble in this forum. i'm not an Access guru. i'm in computer support and have to support things from A-Z (PC to Network, to programming, to...You name it). i've seen more than one Access database application blow up and lock everyone out. That alone makes me shy of Access security (it also makes me a believer in always working with a backup, not a production copy). Anyway, along with lots of other folks in this forum i believe Your example will benefit my small application, make it easier to maintain, and keep the honest people honest.

Thanks for sharing!
Dave Wrede

 

[Callreluctance]

How do you hide the database window?

 

[Templar2k]

Callreluctance,
That's ez mate....
here is how: on your "DatabBase Window" go to: Tools > Startup... then on the startup window; uncheck-mark the box that says: "Display Database Window", and done deal! ;-)

Templar2k

 

[Callreluctance]

Thanks, that did it.

 

[RickSpr]

As BeeTee asked, and GingerR confirmed, applications in which the security is implemented in VBA code have the fatal flaw that they can be easily read via links from another database. That seems to me to be a very basic skill for someone who has played with a copy of Access for a few weeks. So code-implemented security is only useful for helping keep well-meaning people in their own sandboxes; it's useless against the weakest script kiddie with a will to do damage. To his credit, Omega36 admits this. I only hope all those people who asked for a copy are sure that's all they need.

Omega36's objections to Access security were well thought out. I'm gratified to see that he/she is not saying that the simply security system is at all adequate, but rather that Access security is inadequate. I hadn't understood that was his/her point of view from the earlier posts. And I hadn't realized the exposures that come with file-based security systems--thank you for that, Omega36.

I have to voice my doubt on one point, though. Several people, including Omega36, have said that Access security slows down an application, at least where there are lots of users. I can't see how that could be, because Access security is always active. Even if you haven't created any user accounts and permissions, you're getting the default Admin account, and permissions are being checked (but always granted).

The .mdw file doesn't have to be read all the time, because the user's Security ID is read from it at logon and doesn't change. The permissions bits in the Document objects have to be retrieved from the .mdb file with the database objects, but that happens anyway, even if you are logged on as Admin by default. So I don't see how just using different user accounts with different sets of permissions would make any significant difference in speed.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[danvlas]

Just my 2 cents as I've seen much confusion in the interpretation of the mdw file...

Access security (ie permission on objects within the database) does not reside in the mdw file. It resides in the database itself. The mdw is just a way to make sure a user is who he says he is when logging on to Access and it keeps just the personal identification of a number of users. No permission is granted through the use of an mdw file alone. One user gets his/her permissions after the mdw file has identified him/her.
Hacking the mdw file is easy, one can even open it as a normal database. However, such hacking exposes only the user names, not the passwords AFAIK. And even if you hack both the user name and the password, you are still unsure you will have any rights on your target. And if there are more than one mdw file on the system, one being the 'actual' one and 5-6 baits, then the 'devious' hacker will be at least confused after finding user names, passwords and everything else and still not being able to do anything on the file. LOL

Encryption is good, but not absolutely necessary and is a different layer of security. It has to do with reading Access files from another application. If you do not enforce user level security but you encrypt the database, the only thing will be that other programs won't be able to read the database. Sometims good, sometimes bad.
I noticed that when zipping an encrypted database, the final zip file is larger than the database itself. That's because zip can't read its compression information and the database becomes a binary file with all its bytes written.

This should not be interpreted as a 'pro' or 'con' towards Acces security.


HTH

Forgot something...nobody mentioned case-sensitivity in VBA based security: just turn Otion Compare Database to Option Compare Binary...

Daniel Vlas
Systems Consultant

 

[RickSpr]

Dan,

Just a clarification on the .mdw file. For user accounts, it contains the account name, the password, and the SID (Security ID). The password is encrypted. The SID is developed by an algorithm combining the account name and the user PIN; the PIN is not stored anywhere. The SID may also be encrypted, I don't know.

Group accounts are similar except that they have no password. Their SIDs are derived from the account name and PIN only.

Certain predefined user and group accounts have special algorithms that don't follow this rule.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[KornGeek]

It's become buried here, but Omega posted this above:


All future requests for this example should go to this link for a download:

http://www23.brinkster.com/vbprogrammer38/SmartSolutions.htm

 

[CTOROCK]

this is good "The greatest risk, is not taking one."