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!

[Bri]

Comments in-line

go****@darincline.com wrote:

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?
Yes, the RWOP query can be used in other queries the same way that you
would use a table.

I also create RWOP queries for situations where either some fields or
some records are restricted for certain user groups.

Field restriction example; if only Managers are allowed to see Employee
Pay amounts, but everyone needs to know what PayGroup the Employee is
in. Create two RWOP queries:

rwopPayManager: SELECT EmpID, FromDate, ToDate, PayGroup, BasePay
FROM Pay
rwopPayEmployee: SELECT EmpID, FromDate, ToDate, PayGroup
FROM Pay

Then you can give the Manager group permissions to rwopPayManager and
everyone permission to rwopPayEmployee. The Report or Form (in Open
Event) then needs to check the currentuser to see if they are in the
Manager group and then pick one query or the other as the RecordSource.

Record restriction example; If a user is in the Supervisor Group then
they need to see only the Employees that they supervise:

rwopEmployeeSupervisor: SELECT * FROM Employee
WHERE SupervisorID=CurrentUser()
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?
Yes
Does everything then work just as if I was pointing directly
to the back-end tables?
Yes
Are there any performance penalties or other
drawbacks of an extra layer of query between my real (conditional)
queries and the tables?
Not that I've ever noticed. It might be possible in the case of linked
ODBC tables that the extra layer could be the last straw that causes
Access to decide that the query is too complicated to send to the server
and so decides to pull all the data to run the query locally. You would
have to test each query to see. It is usually VERY obvious when this
happens. A query will take an order of magnitude (or two!) longer to run
than it should.
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?

If someone links to the backend tables outside of your app, they will
not have permissions to the tables. Without cracking the Workgroup
security, there should be no way in (assuming that all of the proper
steps to secure it have been done).

All this being said, I have never done an app where I did this with
every table. I do it on all of the tables that require specific security
that can't be handled by the standard User/Group permissions. Remember
to apply the permissions to the BE MDB as well as to the Links in the
FE. Putting permissions only on the links will not secure the BE from
being opened or linked, that must be done separately.

--
Bri

[google]

Thank you very much for your reply! It sounds like RWOP queries (in
conjunction with appropriate user level security, of course) are a good
way to reasonably secure a back-end. Considering that I do
occasionally have some very hairy queries, and sometimes query data via
code, I wanted to make sure that implementing that wouldn't break
anything before going down that road. It sounds like I could simply
re-name all my table link aliases in the FE, then create RWOP queries
based on those tables with the same names as the table links USED to
be, and all the code/queries/recordsets should run as it was before?

Obviously, any time there is a simpler method, that always gets the
nod. With that in mind, doing some research in the newsgroups turned
up a post by Joan Wild that interested me. The thread/post is too old
to reply to directly, but it can be found with the following search:

http://groups.google.com/group/micro...3b208744c74661

In that post, she suggests simply removing a user's ability to create
new databases using the workgroup file that is associated with the
database I'm trying to secure. If I prevent a user from directly
opening up the back-end database (through autoexec code and disabling
the shift-bypass), and if users are restricted from creating NEW
databases based on the same workgroup file (therefore not being able to
create a database that has access to data within the back-end), then it
SEEMS to me that this would also be relatively secure, while being much
simpler than creating and using all the RWOP queries in the front end.
However, I don't understand how this can be done. I've looked at the
relevant info in the MS Security FAQ, but that seems to talk about
restricting users of creating new ojects in the database that you
insert the code into. But what prevents a user from simply joining the
shared workgroup file, opening up Access, and creating a new database
without ever opening up the FrontEnd.mde file that contains the code to
restrict creating new objects?

[Wayne Gillespie]

On 10 Feb 2006 06:11:57 -0800, go****@darincline.com wrote:

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.

http://www.bestfitsoftware.com.au/ut...tilityRWOP.zip
Wayne Gillespie
Gosford NSW Australia

[David W. Fenton]

go****@darincline.com wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

Thank you very much for your reply! It sounds like RWOP queries
(in conjunction with appropriate user level security, of course)
are a good way to reasonably secure a back-end. Considering that
I do occasionally have some very hairy queries, and sometimes
query data via code, I wanted to make sure that implementing that
wouldn't break anything before going down that road. It sounds
like I could simply re-name all my table link aliases in the FE,
then create RWOP queries based on those tables with the same names
as the table links USED to be, and all the code/queries/recordsets
should run as it was before?

