access thread: RE: OBDC security
Message #1 by "Beverly Usher" <bUsher@h...> on Thu, 14 Jun 2001 09:27:41 +0100|
I need to be able to write reports in Access that look at a SQLServer 7.0 d
atabase. I will attach these reports to buttons in a program which we use t
o input and manage the data. I need to set up an OBDC connection to allow u
sers to view the data in these reports. I have currently set up some system
wide OBDC connections which ask for a password, but really would like the
reports to run transparently. I have also set up an OBDC connection which u
ses NT authentication to look at some training data in the db, which does n
ot require a password from the user. However, anyone who can log on to our
system could then look at this data if they set up a blank Access file and
linked to it.
Is there a solution to this? I have considered this type of connection (no
password required) as a user DSN but would rather not have to run around se
tting up all those local DSNs. Can anyone shed light on OBDC/DSN security a
nd what exactly it looks at when using NT security? How can I link specific
logins without having to ask people for their login password?
Sorry this is so long, but I can't think of any other way to explain it.
Thanks for any thoughts, Beverly
Message #2 by "Darron Michael" <darron.michael@h...> on Fri, 15 Jun 2001 15:04:02|
I have an Access97 database on our server that is shared by a lot of
users. I have a table in that database that has the network login_names
of the users, their access rights within the database, and their current
status (in or out of the database).
I have a line in the network login script that captures their login_name
and machine number and stores them in an environment variable.
When a user opens the database, an autoexec macro opens the "Main Menu"
form. Whenever this form is activated, it uses a function to read their
login_name and machine number from their environment variables, and then
compares this to the table of users to find their level, and enables or
disables objects (primarily buttons) on the menu based on their level.
You can program it to react to new / unknown users in one of two ways. It
can eject them and close the database or it can create them an account and
grant them "Guest" access.
I've only recently started working with my new SQL2000 server and I don't
yet know exactly how I'm going to implement my multiple Access97 DBs into
I'll send you the functions for this type of "Security" if you're
interested. It would let you handle the security issues within Access by
keeping them captive in forms, but you would still have the same problem
of having to have an ODBC connection to SQL set up on EACH machine that
will run this database. The only solution I know how to implement would
involve moving the data back out of SQL and into Access. Then you could
mirror the data (once an hour?) up to the SQL server using an automated
import function in SQL's Data Transformation Services.
Email me at darron.michael@h... if you're interested in this
type of solution.
Message #3 by "Beverly Usher" <bUsher@h...> on Fri, 15 Jun 2001 15:37:54 +0100|
Thanks for the reply. I'll take it home to have a serious look at this week
end. I already have a method for capturing logins in an existing system whi
ch I may be able to use, but will take advantage of your kind offer if I ne
ed further help.
Thanks for the ideas!!!