RWOP /table permissions question

[Beth]

Hi,
I have a database in Access 2000, split into FE/BE and have security set on
both sides. The file is converted to an mde before shipping.
I am trying to protect the integrity of the program as much as possible as I
have some admin users trying to some bad things to the program. In doing
that I am running into what appear to be a conflict and I am hoping someone
can tell me a way around this conflict.
I have removed all queries from the database and instead put SQL statements
into the rowsource for every combobox, form, and report. To do that it
appears I have to leave data access on the backend table, which includes
read design by default. That is allowing my admin users to import the
tables into their own front end, thereby bypassing all the data validation I
have setup in my forms. It also lets them learn how my tables are setup.
On the other hand, I can remove all permissions from the tables in the
backend, but I can't use the SQL statements and have to use queries set to
RWOP. That then leaves the queries available for import to other databases.
While I don't want to give up the information about the tables, I think more
development work and intelligence goes into my queries, so I don't want to
have users see the design on those.

Am I missing something in the security setup or is there a way around this
conflict? The only answer I am seeing is to lock the tables and convert to
VB with connection strings.

Any suggestions are welcome.
Beth

 

[Keith Wilby]

Am I missing something in the security setup or is there a way around this
conflict? The only answer I am seeing is to lock the tables and convert
to VB with connection strings.

<Spanish Inquisition mode>

Yes you are missing one crucial step and that is hiding the database window
and providing custom menus ... two, two crucial steps ... I'll come in
again.

</Spanish Inquisition mode>

The steps you're missing are those you take to deny access to your design.
To do this, hide the database window, provide custom menus and create and
set the "AllowBypassKey" property (controls whether or not the SHIFT key can
be used to bypass startup settings). If you search the help for
"AllowBypassKey" you'll get the code to do just that. You can then use
Albert Kallal's utility to control the property so that you can get into the
design when you need to but keep others out:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

You'll also need to set the startup options to stop the default menus from
being available (this is where you hide the db window too). With such a
configuration your RWOP queries and linked tables are as secure as they can
be in Access.

HTH - Keith.
www.keithwilby.com

 

[Joan Wild]

In addition to Keith's comments regarding locking down the interface, remove
all permissions from the tables. Create a RWOP query for each table. Now
base all your SQL statements on these queries rather than the tables. Your
users will need appropriate permissions on the RWOP queries, but not on the
tables.

 

[Beth]

Joan and Keith,
Thank you for the comments. I have the database window hidden on the front
end and shift bypass handeld from Albert's utility.
However, can't the users with admin rights still create a new database and
import the queries and tables to see the structure?

Thanks,
Beth

 

[Joan Wild]

With Admins permissions, yes they can. Put them in a different group.

 

[Beth]

Joan,
Thank you for your responses.
I don't think I am stating everything I need to. Sorry.
They aren't in the default admin group. I moved them into my custom
"MyAdmins" group. That group has to be able to relink tables from the
different backends to the main front end, so had to give them more
privileges on the program. Those users have those permissions and are
familiar with the security file.

I think that would let them create their new file and import the objects.
Is there a way around that?

Lori

 

[Joan Wild]

Look in the security FAQ. There is code you can use to relink tables
without them needing permissions on the tables.
http://support.microsoft.com/?id=207793