You could remove the linked tables entirely and use the IN operator
of the RWOP queries, which allow you to query directly from other
data sources without linked tables. However, those aren't easy to
update if you have to have more than one back end location (they can
only be updated qy editing the QueryDef in code using a workspace
with a logon/password that has permission to edit the RWOP
QueryDefs).

I wouldn't name queries the same as tables, though. That makes
things very confusing in terms of maintenance later on. I'd leave
the tables as is and use standard naming conventions for the
queries.

Of course, if you're not using any naming conventions, that's moot
-- I was assuming tables starting with tbl and queries with qry, for
instance.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

go****@darincline.com wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

If I prevent a user from directly
opening up the back-end database (through autoexec code and
disabling the shift-bypass), and if users are restricted from
creating NEW databases based on the same workgroup file

I know nothing about this latter capability. Where are you getting
that information from?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[google]

"I know nothing about this latter capability. Where are you getting
that information from?"

See my link in the same post. It was something Joan Wild stated, but
I've seen it elsewhere too. I just don't know how to do it, as I
thought a workgroup file only kept track of users/groups, not
pemissions.

[David W. Fenton]

go****@darincline.com wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

"I know nothing about this latter capability. Where are you
getting that information from?"

See my link in the same post. It was something Joan Wild stated,
but I've seen it elsewhere too. I just don't know how to do it,
as I thought a workgroup file only kept track of users/groups, not
pemissions.

The URL did not take me to the article in question, but to the
thread. I didn't feel like wading through it to find out what you
were talking about.

I've done so now, and see no basis for Joan's comment. I have a lot
of respect for Joan's expertise, but I have no idea how to set up an
MDW file in a way that prohibits the creation of new databases.
Indeed, my understanding of Jet security (perhaps imperfect) would
lead me to believe that it's not possible, as the MDW includes only
the account and group information, and none of the actual
permissions, which are all stored in particular MDB files. If the
user creates a new MDB (which cannot be prevented in an MDW), then
they are the owner of it and have full permission on all of its
objects.

All that said, I think you're off on a red herring, in any case. The
ability to create a new database does not give someone the ability
to import data they've got no permissions on. They could perhaps
link to those tables, but still not be able to see anything, unless
they are using software to crack Jet user-level security. If you're
willing to accept that (which I consider reasonable, as any security
system is crackable by someone with the interest and the time), then
I think you should stop worrying about this side issue.

Now, of course, if someone can explain how Joan's comment is
correct, then that's a different issue entirely.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[google]

Sorry about the link not taking you directly there... it works for me,
so I'm not sure what's up with that. Anyway, I just realized that Joan
had responded to my post in another forum:

http://groups.google.com/group/micro...49b476a977d43b

It's a short one, so easy to find her comment. :)

Anyway, the reason I'm interested is because if what she's describing
CAN be implemented, then that sounds simpler than implementing RWOP
queries, or maybe implementing both provides enough "dead ends" that a
would be snooper will eventually decide it's not worth investing more
time. As I posted in that thread, the only way I can imagine that
being doable would be if code can be inserted and automatically run
into an mdw. I know an mdw can be opened up like a regular database,
but if code can be inserted into one, that opens up a whole new avenue
of automation and protection I didn't know existed. Whether it's a
good option or not, I'd at least like to understand it. :-)

[David W. Fenton]

go****@darincline.com wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Sorry about the link not taking you directly there... it works for
me, so I'm not sure what's up with that. Anyway, I just realized
that Joan had responded to my post in another forum:

http://groups.google.com/group/micro...s.security/bro
wse_frm/thread/d3b1a1bdef9bc778/bd49b476a977d43b?hl=en#bd49b476a977
d43b

It's a short one, so easy to find her comment. :)

Anyway, the reason I'm interested is because if what she's
describing CAN be implemented, then that sounds simpler than
implementing RWOP queries, . . .
Eh?

So far as I can see, the only way to implement back-end user-level
security that prevents viewing/importing of data by users but still
permits a user to view/edit data in the front end is by using RWOP
queries.
. . . or maybe implementing both provides enough "dead ends" that
a would be snooper will eventually decide it's not worth investing
more time. As I posted in that thread, the only way I can imagine
that being doable would be if code can be inserted and
automatically run into an mdw. I know an mdw can be opened up
like a regular database, but if code can be inserted into one,
that opens up a whole new avenue of automation and protection I
didn't know existed. Whether it's a good option or not, I'd at
least like to understand it. :-)

