RWOP query with parameters

[Tom Stoddard]

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?

 

[Chris Mills]

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]

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

 

[Chris Mills]

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

 

[TC]

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

 

[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

 

[Joan Wild]

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.

 

[Tom Stoddard]

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?

 

[Joan Wild]

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?

 

[Tom Stoddard]

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

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!

 

[Chris Mills]

Compact the database, and see if that holds true.Good suggestion. Compacting doesn't affect it.

It gives every appearance that the owner of the form is the owner of the
recordsource sql, when saved with the form design. I'd be perfectly happy if
there's a catch in it. I'm only saying what I found.

Thanks
Chris

 

[Joan Wild]

I can remember coming across this before. It worked for a while, and then
the user started getting no permissions errors - the only thing I could
think of was the frontend getting compacted and clearing out all the temp
queries.

It worked for a bit, and then would stop.

 

[Joan Wild]

--
Joan Wild
Microsoft Access MVP

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.

You can't rely on that in a multiuser environment. Also how are you looking
it up?

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.

Using DAO you can grab the new ID before you update the main record...
Private Sub cmdAddRecords_Click()
On Error GoTo cmdAddRecords_Click_Error
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim dbs As Database
Dim rstMain As Recordset
Dim intMainID As Long

fInTrans = False
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbs = CurrentDb()
Set rstMain = dbs.OpenRecordset("MainTable", , dbAppendOnly)

wrkCurrent.BeginTrans
fInTrans = True
With rstMain
.AddNew
!somefield = whatever
!anotherfield= whatever
intMainID = rstMain!PrimaryKey
.Update
End With
'then here you can run your append query into the sub table specifying
intMainID as the value for the foreign key field.
dbs.execute "INSERT INTO.....

wrkCurrent.CommitTrans
fInTrans = False

cmdAddRecords_Click_Exit:
On Error Resume Next
rstMain.Close
Set rstMain = Nothing
Set dbs = Nothing
Set wrkCurrent = Nothing
Exit Sub

cmdAddRecords_Click_Error:
If fInTrans = True Then
wrkCurrent.Rollback
MsgBox "The records were not added"
End If
Select Case Err.Number
Case Else
MsgBox "Error #" & Err.Number & " :" & vbCrLf & vbCrLf &
Err.Description
End Select
Resume cmdAddRecords_Click_Exit

 

[Chris Mills]

Oh well, I'm certainly not offering to extend the Access warranty

I can see that it's "iffy" in that it's not really documented, but then a lot
of the precise detail of Access is not well documented. And certainly one
could easily make a mistake, since the "owner" is not visible. But I don't
know why it would be "intermittent". Maybe it is.

It's arguable whether there is any point of course, in that recordset is a
crackable property with some mde crackers (I think), unlike code at the
present time. I was just contemplating how RWOP's can be "hidden", a wastefull
exercise if ever there was one

Well, might stop the "average" umm...something.

Ta
Chris

 

[Tom Stoddard]

--
Joan Wild
Microsoft Access MVP



You can't rely on that in a multiuser environment. Also how are you
looking it up?

This is not a transactional application. It would be extremely unlikely for
2 users to be editing the same record at the same time and since the
autonumber field is generating random numbers, the likelihood of duplicate
primary keys is not worth the trouble of doing it otherwise.

Using DAO you can grab the new ID before you update the main record...
Private Sub cmdAddRecords_Click()
On Error GoTo cmdAddRecords_Click_Error
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim dbs As Database
Dim rstMain As Recordset
Dim intMainID As Long

fInTrans = False
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbs = CurrentDb()
Set rstMain = dbs.OpenRecordset("MainTable", , dbAppendOnly)

wrkCurrent.BeginTrans
fInTrans = True
With rstMain
.AddNew
!somefield = whatever
!anotherfield= whatever
intMainID = rstMain!PrimaryKey
.Update
End With
'then here you can run your append query into the sub table specifying
intMainID as the value for the foreign key field.
dbs.execute "INSERT INTO.....

wrkCurrent.CommitTrans
fInTrans = False

cmdAddRecords_Click_Exit:
On Error Resume Next
rstMain.Close
Set rstMain = Nothing
Set dbs = Nothing
Set wrkCurrent = Nothing
Exit Sub

cmdAddRecords_Click_Error:
If fInTrans = True Then
wrkCurrent.Rollback
MsgBox "The records were not added"
End If
Select Case Err.Number
Case Else
MsgBox "Error #" & Err.Number & " :" & vbCrLf & vbCrLf &
Err.Description
End Select
Resume cmdAddRecords_Click_Exit

Joan, I was doing just that before I implemented security but now I can't
update the table that way because the user doesn't have permission. I had to
create a RWOP query and save it in order for the user to be able to update
the records I want him to be able to edit. That's how this all started.

Thanks!

 

[Chris Mills]

Look. Call me simple. "You're simple, Chris" "Thankyou!"

I don't really know what your overall design scheme is. I just think there are
lots of complications with doing background table manipulation whilst the same
stuff is open through bound forms. Requery, things like that, sure. Standard
bound forms/subforms can do most things, automatically.

Why not just use all the in-built power of Access? With a stored RWOP if you
need it? I once got hot on "unbound forms", where you certainly have full
control, but they just weren't worth the hassle not least in maintenance.

The average office worker wouldn't know a bound from an unbound form. What
exactly are you trying to protect against? The KGB...Tom Wickerath...?

Please don't think I'm saying anything more than that there's some merit in
keeping things simple. As reasonably as possible. Hide the database window and
don't worry about them getting into stored queries (say). Don't worry about a
form not being open turning a Where clause into a parameter - just have the
referenced form open or self-referenced. If they can do things like
(whatever), who's to say what they can do?

You have to define the "threat" and probability, before you can address it.
HTH
"Simple Chris"

 

[TC]

Sure; the parameters are used however you use them. If you refer to
them in the WHERE clause, they will affect the WHERE clause. If you
don't, they won't. There are various places you could use a parameter
apart from the WHERE clause (although that would probably be the
commonest place).

Again, it is entirely up to the query designer. If you write a query
like this:

SELECT * FROM S3CR3T
WHERE ID_NUMBER = [parameter]

then, people who run that query can presumeably see /any/ ID number,
just by passing the right parameter value. If that is not appropriate,
then, you have written the query improperly. It's really nothing to do
with whether it is an RWOP query, or not.

But if you write a query like this, for example:

SELECT * FROM S3CR3T
WHERE CREATED_BY = CURRENTUSER()
AND CREATED_DATE >= [parameter]

then, the parameter does not let the user see records they shouldn't be
able to see.

Again, if a query lets a user display or edit inappropriate records,
then, the query has been written incorrectly. It's not a shortcoming in
queries, or parameters, or the RWOP feature.

HTH,
TC

 

[TC]

If an updatable, multi-table query is properly designed, Jet will fill
out the related fields for you - automagically! There is no need to do
that manually. But it can be tricky to get it to work:

- Obviously all the tables must be primary-keyed correctly;
- their relationships must be defined in the relationships window, and
- IIRC, all the relevant fields must be included in the SELECT list.

Do some research on multi-table updatable queries

HTH,
TC

 

[Tom Stoddard]

TC,

I guess this all comes down to the updateable issue. Let me explain my
precise problem and see if you have a solutions to offer.

I will try to use the simplest example I can come up with. I have a
SalesReps table with 2 fields, SalesRepName & SalesRepID. I have a Customers
table with 3 fields, CustomerID, CustomerName, SalesRepID. They are related
with referential integrity through the SalesRepID field. Then I created a
Permissions table which has 2 fields, UserName & SalesRepID which is related
to the SaleReps table also with referential integrity. Now I want some users
to be able to edit, add & delete customers in the Customers table as long as
the customer is assigned to a SalesRep which the user has an entry in the
Permissions table for. For example, if there is a record in the Permissions
table with Tom in the UserName Field and 5 in the SalesRepID field then Tom
should be able to delete customers from the Customers table as long as the
SalesRepID for the Customer he deletes is 5. I can enforce that restriction
many ways but when it comes time to delete the Customer I need to use a RWOP
query because no users have delete data permissions on the Customers table.

Now I create a form and base it on a query that uses the Customers table,
the SalesReps table and the Permissions table (or I can do it with just the
Customers & Permissions table). The Customers table is related to the
SaleReps table and the SalesReps table is related to the Permissions table
but the Permissions table is not directly related to the Customers table. It
seems that as long as the Permissions table is part of the same query that
the Customers table is in, then I can't find a way to structure it to allow
edits, deletions or additions. Am I missing something? I have to use the
Permissions table to limit which customers records are displayed on the
form. I end up having to use one query to base my form on and then when the
user selects a record to delete I have to run a different query to
accomplish that. Thats's where the parameters come in.

It's working this way but its a lot more work than I wanted to do to enforce
this level of security. Any suggestions would be appreciated.

Sure; the parameters are used however you use them. If you refer to
them in the WHERE clause, they will affect the WHERE clause. If you
don't, they won't. There are various places you could use a parameter
apart from the WHERE clause (although that would probably be the
commonest place).

Again, it is entirely up to the query designer. If you write a query
like this:

SELECT * FROM S3CR3T
WHERE ID_NUMBER = [parameter]

then, people who run that query can presumeably see /any/ ID number,
just by passing the right parameter value. If that is not appropriate,
then, you have written the query improperly. It's really nothing to do
with whether it is an RWOP query, or not.

But if you write a query like this, for example:

SELECT * FROM S3CR3T
WHERE CREATED_BY = CURRENTUSER()
AND CREATED_DATE >= [parameter]

then, the parameter does not let the user see records they shouldn't be
able to see.

Again, if a query lets a user display or edit inappropriate records,
then, the query has been written incorrectly. It's not a shortcoming in
queries, or parameters, or the RWOP feature.

HTH,
TC

 

[Joan Wild]

This is not a transactional application. It would be extremely
unlikely for 2 users to be editing the same record at the same time
and since the autonumber field is generating random numbers, the
likelihood of duplicate primary keys is not worth the trouble of
doing it otherwise.

Yes, but you are looking up via DLookup the autonumber that was just added,
and since they are random, how do you look it up?

SNIP


Joan, I was doing just that before I implemented security but now I
can't update the table that way because the user doesn't have
permission. I had to create a RWOP query and save it in order for the
user to be able to update the records I want him to be able to edit.
That's how this all started.

OK, so create a RWOP query SELECT * FROM sometable...
Then set the rstMain to this saved query and append to it. Ditto for the
subtable.