limiting table access and RWOP queries

[John Welch]

I'm securing my first mulit-user database, and I'm thinking of giving users
no permissions on tables and basing all forms on RWOP queries. This is going
to be a bit of a hassle, though, since I have several queries written
dynamically in VBA, and it seems I can't give those owner permissions. So
before I do all the work, is there a better/easier way? Should I just hide
the database window and set the AllowBypassProperty to false? The latter
option kind of intimidates me because the code to do it seems a bit messy.
thanks in advance

 

[TC]

I'm securing my first mulit-user database, and I'm thinking of giving users
no permissions on tables and basing all forms on RWOP queries. This is going
to be a bit of a hassle, though, since I have several queries written
dynamically in VBA, and it seems I can't give those owner permissions.

On the contrary, you can't *NOT* give them owner permissions! As you
clearly already know, an "owner permission" query runs with the
permissions of the user who created it, regardless of who is actually
running it. A query that is created through VBA code, does run with the
permissions of the user who created it, and in that sense, it is
/always/ an owner permission query: it can not /not/ be one!

I'm sure you meant to say that this is a hassle because you can't give
the programatically created query the permissions of some /other/ user
- not the user who created it. That is normally true - you can't. Could
you use parameterized stored queries, instead?

So before I do all the work, is there a better/easier way?

For what? Uf you are trying to stop the users going dfirectly to the
tables, then, using rwop queries is the way to go. But even then, they
could write code externally which used those queries to get at the
tables :-(

Should I just hide the database window and set the AllowBypassProperty to false?

You would normally want to do that regardless of whether you did or did
not use rwop queries. You don't want the user using the standard,
development windows or options, in a turnkey database application.

The latter
option kind of intimidates me because the code to do it seems a bit messy.

Not sure why you say that.

Finally, remember that the big thing in any serious database (which you
plan to deploy to other users) is to give it a "front end/back end"
(FE/BE) structure. Google this & other Access groups for those terms,
if you do not know what they mean.

HTH,
TC

 

[Chris Mills]

I'm securing my first mulit-user database, and I'm thinking of giving users

no permissions on tables and basing all forms on RWOP queries.

What do you want to protect exactly? And from whom? (and why? must be other
questions <g>)

RWOP queries hide (minor) details of the table design, and fields (let's say
salaries) that the average user is not entitled to. The latter is the major
reason I can see.

to be a bit of a hassle, though, since I have several queries written
dynamically in VBA, and it seems I can't give those owner permissions.

No you can't in VBA (see my recent post in the loong thread "How to prevent
importing tables", and others of course. A lot of it may apply to you)

I personally don't RWOP queries. In fact I give (a site administrator logon)
FULL permissions to the tables, so I can distribute updated s/w which might
alter the table design. Since I distribute a program and not data, I hold the
worst they can do is mess up their own data! (to be taken within reason)

is there a better/easier way?

It is all complicated. RWOP is just one arrow in the heather. I mean quiver.

Should I just hide
the database window and set the AllowBypassProperty to false?

Yes. But not "just". That's fundamental. Two more arrows shot hither.

option kind of intimidates me

All security should. That's how it works <g>
(obfuscation for the casual user, by as many means as you can manage)

Chris

(Required disclaimer: TAKE A BACKUP FIRST, AND AT EVERY STAGE)

 

[John Welch]

What do you want to protect exactly? And from whom? (and why? must be
other
questions <g>)

RWOP queries hide (minor) details of the table design, and fields (let's
say
salaries) that the average user is not entitled to. The latter is the
major
reason I can see.

I want to completely lock users out of the tables, so that they can't edit,
delete, etc except through forms. I'm not so interested in limiting the
fields they can see, I just want to make sure all their access to data is
through forms.

 

[John Welch]

Thanks for your reply TC-
I'm confused though - what you said and what I notice seem to disagree.
I'm trying to run this code:

CurrentDb.Execute ("delete * from tblLocalCompanyCityLookup")

The table tblLocalCompanyCityLookup has no permissions for members of the
users group.
If I log in as the owner of the table, the query executes. If I log in as a
'user', it says I don't have permissions on the table.
So it seems like the code query runs with user permissions to me. ???

I said I was intimidated by the allowbypass code because in reading through
the newsgroup archives, I've seen that people have had problems with
ChangePropertyDdl and I don't really understand what it's doing - setting a
property only an admin can change. ?? If I run that function, how then can
I get in as an admin, or do I just set it and then it's locked and I keep a
backup onlocked copy for development.?

thanks


----- Original Message -----
From: "TC" <[email protected]>
Newsgroups: microsoft.public.access.security
Sent: Sunday, September 11, 2005 11:48 PM
Subject: Re: limiting table access and RWOP queries

 

[Chris Mills]

A query that is created through VBA code, does run with the

permissions of the user who created it, and in that sense, it is
/always/ an owner permission query: it can not /not/ be one!

That's a bit out-of-context IMHO TC, your description is correct though
burbled (from too long underwater? <g>).

An RWOP query, in any sensible security meaning, means the owner of the
database. And that can't be created on-the-fly with VBA code, the "owner"
becomes the user in that case, which is useless and as you state.

The best I could achieve (assuming stored RWOP queries are themselves
insecure), is that RWOP SQL statement can be written, by the owner of the form
at design-time, direct into the recordsource. That can only be recovered if
they use the kindness of Jeff Conrad and Tony Toews to recover them. (I'm
learning)

Also: whereas I think there are not much design secrets in a table, there may
well be clever stuff in a query. Mine are so weird that not even I know how
they work. Seriously, I can see a good reason to hide queries, as against
tables, (or data but that depends on the reason). OTOH complex queries can be
assembled in VBA to rely on simpler RWOP queries, but then they would be
stored RWOP queries which don't seem very secure to me.

Chris
PS I don't disagree with much of what TC says, just how. I cannot speak as to
my own tongue-tied-ness. Clear as mud, as they say!

 

[Chris Mills]

I want to completely lock users out of the tables, so that they can't edit,

delete, etc except through forms. I'm not so interested in limiting the
fields they can see, I just want to make sure all their access to data is
through forms.

We would all like to do that. I can't.

You'll be aware that is impossible in Microsoft Access (and any Personal
Database System so far as I know) to lock IT-knowledgable users out.

