RWOP queries for back-end secuirty: easy/hard? slow/fast?

[google]

I am creating a new database for use within our company, that I'd like
to make reasonably secure (short of a true server based solution). The
back-end of a non-server based database seems to be the biggest
security hole, due to the ease of opening, linking, and/or importing
raw data directly from the back-end database. I've read information
that indicates that locking down the back-end tables to the owner, then
using RWOP queries in the front end, helps to close this hole somewhat,
but can be problematic when creating SQL statements from code, and
other methods of querying data beyond a straightforward Access query.
I've also seen suggestions to basically create a simple "select *"
query with RWOP in the front end for each and every table in the
back-end, and then use those queries as the record source for other
queries in the front end in lieu of the tables themselves. My question
is two-fold: does this tactic completely wipe out the problems with
RWOP queries? Can I then continue to create SQL statements in code,
pointing to the RWOP queries, which in turn point to the tables in the
back-end? Does everything then work just as if I was pointing directly
to the back-end tables? Are there any performance penalties or other
drawbacks of an extra layer of query between my real (conditional)
queries and the tables? Does this method provide a real security
benefit... i.e. does it truly prevent linking or importing the raw
data, or at least provide a reasonable hindrance to the average user in
doing so?

Thanks!

 

[Joan Wild]

Extra newsgroups snipped...

I've also seen suggestions to basically create a simple
"select *" query with RWOP in the front end for each and every table
in the back-end, and then use those queries as the record source for
other queries in the front end in lieu of the tables themselves. My
question is two-fold: does this tactic completely wipe out the
problems with RWOP queries? Can I then continue to create SQL
statements in code, pointing to the RWOP queries, which in turn point
to the tables in the back-end? Does everything then work just as if
I was pointing directly to the back-end tables?

Yes, you just need to put the appropriate permissions on the RWOP queries.

Does this
method provide a real security benefit... i.e. does it truly prevent
linking or importing the raw data, or at least provide a reasonable
hindrance to the average user in doing so?

A reasonable hindrance...I would say so. You can always prevent the user
from creating new objects (section 24 of the Security FAQ), while using that
secure mdw.

 

[Wayne Gillespie]

I am creating a new database for use within our company, that I'd like
to make reasonably secure (short of a true server based solution). The
back-end of a non-server based database seems to be the biggest
security hole, due to the ease of opening, linking, and/or importing
raw data directly from the back-end database. I've read information
that indicates that locking down the back-end tables to the owner, then
using RWOP queries in the front end, helps to close this hole somewhat,
but can be problematic when creating SQL statements from code, and
other methods of querying data beyond a straightforward Access query.
I've also seen suggestions to basically create a simple "select *"
query with RWOP in the front end for each and every table in the
back-end, and then use those queries as the record source for other
queries in the front end in lieu of the tables themselves. My question
is two-fold: does this tactic completely wipe out the problems with
RWOP queries? Can I then continue to create SQL statements in code,
pointing to the RWOP queries, which in turn point to the tables in the
back-end? Does everything then work just as if I was pointing directly
to the back-end tables? Are there any performance penalties or other
drawbacks of an extra layer of query between my real (conditional)
queries and the tables? Does this method provide a real security
benefit... i.e. does it truly prevent linking or importing the raw
data, or at least provide a reasonable hindrance to the average user in
doing so?

Thanks!

I wrote this utility several years ago to convert a finished app to use RWOP
queries and set the various startup options to lockup the db. It may have
something you can use.

It is in A97 so may need converting to your version of Access.

Using COPIES (for safety) of your Fe/Be, import all objects from the utility
into your FE and run afrmLockDatabase.

www.bestfitsoftware.com.au/utilities/BestfitLockDBUtilityRWOP.zip


Wayne Gillespie
Gosford NSW Australia

 

[google]

"You can always prevent the user from creating new objects (section 24
of the Security FAQ), while using that secure mdw."

I'm afraid to say I'm a bit confused by this. I see the relevant
information in the FAQ, but the way I read that, it is code inserted
into the database (in this case, my mde) that prevents new objects from
being created within that database. Are you saying code can be
inserted and automatically run in an mdw? If so, what prevents someone
from creating a database from an unsecured mdw, closing it, then
re-opening it with my shared, secure mdw, then importing objects from
my secured database? IF code can be inserted into an mdw to prevent
them from creating new databases, can code also be created in an mdw to
prevent them from importing or creating NEW links while using that mdw?

Sorry for the stupid questions, but the though of being able to insert
permissions and other forms of control into an mdw is new to me... I
thought all it could do was keep a list of users, groups, and
passwords. If it can do more than that, then that certainly opens up
some security options.

Thanks!

 

[Joan Wild]

I'm afraid to say I'm a bit confused by this. I see the relevant
information in the FAQ, but the way I read that, it is code inserted
into the database (in this case, my mde) that prevents new objects
from being created within that database. Are you saying code can be
inserted and automatically run in an mdw? If so, what prevents
someone from creating a database from an unsecured mdw, closing it,
then re-opening it with my shared, secure mdw, then importing objects
from my secured database?

Nothing <smile> But if you have used RWOP queries, then they won't have
permissions on the tables, so can't import them (which is what you concern
was, right?). If you make a MDE, then they won't be able to see the design
of forms/reports/modules.

 

[google]

Thanks Joan. My concern with RWOP queries was whether or not there was
any performance impact in the extra layer of queries, since I'd have to
have select all queries in my MDE so that sql that I'm creating on the
fly via code will still work. But from what I've gathered, I should be
able to implement RWOP without any ill effects to the rest of my
database.

