RWOP query with parameters

[Tom Stoddard]

'Ello, 'ello! I thought you were trying the updatability by opening
the query directly & typing into the datasheet?

I have been trying to do it directly in the datasheet and it's not
updateable. I'm using the term recordset generically. The records that are
displayed in the datasheet are a recordset which is returned by Jet based on
the SQL statement the Access generates based on what is entered in the query
grid. A recordset is a recordset is a recordset even if its displayed in a
datasheet. That's why, when you go into the property page of the query, one
of the properties is the "recordset type" property.

I seem to be having trouble describing a fairly simple scenario. Let me try
once again.

tblPermissions
UserName (P..)
SalesRepID (..K) < (M-1) relationship with tblSalesReps

tblCustomers
CustomerID (PK)
SalesRepID < (M-1) relationship with tblSalesReps
CustomerName

tblSalesReps
SalesRepID (PK) < (1-M) relationship with both tblPermissions and
tblCustomers
SalesRepName

My SQL statement copied directly out of the Access query window (except for
the note in parenthesis):

SELECT tblCustomers.* (I've tried many combinations here but none of them
made a difference)
FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON
tblSalesReps.SalesRepID = tblPermissions.SalesRepID
WHERE (((tblPermissions.UserName) = CurrentUser()));

By default, Access sets the 'recordset type' property of the query to
Dynaset. As is, the resulting records when displayed in the datasheet or in
a form are not updateable.

Originally I thought I could leave tblSalesReps out of the query altogether
like this:

SELECT tblCustomers.CustomerName
FROM tblCustomers INNER JOIN tblPermissions ON tblCustomers.SalesRepID =
tblPermissions.SalesID
WHERE (((tblPermissions.UserName)=CurrentUser()))

I added the SalesReps table (as a junction table) to the query thinking that
because there are relationships defined in the relationship window of the
database for those tables that it would result in an updateable query. It
didn't work. Basically, the relationships between these tables looks like
this:

tblPermissions (M----->1) tblSalesReps (1<-----M) tblCustomers

I've read what I could find in the Access help files and this behavior is by
design. This query should not be updateable unless I change the recordset
type property to 'Dynaset (inconsistent updates)'. My permission scheme is
to limit access to records based on their relationship to sales reps so I
believe that I've structured my tables and relationships properly. I can
filter records very easily. My only challenge is that I need to find a way
to edit the resulting records. Inconsistent Updates will allow me to do
that. Now I'm looking for 2 pieces of advice.

1. Is my security scheme flawed?
2. Is there anything I need to know anything about using 'Dynaset
(inconsistent updates)'? I understand that I have to careful about breaking
referential integrity when editing those records but is there some
unexpected 'gotcha' I should be looking out for? The reason I concerned is
that nobody suggested this before. Why not?

Thanks again, and yes, I'm also interested in what Joan has to say.

 

[TC]

I know what the problem is. I got it going at home earlier tonight. But
I am just back on for a moment, after a dinner with friends trying to
submit a crazy idea to Mythbusters! So I will get back on tomorrow &
give you a link to my example db.

Cheers,
TC

 

[TC]

Ok, there is bad news, & good news!

The bad news is, the query as currently written, is not updatable. This
is clearly due to the many-to-many join, which I should have seen
immediately.

The good news is, it only needs a simple change to the query. The table
structure, primary keys & relationships need not be changed.

Here is what you had before:

SELECT *
FROM (tblSalesReps INNER JOIN tblCustomers
ON tblSalesReps.SalesRepID = tblCustomers.SalesRepID)
INNER JOIN tblPermissions
ON tblSalesReps.SalesRepID = tblPermissions.SalesRepID
WHERE (((tblPermissions.UserName) = CurrentUser()));

Here is what to change it to. (From memory, cos I forgot my floppy
disk!)

SELECT *
FROM ( tblSalesReps AS reps INNER JOIN tblCustomers AS custs
ON custs.SalesRepID = reps.SalesRepID)
WHERE EXISTS
( SELECT null
FROM tblPermissions AS perms
WHERE perms.username = currentuser()
AND perms.salesrepid = reps.salesrepid )

Note the use of alias names (AS ...) to make it look simpler.

Yes? No?

HTH,
TC

 

[Tom Stoddard]

SELECT *

FROM ( tblSalesReps AS reps INNER JOIN tblCustomers AS custs
ON custs.SalesRepID = reps.SalesRepID)
WHERE EXISTS
( SELECT null
FROM tblPermissions AS perms
WHERE perms.username = currentuser()
AND perms.salesrepid = reps.salesrepid )