That said, you can fairly-well lock-down Access for average users (as against
Access Gurus).

Removing Database Window, AllowBypassKeys, Default Menus, are fundamental to
limiting the average user. As important as User-Level Security, which is a
requirement too.

But you cannot prevent data being copied-out (IMHO) unless you encrypt it
somehow, for the simple reason that by definition you have decided to give
them access to the data. You can make it harder to bulk-copy by disabling
datasheet view, keyboard select all/copy, default menus, and things like that.
Harder, not impossible.
(much like the things Runtime disables, though Runtime is not of itself a
security measure)

Your statement still doesn't help much (ultimately it's impossible). It might
be more helpfull if you identified the threat (and likelihood). It has been
stated (and my experience agrees) that the majority of users just want the
software to do their jobs or make their jobs easier. They are happy to pay
including for service. This is related to whether they want to usurp your
program, rather than recover nuclear secrets, of course.

(One reason for security is solely economic. What percentage of sales am I
likely to lose from hacking, and how much effort should I spend?)

Chris

(Your requirement is impossible without more info on reasons)

 

[Chris Mills]

A user with a valid logon to "some data" and knowledge of Access and
workgroups, could potentially create a new database and link to the tables to
extract data or modify it to the same degree they have through Forms. Without
special hacking, Couldn't they? Even possibly through Excel or suchlike.

I've certainly broken into my PURCHASED Foxpro accounting database system,
simply by linking via MS-Access, and for the sole reason that the damn forms
prevented me correcting some previous mistakes. In a large corporate that
might be corruption embezzlement and uncosher. In this case it's just ME
correcting his mistakes. The only trouble is, I sent the IRS(IRD here) the
uncorrected version!

(What should I do now?<g>)
(Answer: it's not hard to find evidence, by other than MS-Access security
itself, if someone has been "mickeying")

(anon!)

 

[John Welch]

