In order to use an RWOP query to insert or delete data from certain tables
which users have read only permission on I find myself needing to use
parameters in my query. The user has to have open/run permission on the
query itself and my intention is to set the value of the parameters in code
by using values from a form whose data is restricted so that the user can
only insert or delete records related to records meeting certain criteria.

This works fine but then I realized that the user could just open the query
directly (if they can get to it) and fill in the parameters manually,
thereby bypassing the security that I've tried to provide. Is a RWOP query
with parameters considered secure? It seems to me that it opens a security
hole. Is there some other way to accomplish what I need to do?

What sort of parameters?
You can refer to a field on a form in SQL by, say:
WHERE something = [Forms]![MyForm]![MyField]

Then you change or set the data in that form field and requery.

I'm not sure if you'd call that a "parameter query", since I take "parameter
query" to mean asking the operator directly for something when the query is
run. (per the Help)

The SQL statement therefore doesn't need "changing". It's very dangerous to
change or write RWOP queries by code, because the "owner" becomes whoever is
logged on at the time the code writes the RWOP, which kinda defeats the
purpose of it.

I don't believe "parameters" are appropriate, or not the right term, unless
you DID want to request some entry direct from the operator, as against the
operator entering it somewhere on a form/unbound field and the query picking
that up.

Chris

"Tom Stoddard" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In order to use an RWOP query to insert or delete data from certain tables
> which users have read only permission on I find myself needing to use
> parameters in my query. The user has to have open/run permission on the
> query itself and my intention is to set the value of the parameters in code
> by using values from a form whose data is restricted so that the user can
> only insert or delete records related to records meeting certain criteria.
>
> This works fine but then I realized that the user could just open the query
> directly (if they can get to it) and fill in the parameters manually,
> thereby bypassing the security that I've tried to provide. Is a RWOP query
> with parameters considered secure? It seems to me that it opens a security
> hole. Is there some other way to accomplish what I need to do?
>
>
>

> What sort of parameters?
> You can refer to a field on a form in SQL by, say:
> WHERE something = [Forms]![MyForm]![MyField]

In your example, [Forms]![MyForm]![MyField] is a parameter of sorts. If a
user ran your query when the form was not open, they would be prompted for
that value. They could type it in directly and the query would run even when
the form is not open. When the form is open, Access is able to provide the
value itself, if it can't evaluate the parameter itself it will prompt the
user. You can provide those values in code before you execute the query so
that Access won't prompt the user. The idea of providing those parameters in
code is that you're not limited to fields on a form. For example, I can set
a value for a parameter with a value I've found in a recordset that I have
open in the procedure I'm running. I have found that certain queries will
not work when I put references to controls in the query grid and then try to
execute them using code but they will when I create a parameter and provide
its value in code. I create the parameter by putting a name inside of
brackets in the query grid. Try creating a simple query and putting [Value1]
in the criteria of a field in the query grid and then opening the query.
You'll be prompted to provide a value. In code I can set that value by using
this code: CurrentDb.QueryDefs("qryMyQuery").Parameters("Value1") =
Something. Then I can do: CurrentDb.QueryDefs("qryMyQuery").Execute and the
query will run.



>
> The SQL statement therefore doesn't need "changing". It's very dangerous
> to
> change or write RWOP queries by code, because the "owner" becomes whoever
> is
> logged on at the time the code writes the RWOP, which kinda defeats the
> purpose of it.
>
According to the security faq, RWOP is ignored if the query is defined in
code. It will only work if its part of a saved query.

I'm doing this because I want to add a record to a table that the user
doesn't have insert permissions on. The query that the form is based on is
not updateable so I've created an Append query with RWOP and added
parameters and then saved the query. I'm trying to execute it in code behind
the form. It works well in one instance but I'm having trouble getting to
work on another form I'm working on now. For some reason the record isn't
getting added and I can't figure out if its due to the query or security or
something I haven't thought of yet.

Thanks

> In your example, [Forms]![MyForm]![MyField] is a parameter of sorts.

Well, yes. If the form is not open then the operator is asked, certainly.
Nevertheless, it's one way of automatically passing a parameter, and works
fine.

Of course, such a query is only designed to be run with the referenced form
open. It could, for instance, be stored in the Recordsource of the form and
reference Me!MyField.

> The idea of providing those parameters in
> code is that you're not limited to fields on a form.

I think it's easier to provide them via a form. I think if you're re-writing
an RWOP via code then you stand the danger of altering the "owner". You don't
have a lot of control over that.

You could have a basic RWOP stored of course, and a further parameter query
based on that.

I have a feeling that your manipulation of Querydefs looks to me far too
complicated, when as I understand it all you want to do is alter the WHERE
parameter data, much like a filter. If it's associated (run from) a form, then
the form will be open won't it?

> According to the security faq, RWOP is ignored if the query is defined in
> code. It will only work if its part of a saved query.

