Re: Secured database problems

From: Jeff Conrad (jeffc_at_ernstbrothers.com)
Date: 04/10/04 

Date: Fri, 9 Apr 2004 19:24:25 -0700

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 

--
Jeff Conrad
Access Junkie
Bend, Oregon
>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

Relevant Pages

  • Re: User has group permissions to object, but still denied access.
    ... permissions to those query objects. ... The owners for the other 68 queries can be changed. ... may have been some of the original queries created in the database. ... Regarding the security, the database was converted from an Access 97 ...
    (microsoft.public.access.security)
  • Re: Creating security for MS Access application
    ... I wanted to create user ids and grant permissions based on user ids. ... You've not properly secured your database if that's the case. ... of User Level Security before mucking around with it too much more. ... with instructions on how to properly secure a database without the Security Wizard: ...
    (microsoft.public.access.security)
  • Re: A note to add to ASPFAQ.com for database compacting
    ... I set the database directory permission to give full access to ... then checked "Allow inheritable permissions from parent to ... Thus your problem is that you have to high of security set ... > the files needed and also a compact & repair operation will not cause ...
    (microsoft.public.inetserver.asp.db)
  • Sorting out security
    ... MS Access security. ... created a new workgroup, added a password for the Admin role, added groups, ... user IDs and passwords for users and allocated permissions on the ... remote logins to a secured database. ...
    (microsoft.public.access.security)
  • RE: Security setup does not allow import of tables
    ... When you click on the menu option Tools> Security> User and Group ... Permissions at the bottom of the screen does it show you logged in as the ... | new system database that has a unique Name, Organization, ... In the User and Group Accounts dialog box, ...
    (microsoft.public.access.security)