thanks again Chris,
-I'm assuming that non of my users will have the skill or motivation to
break in to the data on purpose. I just don't want them to be able to do it
accidently.I just want to figure out the best way to keep the average user
from changing data other than through my forms, or from getting lost
somewhere they don't understand.
I think next time I'll wrap all my main tables in RWOP queries, then it
looks like I can write sql in code based on these queries. But for now,
hiding the database window, allowbypass keys, and custom menus is probably
what I'll do. Once you turn off AllowBypassKeys, is it completely locked and
you have to keep a separate copy that you can get into, or is there some way
in code to get into it as an admin?

 

[Chris Mills]

what I'll do. Once you turn off AllowBypassKeys, is it completely locked and

you have to keep a separate copy that you can get into, or is there some way
in code to get into it as an admin?

An invisible button or some unknown key sequence to run unlock code will do
won't it?

 

[TC]

Chris Mills wrote:

An RWOP query, in any sensible security meaning, means the owner of the
database.

Um, it's nothing to do with the owner of the database. An RWOP query is
a query which executes with the permisions of the user who owns the
query - regardless of which user is running the query. There is no
predictable relationship between those two owners, and the owner of the
database.

RWOP SQL statement can be written, by the owner of the form
at design-time, direct into the recordsource.

SQL statements that are written into the recordsource, are executed
with the permissions of the user who is running the form - not the user
who last saved its design.

HTH,
TC

 

[TC]

Hi John

Thanks for your reply TC-
I'm confused though - what you said and what I notice seem to disagree.
I'm trying to run this code:

CurrentDb.Execute ("delete * from tblLocalCompanyCityLookup")

First, you shouldn't use currentdb() like that. You should always cache
its value in a variable:

dim db as database
set db = currentdb
db. ... whatever
set db = nothing

For supporting references, just google the groups on "currentdb cache
kaplan" without the quotes.

Second, your code is not executing a stored query. RWOP is only useful
with stored queries. Queries that are executed through code, like the
one in your example, are /always/ executed with the permissions of the
user who is running the code. In that regard, /all/ such queries are
effectively RWOP queries. You can include the "WITH OWNERACCESS OPTION"
phrase, or not, as you please, & it will make no difference to a query
that is executed through code.

The table tblLocalCompanyCityLookup has no permissions for members of the
users group.
If I log in as the owner of the table, the query executes.
If I log in as a 'user', it says I don't have permissions on the table.

As expected! The owner of an object always has (or can regain) full
access to that object. And if the 'user' in question does not have
access to the table, either directly, or through his group membership,
then: he will not get access to the table!

IOW the example you give is nothing to do with using owner-access
queries. An owner access query is only useful when (1) it is a stored
query, and (2) you carefully choose the /owner/ of the query - distinct
from the user /running/ the query.

I said I was intimidated by the allowbypass code because in reading through
the newsgroup archives, I've seen that people have had problems with
ChangePropertyDdl and I don't really understand what it's doing - setting a
property only an admin can change. ??

Yes - but don't use vague terms like "an admin"! If you set the 4th(?)
parameter of the CreaateProperty method to True, then, the only users
who can change the value of that property, are members of the Admins
group of the workgroup file which was in effect when the database was
first created.

HTH,
TC

 

[Chris Mills]

Um, it's nothing to do with the owner of the database.

I stand by my statement. Sure it's technically the owner of the object, and
implied is that I say *usually* one owner owns everything. Sure, not
necessarily. Is this a lesson in pedantics? Why did I put "in any sensible
security meaning" (which is immediately obviously opinion).

The purpose here was to differentiate the "runner" from the "owner". There
isn't much point in the "runner" writing an RWOP query (incl through VBA
code), as you correctly and pedantically state. THE POSTER ALREADY KNEW THAT!

SQL statements that are written into the recordsource, are executed
with the permissions of the user who is running the form - not the user
who last saved its design.

Please check your facts with actual testing, TC, as I did (twice now). I was
NOT referring to VBA putting the SQL statement there, in which (different)
case you are quite correct.
Alright then:
If TC owns, and writes (and saves, all while logged on as TC, have I forgotten
anything?) a form with a recordsource SQL statement incl "with owneraccess
option", and Chris logs on and runs the form, which TC has given him perms to
do, but no perms at all to the table, he(Chris) can run the form
Lickety-Splick (it's a technical term). This is not the case if VBA writes the
SQL statement "at runtime" "so-to-speak", in which case the statement is
written by the user.
OK? What is the point of an RWOP written by the user? None, as you correctly
state. But then that's what the poster was already saying.

How do I know this? I didn't. So I searched around for a PC to do an actual
test. Now I'm exhausted! (the PC ran fast and I had trouble catching it...)

Chris ;-)

 