I really don't think you have any real understanding of Jet
user-level security.

And Joan's original comment is still not explained by her reply
there. It says nothing about being able to prevent the creation of
new databases. All it describes is applying security to prevent a
user from viewing or importing data from a back end.

Which is what you'd been instructed in doing in the previous
messages in this thread.

1. secure the back end with user-level security, denying read (and
all other) permission on the tables.

2. link to the tables in the front end and use RWOP queries to allow
users to view/edit the data in the secured back end.

There is no other way to do this if you're going to deny users read
access on the back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[google]

"So far as I can see, the only way to implement back-end user-level
security that prevents viewing/importing of data by users but still
permits a user to view/edit data in the front end is by using RWOP
queries."

That is my understanding as well. Which is exactly why I'm trying to
understand Joan's comment.

"I really don't think you have any real understanding of Jet user-level
security."

I never made any claims to have a complete understanding of anything.
As I stated before, I THOUGHT that the workgroup file only kept track
of users, and validating them. I thought that permissions had to be
set within each database, based on the user that the workgroup file
validated. If you'd like to correct me, that's why I'm here asking
questions... to learn. I don't understand how to prevent someone from
creating a new database while logged in with a specific mdw. I didn't
know that was even possible. Surely you can understand my curiosity if
something is presented that I don't understand.

"And Joan's original comment is still not explained by her reply
there. It says nothing about being able to prevent the creation of
new databases."

That's EXACTLY what she says. Someone asked:

"how do you prevent importing of tables from a secured file?"

And her reply was:

"As I suggested elsethread, remove their ability to create a new
database
while using the secured mdw. "

It seems pretty clear to me. And as I mentioned earlier, I've even
seen evidence elsewhere that this can be done. Take a look towards the
bottom of the post here:

http://www.usenet-archive.de/comp.da..._frontend_.php

"Mark" claimed to have disabled users' ability to create databases
while using his workgroup file (though he had a problem with them
working around that), and Keith Wilby, who I think knows a thing or two
about jet security, seemed to agree that they shouldn't be able to.
I'm simply trying to understand how this can be done.

"There is no other way to do this if you're going to deny users read
access on the back end."

Yes, using RWOP queries is the only way I understand to prevent "easy"
access to the data. I was a bit concerned both about the effect of the
added layer of queries, as well as the possibility that some existing
code may stop working. Bri's response helped ease my concern. But if
there ARE other methods, I'd like to learn about them. As Joan said in
the other thread: "If they can't create a new database while joined to
your secure mdw then how will they be able to import anything?" If
that is indeed possible, I'd like to understand that. I don't
understand why my curiosity must be met with such condescension.

[David W. Fenton]

go****@darincline.com wrote in
news:11*********************@o13g2000cwo.googlegro ups.com:

"So far as I can see, the only way to implement back-end
user-level security that prevents viewing/importing of data by
users but still permits a user to view/edit data in the front end
is by using RWOP queries."

That is my understanding as well. Which is exactly why I'm trying
to understand Joan's comment.
I'm quite puzzled as to the discussion you're citing here. It seems
to me that a lot of different things are going on that aren't really
relevant to your specific question about Joan's quetionable comment.
"I really don't think you have any real understanding of Jet
user-level security."

I never made any claims to have a complete understanding of
anything. As I stated before, I THOUGHT that the workgroup file
only kept track of users, and validating them. I thought that
permissions had to be set within each database, based on the user
that the workgroup file validated. . . .
That is certainly correct. That is why I can make no sense out of
Joan's claim of preventing the creation of databases with a
particular MDW file -- it can't be done, unless there are aspects of
Jet user-level security that are left out of all the documentation
on the subject that I've ever seen.
. . . If you'd like to correct me, that's why I'm here asking
questions... to learn. I don't understand how to prevent someone
from creating a new database while logged in with a specific mdw.
. . .
As I said, it can't be done. Joan's remark makes no sense to me, and
the thread you referred to does not in any way address the original
assertion -- it's about importing, not the creation of fresh MDBs.
. . . I didn't
know that was even possible. Surely you can understand my
curiosity if something is presented that I don't understand.

"And Joan's original comment is still not explained by her reply
there. It says nothing about being able to prevent the creation of
new databases."

That's EXACTLY what she says. Someone asked:

"how do you prevent importing of tables from a secured file?"

And her reply was:

"As I suggested elsethread, remove their ability to create a new
database
while using the secured mdw. "

