A97 Workgroup Security puzzle

[Andrew Chanter]

I have taken over support of an A97 application which has presented an

interesting and confounding scenario involving workgroup security. I cant
figure out whether my predecessor deliberately engineered this situation or
has hit on a bug in the security model and exploited this. The system is in
4 components, a database back end, an application front end (ODBC links to
back end), an administration front end and a workgroup security file. There
have been no write permissions assigned to the tables in the back end
database, which means that no user other than the database owner (now me)
can make changes to the data directly in the back end. When I saw this it
made me wonder how the users were able to edit data in their front end
application as I would have thought the principles of inheritance would have
prevented this. In the front end database, users have been given full
editing rights to all of the table links, but this does not permit editing -
presumably because the reciprocal rights dont exist in the back end.
However - and here's the thing - there are a heap of queries in the front
end that have also had full editing rights assigned. The users are able to
make changes to the data so long as their forms reference these queries
rather than the tables. In other words, if I open tblData as a user I cant
edit the data, but if I open qryData which is simply 'select * from tblData'
I can edit - its true I swear! If I make a copy of qryData, this does not
allow editing even though it has the same permission set as the original, so
there is something special about these queries.

So, in summary, there are a number of queries in the front end app that

have somehow been given the power to override the permissions on the source
tables. I cant see what is different about these queries but obviously
there is something. The issue that I have is that I need to give write
permission to a small group of users via the administration interface. I
cant replicate the setup with the queries in the main app, so I'm snookered.
Obviously I can work around this by granting write permissions in the back
end database, but its kind of nifty the way the thing works at the moment in
only allowing data mods to occur via the front end interface. So if I could
replicate this setup with queries in the admin db I would be happy.

Any clues out there?

[Rick Brandt]

Andrew Chanter wrote:

I have taken over support of an A97 application which has presented an

interesting and confounding scenario involving workgroup security. I
cant figure out whether my predecessor deliberately engineered this
situation or has hit on a bug in the security model and exploited
this. The system is in 4 components, a database back end, an
application front end (ODBC links to back end), an administration
front end and a workgroup security file. There have been no write
permissions assigned to the tables in the back end database, which
means that no user other than the database owner (now me) can make
changes to the data directly in the back end. When I saw this it
made me wonder how the users were able to edit data in their front
end application as I would have thought the principles of inheritance
would have prevented this. In the front end database, users have
been given full editing rights to all of the table links, but this
does not permit editing - presumably because the reciprocal rights
dont exist in the back end. However - and here's the thing - there
are a heap of queries in the front end that have also had full
editing rights assigned. The users are able to make changes to the
data so long as their forms reference these queries rather than the
tables. In other words, if I open tblData as a user I cant edit the
data, but if I open qryData which is simply 'select * from tblData' I
can edit - its true I swear! If I make a copy of qryData, this does
not allow editing even though it has the same permission set as the
original, so there is something special about these queries.

So, in summary, there are a number of queries in the front end app
that

have somehow been given the power to override the permissions on the
source tables. I cant see what is different about these queries but
obviously there is something. The issue that I have is that I need
to give write permission to a small group of users via the
administration interface. I cant replicate the setup with the
queries in the main app, so I'm snookered. Obviously I can work
around this by granting write permissions in the back end database,
but its kind of nifty the way the thing works at the moment in only
allowing data mods to occur via the front end interface. So if I
could replicate this setup with queries in the admin db I would be
happy.

Those are Run-With-Owners-Permission queries and that is a standard setup for
Access security. It prevents people from importing or linking to the tables
with a file independent of the front end that the developer intends to be used.

By the way, those are not ODBC links. You can't use ODBC to link an Access file
to another Access file.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com