Note the use of alias names (AS ...) to make it look simpler.

Brilliant! Why does that work?

Let me guess. Since the permissions table is not actually attached to the
Customers table in the main query there is no reason not to allow updates.

I wouldn't have thought to select "null". Is there an advantage to doing
that instead of selecting one of the fields in perms or even *?

Thanks again for all of your help. This has been a long thread and has
gotten a little off of the security topic but for good reason. For those who
might take the time to read all of this and for my own closure, I'd like to
summarize.

My real world scenario:

I work for a company which has about 600 customers. Each customer is
assigned to 1 of 9 sales representatives. I have a database which enables
users to create and maintain price lists for many of those customers, among
other things. The users include sales reps, customer service reps,
purchasing personnel and managers. Customer service reps access the database
in order to look up pricing for customers and some of them are given
permission to maintain some of the sales reps price lists (some being the
important term). Sales reps should only be allowed to edit price lists for
their own customers, and managers should be able to edit all sales reps
price lists. This scenario requires me to assign permissions in an arbitrary
manner.

My solution:

I took update, insert and delete permission to the tables related to the
customers and their price lists away from all users and have allowed them to
edit the data only through queries that are 'Run With Owner Permissions'. I
created a permissions table which stores unique combinations of usernames
and salesrep numbers. In order to determine which records a user is able to
access, I filter the data using the permissions table which checks to see if
a price list is related to a customer who is assigned to a salesrep that the
current user has permission to edit. This solution provides me with a level
of security that the company is comfortable with.

Challenge:

In the process of implementing this scheme, I created some RWOP queries with
the permissions table attached so that I could filter the data as desired.
The result of the query was a recordset which was not updateable. This is
not the result I wanted.

Solution:

TC offered a solution which is accomplished through a design change to my
queries. His solution uses the "Exists" statement in the where clause of the
query (The sql statement is included at the top of this message). The query
checks for the existence of records in the permissions table which match the
salesrepID in the salesreps table of the main select statement. In doing so,
the query does not attach the permissions table directly to any of the
tables from which output fields are being selected. The result is a
recordset which is updateable.

Hats off to TC!

 

[TC]

Brilliant! Why does that work?

The original query had a many-to-many relationship between two of the
tables. Many-to-many queries are never updatable. There are some
logical reasons for this; a bit too much to discuss here. I removed one
table, to leave an oridinary updatable one-to-many query. I chose to
remove the Permissions table, because I could easily see how to regain
the required functionality by adding a "subquery" (the second SELECT
clause) on that table.

Let me guess. Since the permissions table is not actually attached to the
Customers table in the main query there is no reason not to allow updates.

No, it is really just the issue of m:m versus 1:m.

I wouldn't have thought to select "null". Is there an advantage to doing
that instead of selecting one of the fields in perms or even *?

This is a favourite hobby horse of mine, though I haven't actually
ridden it for some years now! The EXISTS operator is interested in one
thing, and one thing only: does the subquery return any /rows/? If it
does, the EXISTS is True; if it doesn't, the EXISTS is False. It does
not care about the /columns/ in the rows that are (or aren't) returned.

So, the subquery could say SELECT UserName, SELECT "hello world",
SELECT *, SELECT 123 & SalesRepID, or whatever else you wanted it to.
This would have no impact whatever on how the EXIST worked. Therefore,
as a matter of principle, I select "the least" thing that I can: NULL.

The other advantage of using NULL is that it verifies whether the
reader understands the EXISTS operator! If someone asks "why NULL?",
this suggests that they don't understand that the /columns/ selected by
the subquery, are irrelevant. I used this trick in my previous life as
a software project leader. Whene-ever someone asked "why NULL?", I knew
to give them my little EXISTS lecture!

Thanks again for all of your help. This has been a long thread and has
gotten a little off of the security topic but for good reason.

Thanks to you for staying with it! I've enjoyed it a lot, because we
eventually got a result. Too often, people get 2/3rds of the way to a
solution, then drop off the radar. I find that very frustrating, which
is why I generally stopped answering database design questions in other
newsgroups.

For those who might take the time to read all of this and for my own closure, I'd like to summarize.

And well done for that too. I follow some other technical newsgroups
where I am not an expert in the topics discussed. Sometimes, lots of
people post to a topic, but no-one posts a summary, so I'm not sure
what they all concluded!


Cheers & well done,
TC