[TC]

Chris, I'm not going to argue. The definition of an RWOP query does not
include any reference to "the owner of the database". If you think that
it does, there is little point in arguing further.

Cheers,
TC

 

[Chris Mills]

Chris, I'm not going to argue. The definition of an RWOP query does not

include any reference to "the owner of the database". If you think that
it does, there is little point in arguing further.

No it doesn't. Quite right. I never said that it does (technically), I said
"in any sensible security meaning" and you want to bullshit on about
technicalities...alright then I would have been "more" correct to say the
owner of the "Said Object". Are you happy now? In "most situations" the "Owner
of the database" is the developer and the owner of all objects. Perhaps they
are not the owner, or the developer...do you understand the term PEDANTIC? Not
to mention irrelevant truisms such as if the runner writes an RWOP they are
quite obviously the owner of it...

But let's be friends and have professional disagreement as to opinion when
necessary. My second point (that RWOP can be written into the recordsource and
works under the RWOP conditions specified, namely at owner design-time), is
pure fact (or not) and therefore anyone even you could actually test it before
spouting.

You replied too quickly to have had time to actually test it, and you
obviously haven't. Were you just engaging in professional jealousy, or
actually trying to help the poster (or your own) understanding? It is true
that I have only recently extensively tested specific aspects of RWOP myself.
You obviously haven't.

Best Regards (otherwise)
Chris

 

[Chris Mills]

Not sure if this got posted.
The first thing you have to do, when interpreting a post TC, is "Speaka Da
English" (so to speak) AKA interpret the issue. If my posts do not assist the
poster in this case, then I apologise.
----------

Chris, I'm not going to argue. The definition of an RWOP query does not
include any reference to "the owner of the database". If you think that
it does, there is little point in arguing further.

No it doesn't. Quite right. I never said that it does (technically), I said
"in any sensible security meaning" and you want to bullshit on about
technicalities...alright then I would have been "more" correct to say the
owner of the "Said Object". Are you happy now? In "most situations" the "Owner
of the database" is the developer and the owner of all objects. Perhaps they
are not the owner, or the developer...do you understand the term PEDANTIC? Not
to mention irrelevant truisms such as if the runner writes an RWOP they are
quite obviously the owner of it...

But let's be friends and have professional disagreement as to opinion when
necessary. My second point (that RWOP can be written into the recordsource and
works under the RWOP conditions specified, namely at owner design-time), is
pure fact (or not) and therefore anyone even you could actually test it before
spouting.

You replied too quickly to have had time to actually test it, and you
obviously haven't. Were you just engaging in professional jealousy, or
actually trying to help the poster (or your own) understanding? It is true
that I have only recently extensively tested specific aspects of RWOP myself.
You obviously haven't.

Best Regards (otherwise)
Chris

 

[Chris Mills]

Chris, I'm not going to argue. The definition of an RWOP query does not

include any reference to "the owner of the database". If you think that
it does, there is little point in arguing further.

No it doesn't. Quite right. I never said that it does (technically), I said
"in any sensible security meaning" and you want to rave on about
technicalities...alright then I would have been "more" correct to say the
owner of the "Said Object". Are you happy now? In "most situations" the "Owner
of the database" is the developer and the owner of all objects. Perhaps they
are not the owner, or the developer...do you understand the term PEDANTIC? Not
to mention irrelevant truisms such as if the runner writes an RWOP they are
quite obviously the owner of it...

But let's be friends and have professional disagreement as to opinion when
necessary. My second point (that RWOP can be written into the recordsource and
works under the RWOP conditions specified, namely at owner design-time), is
pure fact (or not) and therefore anyone even you could actually test it before
spouting.

You replied too quickly to have had time to actually test it, and you
obviously haven't. Were you just engaging in professional jealousy, or
actually trying to help the poster (or your own) understanding? It is true
that I have only recently extensively tested specific aspects of RWOP myself.
You obviously haven't.

Best Regards (otherwise)
Chris