"Home grown" Access Security

Will

I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security and what they are more focused on is a solution for when a user
performs a critical action the system can verify that it is actually that
user, e.g. Checks User Name and Password in a table in place of just
selecting from a drop down, (Has anyone implemented similar?). My question
is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their username
and password in on every function?

Thanks in advance

Will

Nov 13 '05 #1

Terry Kreft

Yes it is.

Store in either
global variables
or
database properties
or
a table.

But as Access security prompts for the username and password when the db
starts and then applies that security as they use the database I don't
really see what you are gaining, except a (possibly) bug ridden not really
secure alternative.
--
Terry Kreft
MVP Microsoft Access
"Will" <Wi*********@hotmail.com> wrote in message
news:34*************@individual.net...

I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security and what they are more focused on is a solution for when a user
performs a critical action the system can verify that it is actually that
user, e.g. Checks User Name and Password in a table in place of just
selecting from a drop down, (Has anyone implemented similar?). My question is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their username and password in on every function?

Thanks in advance

Will

Nov 13 '05 #2

Rick Brandt

Will wrote:

I am looking at using a table with user names, passwords and user
rights, which I would administer. I have read a lot about the
shortfalls of this and the lack of security but the customer does not
wish to use Access security and what they are more focused on is a
solution for when a user performs a critical action the system can
verify that it is actually that user, e.g. Checks User Name and
Password in a table in place of just selecting from a drop down, (Has
anyone implemented similar?).

If that's all they want just capture their Windows logon. No tables and
passwords even required.

The most basic problem with home-grown security is that ANY user will be
able to see your table of UserNames and Passswords. It can work just as a
way to track who entered what, but as stated you can just capture the
Windows name for that.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #3

Tim Marshall

Rick Brandt wrote:

If that's all they want just capture their Windows logon. No tables and
passwords even required.

From the access web: http://www.mvps.org/access/api/api0008.htm.
That'll get you the windows login as Rick suggested, unless there's an
easier way...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #4

Albert D. Kallal

"Will" <Wi*********@hotmail.com> wrote in message
news:34*************@individual.net...

I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security
Why do they not wish to use ms-access security? Further, why would you
develop your own system, write tons of code,a nd cost the client more money?

It might not look very good if you sell them on the idea to writing you own
stuff. Then, someone who knows ms-access will come along and ask why did you
folks waste all this money on writing your own security system when it is
built in? The end result here that any ms-access person that comes along
will view your approach as not being very honest on your part...but perhaps
some ploy to make more work for your self!
My question
is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their
username
and password in on every function?

Sure you can do this. I would assume that many tings such as the
company/customer name, and all kinds of setup stuff is contained, and
maintained in your application. I load all kinds of things into memory at
startup, stuff like company name, tax rates, year end dates etc. Loading
user name into memory (a global var) would simply be one of many things that
your application now no doubt maintains, and has when it runs.

Of course, any un-trapped error does re-set all global vars, but if you use
a mde, then errors don't re-set your global vars....

If you do use ms-access security, then you do have to sit down and learn
how ms-access security works. Often it seems that developers would rather
roll their own then learn how ms-access security works. The end result is a
custom security solution that any new developer now has to learn! It don't
make sense to try and write your own sql system, and the same goes for
security. Remember, even when you use ms-access security, you can and will
OFTEN wind up writing a lot of custom security code anyway. However, in the
long run you are still FAR better to use built in security as then you can
apply this knowledge to future ms-access projects..and have learned a
valuable skill in the process.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

Nov 13 '05 #5

Salad

Will wrote:

I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security and what they are more focused on is a solution for when a user
performs a critical action the system can verify that it is actually that
user, e.g. Checks User Name and Password in a table in place of just
selecting from a drop down, (Has anyone implemented similar?). My question
is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their username
and password in on every function?

Thanks in advance

Will

Maybe http://www.mvps.org/access/api/api0008.htm will be of assistance.

Nov 13 '05 #6

Darryl Kerkeslager

Perhaps what he is actually seeking is not the "security", but just the
permission levels. I use this scheme in my app because all users are are
already logged on to a secure intranet using Windows NT security, and are
business-wise authorized to perform all functions of my app. However, they
are not assigned to certain related areas: some data in another database
must be kept in synch with mine, unfortunately by human interface; only
two-three users per office do that, so any changes made to that set of data
by other users must be logged, and made visible to the 2-3 users who use
both systems. It's not a matter of security - nobody would have any
motivation to bypass it - it's just a matter of permissions, to make things
run smoothly.

I use Dev's fOSUserName() function to get the NT/XP user name, and assign
them to a user group upon login. From then on, a simple function call
checks if they're in the right user group to perform the function without
logging it. They of course never enter a separate password.
Darryl Kerkeslager

