"Whitey" <No********@gmail.com> wrote in news:1118338960.535097.95140
@z14g2000cwz.googlegroups.com:
I have a password secured database. After the password is entered the
user has a form that I created that allows them to search the database
and retrieve information.
The problem is that the connection string requires a the database
password even though the user entered it to open the database. How can
I get the password from the system to build the connection string?
Thanks for any help,
Whitey
You can't... *:(
One thing you can do instead is to secure the database (either create a
new .MDW file via the \Microsoft Office\Office\1033\WRKGADM.EXE program
or work on a *copy* of SYSTEM.MDW), and create a new user that is the
Owner of the database, say, DBAdmin. Then give the Admin user a password
(even if it's just a carriage return), but take it out of the Admins
group.
Next, make a shortcut that calls Access but uses this new .MDW file,
modified to suit your system, of course:
"c:\program files\microsoft offfice\office\msaccess.exe" /wrkgrp "c:
\theapp\security.mdw" "c:\theapp\myapp.mdb"
Then, be sure to login as DBAdmin, instead of just Admin. It is
important, because you want DBAdmin to be the database owner, and thus,
own all of the database objects (tables, queries, forms, etc). Then,
because by default Access connnects to an MDB file by default as the user
"Admin", you can definitely control access to things this way, and when
you distribute your app to customers, they will log in of course as Admin
(even if they don't get the Access login prompt), which will then open
your custom Login form.
But first, develop a Startup form that is your "login" form. This will be
the form that "logs" users into your application. You don't close this
form, you just hide it, when a user successfully logs in. It should be
owned by DBAdmin, but you give access to the Admin user.
For all your queries, you can give selective access to the data by using
the "WITH OWNERACCESS OPTION", even if you lock down the tables so that
the Admin user can't open them, which is GOOD. You can control data
access (read, insert, update, delete) to the views, then, without
allowing the Admin user any access to the tables themselves.
Let the Admin user access this form, change all your queries to use the
"WITH OWNER ACCESS" option, too.
When you are ready, you will want to write a VBA function that can turn a
bunch of stuff on for you (i.e., disabled menus, disabled F11 key, etc)
if it detects that you log in as DBAdmin. You don't want to go turning
these things off beforehand!
The kind of implied cleverness about this is that you are leveraging
Access' built-in user-level security to do some heinky stuff for you for
not a lot of effort, especially when you distribute your app to other
users, and the data will be LOADS more secure, in practice and in
reality.
You just then have a DBAdmin-owned User table that stores usernames and
hashed passwords (I'm pretty sure that there is VBA code for an MD5 or
SHA-1 hash generator!) in that table.
If you then need to constrain user access in the app to different things,
then if you HIDE your login form, vs closing it, then the user's name
persists, and you then just have to code all your forms (and your UI is
through forms and reports...) to check the MAIN login name, etc.
The goal is to provide an application security layer that you can control
user access to the data without invoking the built-in security layer that
lets you restrict design-level access to the database objects only to you
and your custom Security.mdw file used to build the MDB file that you
distribute and configure to users. Now you don't have to manage MDW files
for users of our app!
There are tools to brute-force the database level password, but I don't
know of any that brute force passwords in a .MDW file (nor have I looked
recently, though).