It seems pretty clear to me. And as I mentioned earlier, I've
even seen evidence elsewhere that this can be done. Take a look
towards the bottom of the post here:
But she offers no explanation of how to prohibit the creation of a
new database, only of how to prevent someone from importing secured
tables into a new MDB. I think the ability to accomplish the latter
makes the former completely irrelevant.
http://www.usenet-archive.de/comp.da.../114736-Re_Imp
orting_from_secured_frontend_.php

"Mark" claimed to have disabled users' ability to create databases
while using his workgroup file (though he had a problem with them
working around that), and Keith Wilby, who I think knows a thing
or two about jet security, seemed to agree that they shouldn't be
able to. I'm simply trying to understand how this can be done.
I don't know that there's any way to do it. So far, no one has
supplied a set of instructions for accomplishing this task.
"There is no other way to do this if you're going to deny users
read access on the back end."

Yes, using RWOP queries is the only way I understand to prevent
"easy" access to the data. I was a bit concerned both about the
effect of the added layer of queries, as well as the possibility
that some existing code may stop working. Bri's response helped
ease my concern. But if there ARE other methods, I'd like to
learn about them. As Joan said in the other thread: "If they
can't create a new database while joined to your secure mdw then
how will they be able to import anything?" If that is indeed
possible, I'd like to understand that. I don't understand why my
curiosity must be met with such condescension.

I don't have a clue what Joan is talking about there. If she is
right, then it is *me* who knows nothing about Jet user-level
security.

I guess that's certainly possible, but given all the work I've done
with it over the years, it seems quite unlikely to me.

You described it correctly above, that the MDW holds the users and
groups, and the permissions are in the MDBs. There is no way to set
an MDW to control permissions of any kind so far as I'm aware.

I'd be glad to be enlightened, but I don't see that there would be
any point to having this capability, as it does nothing to protect
your data beyond what securing your actual tables accomplishes. If
the tables are secured, they can't be imported or linked to, so what
difference does it make for circumventing security if the user can
create an MDB file? They can't get at the secured data with that
newly-created MDB file, so how would preventing its creation enhance
security in any way?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Joan Wild]

Hi David, I first learned of this from the ADH. There is a dbSecDBCreate
permission, which you can set on the Database container of the *workgroup*
file. All the code and explanation can be found in the Securing chapter.

--
Joan Wild
Microsoft Access MVP

David W. Fenton wrote:

That is certainly correct. That is why I can make no sense out of
Joan's claim of preventing the creation of databases with a
particular MDW file -- it can't be done, unless there are aspects of
Jet user-level security that are left out of all the documentation
on the subject that I've ever seen.

[David W. Fenton]

"Joan Wild" <jw***@nospamtyenet.com> wrote in
news:11*************@corp.supernews.com:

David W. Fenton wrote:

That is certainly correct. That is why I can make no sense out of
Joan's claim of preventing the creation of databases with a
particular MDW file -- it can't be done, unless there are aspects
of Jet user-level security that are left out of all the
documentation

I first learned of this from the ADH. There is a dbSecDBCreate
permission, which you can set on the Database container of the
*workgroup* file. All the code and explanation can be found in
the Securing chapter.

Two questions:

1. what purpose does this server in terms of security? Yes, it
limits what people can do, but it doesn't do anything at all to
protect existing secured data.

2. what happened that this very clear answer of yours is nowhere to
be seen in the Google Groups archive where this question was asked
of you before?

I know you know your stuff, Joan, so that's why it was all rather
mystifying. From anyone else, I just would have dismissed it out of
hand.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Joan Wild]

David W. Fenton wrote:

"Joan Wild" <jw***@nospamtyenet.com> wrote in
news:11*************@corp.supernews.com:

I first learned of this from the ADH. There is a dbSecDBCreate
permission, which you can set on the Database container of the
*workgroup* file. All the code and explanation can be found in
the Securing chapter.
Two questions:

1. what purpose does this server in terms of security? Yes, it
limits what people can do, but it doesn't do anything at all to
protect existing secured data.

I was responding to darincline, who wanted to know if it could be done. A
user can open a secure mdb, then create a new database and import objects
from the secure database. But if they can't create a new database while
joined to the secure mdw, then it's another roadblock. Of course, it's all
academic, given the state of Access security. Even the above can be gotten
around without the security crackers available for download.

2. what happened that this very clear answer of yours is nowhere to
be seen in the Google Groups archive where this question was asked
of you before?

Beats me - ask Google.

--
Joan Wild
Microsoft Access MVP