Microsoft Access Security Best Practices when linking to a SQL back end

  • Thread starter Thread starter tdmailbox
  • Start date Start date
T

tdmailbox

Does anyone know where I can find a best practices type document on
access security? Basicly I have a access database application I am
writing with a SQL server back end for a bank client. The application
was originally written to use access's own .mdw file for security.

However there is a push by the internal IT staff for me to move the
security into a table inside the SQL database. I am not really sure
what advantages this would provide and I am trying to make sure I am
not missing something. They are not looking to premission each user on
the SQL database, they are looking for me to create a user table inside
the SQL backend for the database that I built and have the access
databse call that table to verify permissions before granting access to
the application.

I am trying to determine if there is any advantage to configuring
security that way rather then using a .mdw file.

Can anyone shead some light on why using an internal(or in this case
linked) table would be better to store user ids and password?
 
I'm assuming that you will keep the Access frontend. Is that a correct
assumption? If it is, then your IT personnel are making an erroneous
assumption. Before Access can verify the username and password in the SQL
Server table, it will have to actually be opened and have some query run
from a startup form. If the authentication fails, then you could force
Access to close. On the other hand, the built in user level security in
Access authenticates the user before the database actually opens.

Furthermore, it will be significantly easier for users to mine passwords
from the SQL table than from the .mdw file. Also, attempting to create your
own security algorithm is not going to be an easy task.
 
Yes we would be keeping the access front end. So the access database
would open a security table on the sql backend from an access startup
form and use that to permission everyone. It would have to do exactly
what you said, check creditials and then close out access if they
provide bad credentials.

My biggest concern is actually what you said, trying to re-invent the
wheel by creating my own security algoritrhm and then in turn doing a
better job then the group of microsoft programs that designed the .mdw
security controls.

It isnt my IT staff that is pushing this, I am doing this application
for a client and it is their internal IT staff that was the one that
stated using a .mdw file would violate their security policy.
 
What they are asking for makes sense...sorta

1. User logs in during startup of your app
2. Your app calls a stored procedure that verifies whether the user name
and password exist in the "security" table, and optionally returns a role
specifying the functionality that user is allowed to use. No one other than
an admin actually gets to see anything in the "security" table...the SP just
returns yea/nay allowing access to your app...and the role, if needed.
3. Your app is compiled as an MDE, and closes during startup if the SP
rejects the user. Likewise, you'd have to execute the SP in every form,
report, function, etc. before allowing that form, report, whatever to open
or run. Just because you have a startup form, doesn't mean a knowledgable
user can't bypass it. Likewise, they would need to set permissions on the
tables and any views, SPs, etc, your app uses.

Each user would need permission to execute the stored procedure in 2. So,
there is no MDW for users to take home to crack...just the server. Still,
you'll have a lot of code to write....and check. And depending on how this
is done, it could generate a lot of network traffic. Plus, you'd have to
make sure that someone on the network with a packet sniffer can't easily
grab usernames and passwords when this SP is called.

Sounds more like job security than Access security. Did they offer you a
big contract?
 
Lynn Trapp wrote:
(snip)
Furthermore, it will be significantly easier for users to mine passwords
from the SQL table than from the .mdw file.
Click to expand...

You should never store a plaintext password. You should only store its
cryptographic 1-way hash. Then, the plaintext password never goes along
the wire, and is not stored in the database. (So, anyone & his dog is
welcome to view the password table.)

HTH,
TC
 
Originally the project was quite small but it has grown somewhat. I
was actually looking over the weekend if I could buy someone's code
module that does this or sometime similar. I figured it might be
cheaper to buy then to build. Anyone know of a vendor or open source
website that has code to athenticate a user against a table inside of
access before allowing them to load the database?

I could code this but i figured someone has most likely already built
this or something similar.
 
There really is no shortcut in accomplishing what you want to do. Like I
indicated earlier, you'd have to write code to call a function during
startup and EVERY function, form, report, what not would have to verify that
the user is permitted to do "it"....whatever it may be. If you're only
concerned with them opening the database, i.e., every user is permitted to
perform any task / has the same role, that would only simplify matters
slightly. You'd still need a stored procedure on the server like I
indicated earlier...plus a form and a few lines of code in your MDE to allow
entry of username and password, and to call the SP. You'd still need to
implement Access workgroup security on the database (and not distribute the
MDW) to ensure that users don't bypass your startup code....or you'd have to
write code in every function, form, report to ensure the user is permitted
to use that function.

YOU'LL have to write it....because no one has built your app before. And
the one thing you absolutely cannot/should not do is store and authenticate
usernames and passwords from within the MDE itself....that would be contrary
to your customer's requirement and pretty dumb because anyone having access
to the file could hack it and get the usernames/passwords.

Whatever you estimate this will take in respect to manhours, multiply it by
at least 10...hence my reference to job security earlier. There is no
canned solution, nor is this a trivial problem to address.
 
I'd be asking *why* "using a .mdw file would violate ... security
policy".

Are they afraid that this would compromize their centralized control of
their security? Presumeably there are security constraints at the
server end, regardless of what you do at your end. If the central
security said that Fred could not do ''X, then, Fred could not do 'X',
even if your Access workgroup file said that he *could*. So there is no
way your workgroup file should be able to override whatever security
had been set up centrally.

Does that help?

TC
 
Back
Top