"Terry Kreft" <te*********@mps.co.uk> wrote:

But as Access security prompts for the username and password when the db
starts and then applies that security as they use the database I don't
really see what you are gaining, except a (possibly) bug ridden not really
secure alternative.

Nov 13 '05 #7

David W. Fenton

"Will" <Wi*********@hotmail.com> wrote in
news:34*************@individual.net:

I am looking at using a table with user names, passwords and user
rights, which I would administer. I have read a lot about the
shortfalls of this and the lack of security but the customer does
not wish to use Access security and what they are more focused on
is a solution for when a user performs a critical action the
system can verify that it is actually that user, e.g. Checks User
Name and Password in a table in place of just selecting from a
drop down, (Has anyone implemented similar?). My question is, is
it possible for a user to enter their user name and password when
they open the database and then to store that user name for the
rest of their database activity, rather than requesting that they
put their username and password in on every function?

There's a fundamental flaw in the thinking here. You assume that the
person at the keyboard is the person associated with the
username/password pair that the system is logged in as. You *can't*
assume that.

Say, for instance, that someone logs onto the database with their
personal username/password, and then walks away to have lunch.
Someone else sits down at the computer and pulls up the database and
types all sorts of things into the database. It looks like the lunch
guy did the work, but that's not the case.

You simply cannot know from the internals of Access or any custom
application exactly who is doing the data entry unless you force
them to authenticate for every single operation. Even then, someone
can very easily give someone else their username/password.

If you're going to ask for a username/password, then use Jet
security. It's built-in, it's less crackable than anything you'd
write from scratch and it is stable. You don't have to apply
security to objects, you can use the logon simply to identify the
user (I do this all the time, with no restrictions on security,
often to selectively enable controls and subforms).

I can't think of *any* advantage to rolling your own security for
this purpose.

And even using the NT logonID is going to mean that you have to keep
a table of the usernames somewhere, mapped to their authorization
levels. Why not use the built-in capabilities for doing this?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #8

Keith

"Will" <Wi*********@hotmail.com> wrote:

the customer does not wish to use Access
security

Is the customer a seasoned Access developer? Has he also specified that
none of the code should contain any vowels? IMHO it is ludicrous for the
customer dictate this kind of thing to the developer - they have
requirements and you, the developer, provide the solution using whatever
methods you see fit.

The issue regarding authentication can be resolved using built-in security
and a fairly simple audit routine. If you record every user transaction
against the NT user name and a time stamp, any anomolies regarding who did
what and when can be dealt with as and when they arise.

Regards,
Keith.
www.keithwilby.com

Nov 13 '05 #9

Tim Marshall

Darryl Kerkeslager wrote:

Perhaps what he is actually seeking is not the "security", but just the
permission levels. I use this scheme in my app because all users are are
already logged on to a secure intranet using Windows NT security, and are
business-wise authorized to perform all functions of my app.

I use something similar - 90% my apps are written against an Oracle
database. It's a simple matter to set up an Oracle user table with
various columns/fields for permissions and grant select only (read only
access). In fact, the connect string can be buried in a module of an
mde making it impossible for a user to access the table (I use pass
through queries as opposed to linked tables) at all.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #10

Barry Edmund Wright

Will,
Clients can sure ask for a lot of stupid stuff! You need to write a
convincing argument outlining the benefits of Access Security and give it to
your client. Of course to do that you need to know it inside out. I believe
that to be your challenge.

Writing a function that records and tracks adds and updates by user
(currentuser) is a lot easier to implement when Access Security is
implemented first which is what your client is looking for.

Regards, and good luck with your client.
Barry
"Will" <Wi*********@hotmail.com> wrote in message
news:34*************@individual.net...
I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security and what they are more focused on is a solution for when a user
performs a critical action the system can verify that it is actually that
user, e.g. Checks User Name and Password in a table in place of just
selecting from a drop down, (Has anyone implemented similar?). My question
is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their username
and password in on every function?

Thanks in advance

Will

Nov 13 '05 #11

Kathy

Yes, you could have a login form with user name and password. After
validating the login, make the form hidden and open the next form. Although
the login form is hidden, you can still reference the values on the form in
code.

I am looking at using a table with user names, passwords and user rights,
which I would administer. I have read a lot about the shortfalls of this
and the lack of security but the customer does not wish to use Access
security and what they are more focused on is a solution for when a user
performs a critical action the system can verify that it is actually that
user, e.g. Checks User Name and Password in a table in place of just
selecting from a drop down, (Has anyone implemented similar?). My
question
is, is it possible for a user to enter their user name and password when
they open the database and then to store that user name for the rest of
their database activity, rather than requesting that they put their
username
and password in on every function?

Thanks in advance

Will

Nov 13 '05 #12