Creating Restricted Access to a database

LUKE_PRYOR
LUKE_PRYOR used Ask the Experts™
on
So I recently finished(well lets just say its finished) a database and I would like to set up multiple entities to view and utilize the database.  I have one person specifically who will need access to one form, one query and maybe one additional table.  Im still confused on the best way to do this though.  SHould i create a custom menu or is there an easier way to do it.  I just dont want a novice to have the ability to effect any of my other tables/queries and I dont want to confuse a very computer illiterate person with an abundance of objects.  If you could at very least point me in the right direction I would appreciate it.  Thanks
Luke P
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could possible handle this in VBA - Have a custom main menu and On Load make invisoble certain buttons based on the logged in network user.

In VBA .. something like

Dim user$

user$ = Environ$("Username")

Select Case user$
     Case "James1"
               Me.cmdForm1.Visible = FALSE
     Case "Paul1"
        .........
End Select
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
This can be simple or complex depending on your exact needs...

<I have one person specifically who will need access to one form, one query and maybe one additional table. >
...and what about all the other users?

If you need perhaps two levels of Security, it can be simple, any more, and things can get messy quite quickly
(Multiple users in the DB at the same time, multiple logins(Users), additional Security tables, tables, Auditing (who did what), ...etc)

What is your skill level with VBA?

So you need to be very specific here.


JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The other kicker here is that all access to your DB should be done through "Forms".

Meaning your one user would not be using a Table and a Query, but rather viewing (accessing) this info via a "Form"

So this requires some design foresight as well.

Author

Commented:
Ok additional info...
I have basically no VBA experience aside from copying and pasting some code I've found on the web... Uh oh right...

So... What you are saying is that I need to have a form open on entry?  Then based on the resulting info of this form(input) it would open up the database in either restricted or standard view?  Im fine with just two for now, but  my next database is going to be way more advanced(much to my dismay...) so possibly at least seeing more advanced options might temper my expectations.  

Also,
In the first suggestion BCUNNEY would i need to set every object as false as far as visibility?

Sorry for the dumb questions, I feel all alone in this world of VBA and SQL, there are a lot of resources, but not enough time to figure out the best ways of doing things.  Thanks
Luke
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I feel all alone in this world of VBA and SQL, there are a lot of resources, but not enough time to figure out the best ways of doing things.>
I understand...
As you will learn, there is really no "Best" way to do something that works for all situations, ...this is the mostly confusing part and it will only become clearer with experience.
;-)

Things like this should really only be considered *after* the DB is fully functional.
This way setting up forms instead of giving users directl access to tables and queries is not a new concept.

So again, can you explain your ultimate goal with this system, or is this system just to restrict this one user...?

JeffCoachman

Author

Commented:
I would say that my database is fully funtional.  I have some housekeeping to do and to possibly reformat some queries, but its doing what I intended it to do.  

My Ultimate goal is to share this database on our server (Originally 2 unique users, but if I can figure out the work flow better there might be more eventually.  Overall I would be the only person who would do any design work ever and he is the only one who would do data entry) which would allow him to do data entry and data correction and that is all.  I might eventually roll more things out to him as he proves capable, but for now baby steps.  

Is this enough info?  I sometimes have success with google, but this has led me on a wild goose chase.  Thanks
Luke
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<My Ultimate goal is to share this database on our server>
To be sure, is the db "Split"
Are you creating an .accde file for the end user(s)

Then as I stated, you can do this by only designing your user interface around Forms.
The you can use BCUNNEY's suggestion to determine what user is in the DB, and allow or deny access to any form.

But again, this would be "simple" security.
Meaning, a savvy or malicious user may be able to bypass it relatively easily.

And if this evolves beyond a handful of users, it will be inadequate and a more robust security model will be needed.

;-)

JeffCoachman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial