Secured database problems

[Richardson]

Since I got a good answer to my last question, here's another one.
My database is split into FE/BE. I have several action queries that I need
to run in the front end.
I don't want to give my users the ability to see the design of the tables
(or queries) in any location (including importing them to another database).
I tried removing permissions from the tables and setting the permissions to
Owner's in all my queries, but Access keeps giving me errors saying that
read permissions were not set for the necessary tables/queries. My
understanding was that by setting RWOP it was not necessary to offer read
permission to the user for the tables.
I have reached the point of total frustration and I hope you can tell me I
am overlooking something simple and I can get this working tonight.

Thanks in advance.

Lori

 

[Jeff Conrad]

You're not overlooking something simple. Setting up Access
User Level Security *properly* is a challenging task to
say the least.

I believe you need to do this:

1. In the BE remove all permissions for the tables except
for you (the super-user). Give yourself Full Permissions
to everything.

2. Delete all links in the FE to the BE.

3. Re-link all the tables while logged in as you just to
be sure.

4. Now set on all the TABLE LINKS in the FE for your
custom group(s) to "Modify Design." To quote the SEC FAQ:

"The minimum permissions for a linked table require Modify
Design permission on the linked table."

5. Make sure you have the permission set to Owner in all
the queries you deem necessary. I'm actually working
through this myself as well. I found it easier to create
one and ONLY one RWOP for each linked table. Then each of
my other saved queries reference those RWOP queries. To
make it easier to remember I named all those RWOP queries
like so:

BE Table: FE RWOP Query
tblAppointments qryTblAppointments
tblSchedule qryTblSchedule
etc...

It makes it so much easier I think.

6. Using this setup your users will not be able to link to
or import your tables into another database.

7. This is just one of many steps to make sure your other
users are completely locked out though! Disable the shift
key (as you are working I see), set up custom menu
bars/toolbars, hide the Database Window, set up custom
forms for adding/deleting users and changing passwords,
distribute MDEs, etc.

Not knowing what reading materials you have I'll offer
this list which will be of GREAT help in properly setting
up User Level Security:

-Download the Security FAQ here (the Security Bible):
http://support.microsoft.com/?kbid=207793

-Download Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd/AccessSecurity.html

Read Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm

I also found the security chapter in the Access
Developer's Handbook very useful:
http://www.developershandbook.com

 

[TC]

The fundamental issue with RWOP queries is that the >RWOP query< must be
owned by a user who >does have< the required level of access to the relevant
tables.

It is way not sufficient to just set RWOP in an existing query, without
addressing the ownership issue noted above.

HTH,
TC

 

[Richardson]

Jeff,
Thank you for the advice. As far as I can tell, I have gone through exactly
what you listed. Everything is working fine except that I can still go to
new blank database when logged on as a regular user and link to the tables
since they have read design permissions. I will read through the links you
gave me (and mark them for future reference).
On disabling the shift key, I put in the code from your other post and it
disables the shift key, but it does so for all logins. I went ahead and
disabled the shift key entirely, waited until I was ready to distribute the
front end, and then set the startup up options to use custom toolbars, hide
the database window etc. The problem is that any changes I make for future
distribution have to be done on a copy that completely replaces the previous
front end instead of being able to go back and modify the distributed one.
I really appreciate your help.
Lori

 

[Jeff Conrad]

Hi Lori,

Thank you for the advice. As far as I can tell, I have
gone through exactly what you listed. Everything is
working fine except that I can still go to new blank
database when logged on as a regular user and link to the
tables since they have read design permissions.

Another step or two has been missed on securing the back
end tables then. Triple check to make sure that the built-
in Access group called "Users" have NO permissions to the
tables whatsoever (or anything at all period!). I would
also make sure the group called "Admins" has NO
permissions on anything as well. You should remove the
Admin USER from the "Admin" groups as well. That user
should only be part of the "Users" group. Your super-user
(you) should be the owner of all objects and be part of a
custom group that "controls everything."

I will read through the links you gave me (and mark them
for future reference).

Definitely! The links will help a lot.

On disabling the shift key, I put in the code from your
other post and it disables the shift key, but it does so
for all logins.

Not quite sure I follow you there. Are you saying it
disables the shift key for all databases?? It should not
do that at all. Or are you saying that all users of this
particular database with a specific MDW file cannot hold
down the shift key and get in? That's what the intent is.
Everyone will not be able to use the shift key bypass
UNLESS you use Albert's nifty utility to unlock the door
so to speak. If you're thinking that you log in with your
User Name and Password and can hold down the shift key any
time you'd like, that's not the case. You have to run the
code again to unlock the door.

I went ahead and disabled the shift key entirely, waited
until I was ready to distribute the front end, and then
set the startup up options to use custom toolbars, hide
the database window etc.

I would leave everything open while your still working on
the program just so it is easier for you. When you're
ready to deploy it to your users, then lock up everything
tight.

The problem is that any changes I make for future
distribution have to be done on a copy that completely
replaces the previous front end instead of being able to
go back and modify the distributed one.
I really appreciate your help.

Well assuming I'm understanding you correctly this is OK.
It would be best to distribute MDE files to your users
while keeping several backup copies of the MDB file. You
give each user a locked up MDE file linked to the secured
BE so they don't mess up anything. You continue to work on
adding things in a development MDB. When you're ready to
deploy an update you simply delete their existing MDE and
replace it with another updated, locked up MDE.

**If you make changes to the BE table structure you will
need to make sure NO one is using the BE before deploying
a new one on the server.**

MVP Tony Toews has a utilty used to update user's FE
databases with new versions. I have not used this myself,
but have heard good things about it. You can find that
here:

http://www.granite.ab.ca/access/autofe.htm

Good luck,
Jeff Conrad
Access Junkie
Bend, Oregon