However, I'm still intrigued by idea of preventing users from creating
new databases while attached to my mdw. And I still don't follow how
to do that, as the security faq seems to be talking about preventing
users from creating objects while using my MDE, not my MDW. There's
apparently a 'hole' in that as a security measure, as you just
acknowledged, but I'd still like to understand it. How exactly do you
create permissions that are "tied" specifically to an MDW?

Thanks!

 

[Joan Wild]

Thanks Joan. My concern with RWOP queries was whether or not there
was any performance impact in the extra layer of queries

I've never noticed an impact.

However, I'm still intrigued by idea of preventing users from creating
new databases while attached to my mdw. And I still don't follow how
to do that, as the security faq seems to be talking about preventing
users from creating objects while using my MDE, not my MDW. There's
apparently a 'hole' in that as a security measure, as you just
acknowledged, but I'd still like to understand it. How exactly do you
create permissions that are "tied" specifically to an MDW?

I've never actually implemented that, but what you're doing is preventing a
user from creating objects while joined to, or using that mdw, as I
understand it. Perhaps I have it wrong, and it prevents the user from
creating objects just in that database.

OK, a bit more digging and yes you can deny the creation of a database while
using a particular mdw. Basically you set the permission on the database
container of the workgroup file (rather than the current database).

 

[google]

Thanks for digging in to that! Although I'm afraid to say I don't know
how to reference the mdw's container. And another part that confuses
me is, the security faq says that the database has to be compiled as an
mde to be able to set permissions for creating objects. Obviously,
it's not the mdw that's to be compiled to an mde, so does this mean
that just SOME (any) mde is used to access and set a property of the
mdw, and that property "sticks" in the mdw until we use code to change
that property? In other words, an mdw actually DOES have permission
properties, but those properties can only be accessed and changed via
code from a separate mde file? If I'm understanding this correctly,
then it seems that I could have my own compiled database that I keep on
my computer (not shared on the network for everyone to have access to)
that is used strictly for accessing and changing these attributes of
the shared mdw. And it seems to me that I would have to manually set
these permissions on a user by user basis... or could I assign these
permissions (or lack of permissions) to an entire group? Does a new
user automatically have "create database" permissions individually that
would override any group settings?

The obvious question is, why do I care if I'm going to set tables to
owner permissions only? And the answer is: I simply like throwing as
many speed bumps at a potential snooper as I can. There's no way I can
make a desktop database completely secure, so the best I can do is make
it as troublesome as possible, in the hopes that the vast majority of
potential snoopers decide it's just not worth their time.

Sorry for the dumb questions - as I said before, the concept of
permissions, or any other editable attribute, of a workgroup file, is
new to me.

 

[Joan Wild]

--
Joan Wild
Microsoft Access MVP

Thanks for digging in to that! Although I'm afraid to say I don't
know how to reference the mdw's container.

Instead of Set db = Currentdb()
Set db =
DBEngine.Workspaces(0).OpenDatabase(SysCmd(acSysCmdGetWorkgroupFile))
Then set the container to
db.Containers!Databases

And another part that
confuses me is, the security faq says that the database has to be
compiled as an mde to be able to set permissions for creating
objects.

Where? Not in my copy.

Does a new user automatically have
"create database" permissions individually that would override any
group settings?

Sorry for the dumb questions - as I said before, the concept of
permissions, or any other editable attribute, of a workgroup file, is
new to me.

I really haven't played with this enough to answer your questions. You
could test this out yourself, and hopefully come back and share what you've
found?

 

[google]

Thank you again Joan for the help with the code!

"Where? Not in my copy."

Admittedly, I may be using the wrong version. The only one I've ever
been able to find is the one listed specifically for versions 2.0
through 2000 here:
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299693
I've searched for "security faq" in the Access 2003 knowledge base,
and only get two returns (neither of which is a security faq). I'm
sure there's a newer one that I'm just not able to find, but to answer
your question, in the older one, under section 24, it says: "There is
no way to remove permission to create forms, reports, macros, or
modules in Microsoft Access unless you compile your database as an MDE
in Access 97 or Access 2000." Of course, the letter of that caveat is
limited to forms, reports, macros, and modules, but that is all that
the paragraph insinuates that you can limit: it never mentions the
ability to restrict database creation that you've been so kind to shed
light on. But again, perhaps I'm not looking at the CORRECT security
faq.

"You could test this out yourself, and hopefully come back and share
what you've found?"

Yes, I will have to do that. Obviously the "hole" mentioned earlier
prohibits this from being a true security measure, but like most of the
other "security" options in Access, it could be one more deterrent to
make a snooper decide it's just not worth the time.

 

[Joan Wild]

Thank you again Joan for the help with the code!

"Where? Not in my copy."

Admittedly, I may be using the wrong version. The only one I've ever
been able to find is the one listed specifically for versions 2.0
through 2000 here:
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299693

Nope that's the latest.

your question, in the older one, under section 24, it says: "There is
no way to remove permission to create forms, reports, macros, or
modules in Microsoft Access unless you compile your database as an MDE
in Access 97 or Access 2000."

Well that really isn't about our discussion. That's referring to a MDE.
Create a MDE, and users can't import those objects. It doesn't mean that it
must be a MDE to prevent creation of a new database.

Yes, I will have to do that. Obviously the "hole" mentioned earlier
prohibits this from being a true security measure, but like most of
the other "security" options in Access, it could be one more
deterrent to make a snooper decide it's just not worth the time.

Indeed, that's the best you can do. It usually is sufficient for most users
of Access. I've never come across one that wanted to. They seem happy to
have a tool that helps them do their job.