What am I doing wrong

[Ian Baker]

I am trying to lock down a FE/BE A2k db with a result of locking 1 table
that contains the apps rego information but allowing access to the other
tables. The FE is to be locked down using MDE. All this without having to
distribute an mdw i.e. allowing the default system mdw to be used to use the
app but I use a seperate "Developer" mdw file for development.

The steps I took and it seems I must be doing something wrong:
Opened the FE mdb using the standard system.mdw.

Run the security wizard.

At step 1, created a new workgroup file
At step 2, saved the new workgroup file as Developer.mdw and also chose
'make a shortcut', rather than 'make it the default'
At step 3, selected all objects
At step 4, did not choose any groups
At step 5, chose 'yes, I would like to assign permissions to the Users
group', and assigned the permissions that I wanted the world to have. Using
RWOP queries in the frontend I chose Open the database, full permissions on
the tables (links), full permissions on the queries, open forms, and open
reports. I thought I would try full permissions on tables and queries then
if it worked I would back off on the table link and the RWOP query for the 1
rego table - I probably only need to use a RWOP query for the rego table).
At step 6, did not add any users at all but set a password for myself.
At step 7, did nothing as it showed that I was a member of the admins group
At step 8, verified that the location/name of my backup was suitable and
clicked Finish.

Previewed the security wizard report
Closed Access, and used the desktop shortcut to launch the mdb, logging in
with my username and password. All working ok

Closed Access and opened the mdb directly from windows explorer which means
I was logged in as Admin in the default system.mdw.
Could open the tables (links)
Could not open the RWOP queries (even though they are set to run with Owner
permissions - hence the RWOP ;-))
Err or was "Record(s) can not be read; no read permission on'tbl1'" - which
is strange seeing I can open and read the records on the tables etc and the
query was RWOP.

I am pulling my hair out on how to achieve this so if you can help then my
greatest thanks (if you are reading this Joan then this is what you told me
to do months ago so I must be doing something wrong)

 

[TC]

Ian

Howeer you do it, this is what you need to achieve.

1. The reggo table must owned by a user who does not exist in any workgroup
file except for your development one. To confirm this, join a standard
(default) workgroup file, open the BE database, & check the owner of the
reggo table. It should say "<unknown>", or somesuch. If ity doesn't, there's
one problem.

2. The standard Users group, should not have any access permissions on the
reggo table. If it does, then, any user in any default workgroup file, will
also have those permissions. Join a standard workgroup file, and
double-check that no users in that file can view the content of that table.
If they >can<, you must have got 1. or 2. wrong.

3. The RWOP query in the FE, must be owned by the same user as the reggo
table. (If it wasn't - and you did 1. and 2. right - the RWOP query would
not work.)

IMO, you have probably not achieved one or more of those things.

HTH,
TC

 

[Ian Baker]

Hi TC

1. The reggo table must owned by a user who does not exist in any workgroup
file except for your development one. To confirm this, join a standard
(default) workgroup file, open the BE database, & check the owner of the
reggo table. It should say "<unknown>", or somesuch. If ity doesn't, there's
one problem.

- When opened by the Developer mdw the database and all objects are owned by
myself
- When opened by the system mdw the database and all objects are owned by

2. The standard Users group, should not have any access permissions on the
reggo table. If it does, then, any user in any default workgroup file, will
also have those permissions. Join a standard workgroup file, and
double-check that no users in that file can view the content of that table.
If they >can<, you must have got 1. or 2. wrong.

- No they haven'y any permissions on the rego table in the BE but they do
have all permissions on the table link in the FE. This is so the users can
link the tables

3. The RWOP query in the FE, must be owned by the same user as the reggo
table. (If it wasn't - and you did 1. and 2. right - the RWOP query would
not work.)

Please see 1 above.

The other strange thing is I tried securing the entire FE (no perms to users
group) and that works fine - I can open and use the FE by the Developer link
but can't open it using the default mdw and if I open the FE using the
shortcut i.e. the Developer mdw go to File Open and open the BE step thru
the security wizard to secure the BE I can still open the BE by the default
mdw (all the security permissions and group m/ships indicate I shouldn't be
able to)

 

[Joan Wild]

I am trying to lock down a FE/BE A2k

A2000? I thought you were using 2002. Anyway, from the security FAQ:

<q>
The Access 2000 Security Wizard removes permissions to the point where they
are not visible on the security menus, but testing has revealed that in
Access 2000 it is possible to open a database by using the default workgroup
information file regardless of the menu settings.
<q>

So, open your database using the developer mdw. Create a new database and
import all the objects from your 'supposedly secure' database.

Now modify the permissions manually for the Users Group.

Repeat for the backend.

full permissions on the queries, open forms, and open
reports. I thought I would try full permissions on tables and queries then
if it worked I would back off

I would have approached it the opposite way - read data on the RWOP queries
and then only added other permissions where necessary.

 

[Ian Baker]

Thanks Joan
It seems to be working by creating the Developer mdw, creating a new empty
db whilst in Developer, import all the objects and then set the
permissions - I will just do some more testing to make sure all is well

As the app is a distributed MDE it is actually being done in A2k, A2K2 &
A2k3

Thanks again

 

[Ian Baker]

Joan
After testing all seems ok with one exception. I have in my startup form the
following line of code:
Application.SetOption "Track Name AutoCorrect Info", False
It is included to help make the app run faster along with other SetOptions
It appears that with security set this way this line will bomb out yet other
SetOptions work ok. The funniest thing is before the code I have On Error
Resume Next then after my SetOptions my error handling starts (strange)

 

[Joan Wild]

You shouldn't have to set this option, as it is stored in the database.
Once you change this setting, it will only apply to the opened database.

Therefore, there's no need to even set it.