Yes. Writing through code is pointless. However I did find one thing (you can
test it yourself). If you write a SQL statement in the Recordsource and save
the form design, that SQL statement appears to store as the owner, who you
were logged on as when you saved it (which would normally be the developer or
"owner", whoever). But as soon as you re-write the RWOP recordsource through
code, well it's re-written, again with whoever was logged on at the time, in
this case the user becomes the "owner". So there is a case where the RWOP does
not have to be stored (as a separate query). Of course, there's a potential
danger in this, in that it is not immediately obvious who the owner is, so it
would be easy to make an inadvertent mistake.

I'm not really sure what your issue is. If a query is stored then certainly a
user could just run the query. That's why you lock-down the database with
AllowBypassKeys, to remove such a view. I have given a special case,
apparently not well-known, where an RWOP can be stored directly in a
Recordsource. Depending on the circumstances of course, but I don't have a
great pressing reason to use RWOP. Using a form field for a parameter works
great. It is possible, that if "they" have as much Access knowledge as you
seem to be attempting to prevent, you may be wasting your time whatever you
do. To me, all this stuff about "parameter passing" is for operator
convenience and a nice-working form. If they can get to a stored RWOP query,
then they can run it, yes. That's how Access works. I suspect there's a point,
with Access, where you may be trying to be "too smart" as it were. But let
others give their ideas, by all means.

Chris

I've got to this one before the email one :-)

Remember that the purpose of an RWOP query is to let the user access
objects to which he normally does not have access. And the purpose of a
parameterized query is to let the caller pass one or more values, at
runtime, which affect how the query works, in some way defined by the
person who wrote the query.

You can put those two things together & they will both work 100%
correctly. The result will be, a query that lets the user access
objects to which he normally does not have access, and which accepts
one or more values at runtime which affect how the query works, in some
way defined by the person who wrote the query.

So, if you write the query in such a way that people can select
inappropriate data by passing the right parameter values, the problem
is how you wrote the query - it's nothing wrong with RWOP, or
parameterized, queries.

HTH,
TC

PS. One for you, Tom: why can't a person who uses an RWOP query, just
edit the query SQL directly, so he can can do /any/ data retrieval or
manipulation that the owner of the query can do? (Not just the specific
data retrieval or manipulation that the query owner wrote it for
originally?)

I know the answer already. I just feel you're going to be asking this
soon :-)

Cheers,
TC

Chris Mills wrote:
> Yes. Writing through code is pointless. However I did find one thing
> (you can test it yourself). If you write a SQL statement in the
> Recordsource and save the form design, that SQL statement appears to
> store as the owner, who you were logged on as when you saved it
> (which would normally be the developer or "owner", whoever).

Compact the database, and see if that holds true.


--
Joan Wild

"TC" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> PS. One for you, Tom: why can't a person who uses an RWOP query, just
> edit the query SQL directly, so he can can do /any/ data retrieval or
> manipulation that the owner of the query can do? (Not just the specific
> data retrieval or manipulation that the query owner wrote it for
> originally?)
>
> I know the answer already. I just feel you're going to be asking this
> soon :-)
>

I assume that the user can't rewrite the sql if he/she doesn't have
permission to change the design of the query. That's my point about
parameters; a user doesn't need to change the design of the query when they
provide values for parameters but in effect, the value of the parameters
become part of the Where clause of the query.

Am I mistaken?

Tom Stoddard wrote:
>
> I'm doing this because I want to add a record to a table that the user
> doesn't have insert permissions on. The query that the form is based
> on is not updateable so I've created an Append query with RWOP and
> added parameters and then saved the query. I'm trying to execute it
> in code behind the form. It works well in one instance but I'm having
> trouble getting to work on another form I'm working on now. For some
> reason the record isn't getting added and I can't figure out if its
> due to the query or security or something I haven't thought of yet.

Did you give the users insert permission on the RWOP query?

--
Joan Wild

>
> Did you give the users insert permission on the RWOP query?
>
> --
> Joan Wild
>
Yes, I did Joan. Thanks! I figured it out. I was actually executing two
queries inside of a transaction. There was an error ocurring before the
transaction was committed. It works fine as long as I don't use the
transaction. That raises another question, however, which isn't really
related to security but you might be able to answer it anyway.

The reason for the two queries is that I want to add records to a second
table which is related to the first table I'm append the records to. The
problem is that I need to know what the primary key field of the new record
is before I can add the subsequent records to the second table. If I don't
use a transaction I can get that value by using a DLookup function against
the first table right after I've inserted the record. If there is an
uncommitted transaction then the DLookup function causes an error. If the
transaction is committed, or there is no transaction at all, then the code
works perfectly. The question is:

Is there an easier way to get the primary key value for a record when its
added to a table which uses an autonumber field (set to generate random
numbers because the database is replicated) as it's primary key (or
identity)? I've tried searching for this online but haven't found a solution
related to Access yet.

Thanks Again!