Re: RWOP queries for back-end secuirty: easy/hard? slow/fast?

Comments in-line

google@xxxxxxxxxxxxxx wrote:
I am creating a new database for use within our company, that I'd like
to make reasonably secure (short of a true server based solution). The
back-end of a non-server based database seems to be the biggest
security hole, due to the ease of opening, linking, and/or importing
raw data directly from the back-end database. I've read information
that indicates that locking down the back-end tables to the owner, then
using RWOP queries in the front end, helps to close this hole somewhat,
but can be problematic when creating SQL statements from code, and
other methods of querying data beyond a straightforward Access query.
I've also seen suggestions to basically create a simple "select *"
query with RWOP in the front end for each and every table in the
back-end, and then use those queries as the record source for other
queries in the front end in lieu of the tables themselves. My question
is two-fold: does this tactic completely wipe out the problems with
RWOP queries?

Yes, the RWOP query can be used in other queries the same way that you would use a table.

I also create RWOP queries for situations where either some fields or some records are restricted for certain user groups.

Field restriction example; if only Managers are allowed to see Employee Pay amounts, but everyone needs to know what PayGroup the Employee is in. Create two RWOP queries:

rwopPayManager: SELECT EmpID, FromDate, ToDate, PayGroup, BasePay
FROM Pay
rwopPayEmployee: SELECT EmpID, FromDate, ToDate, PayGroup
FROM Pay

Then you can give the Manager group permissions to rwopPayManager and everyone permission to rwopPayEmployee. The Report or Form (in Open Event) then needs to check the currentuser to see if they are in the Manager group and then pick one query or the other as the RecordSource.

Record restriction example; If a user is in the Supervisor Group then they need to see only the Employees that they supervise:

rwopEmployeeSupervisor: SELECT * FROM Employee
WHERE SupervisorID=CurrentUser()

Can I then continue to create SQL statements in code,
pointing to the RWOP queries, which in turn point to the tables in the
back-end?

Yes

Does everything then work just as if I was pointing directly
to the back-end tables?

Yes

Are there any performance penalties or other
drawbacks of an extra layer of query between my real (conditional)
queries and the tables?

Not that I've ever noticed. It might be possible in the case of linked ODBC tables that the extra layer could be the last straw that causes Access to decide that the query is too complicated to send to the server and so decides to pull all the data to run the query locally. You would have to test each query to see. It is usually VERY obvious when this happens. A query will take an order of magnitude (or two!) longer to run than it should.

Does this method provide a real security
benefit... i.e. does it truly prevent linking or importing the raw
data, or at least provide a reasonable hindrance to the average user in
doing so?

If someone links to the backend tables outside of your app, they will not have permissions to the tables. Without cracking the Workgroup security, there should be no way in (assuming that all of the proper steps to secure it have been done).

All this being said, I have never done an app where I did this with every table. I do it on all of the tables that require specific security that can't be handled by the standard User/Group permissions. Remember to apply the permissions to the BE MDB as well as to the Links in the FE. Putting permissions only on the links will not secure the BE from being opened or linked, that must be done separately.

--
Bri

.

Relevant Pages

  • Re: limiting table access and RWOP queries
    ... The table tblLocalCompanyCityLookup has no permissions for members of the ... So it seems like the code query runs with user permissions to me. ... > tables, then, using rwop queries is the way to go. ... in a turnkey database application. ...
    (microsoft.public.access.security)
  • Re: granting table permissions
    ... Granting permissions on the 'new table/query' won't work in this case, ... the user will be the owner of the resulting table. ... You could then run a delete query to empty the table, ... query (both of these would need to be RWOP queries). ...
    (microsoft.public.access.security)
  • Re: limiting table access and RWOP queries
    ... > no permissions on tables and basing all forms on RWOP queries. ... and it seems I can't give those owner permissions. ... A query that is created through VBA code, ... > Should I just hide the database window and set the AllowBypassProperty to false? ...
    (microsoft.public.access.security)
  • Re: limiting table access and RWOP queries
    ... > A query that is created through VBA code, ... An RWOP query, in any sensible security meaning, means the owner of the ... The best I could achieve (assuming stored RWOP queries are themselves ... Seriously, I can see a good reason to hide queries, as against ...
    (microsoft.public.access.security)
  • Query forms dont work with back-end database
    ... The application has a front and back-end. ... First let me define what I mean by, ?sub form? ... calling these lower level bound forms, sub forms and basing them on query ...
    (microsoft.public.access.queries)
Loading