MS Access database support - A97 Workgroup Security puzzle

Author

A97 Workgroup Security puzzle

Andrew Chanter

2005-06-28, 8:25 pm

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

2005-06-28, 8:25 pm

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

Sponsored Links