User-Level Security & Split db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

croy - 21 Dec 2007 20:12 GMT

This mdb pair has been in use for some time, but there have
been lots of development tweaks (not security) since the
last time I tested the ability of the "data-entry" MS Access
group to enter data (MS Access 2002/WindowsXPSP2).

In getting ready to publish another front end, when
attempting to open a data-entry form as a "data-entry" user,
I'm presented with "Record(s) cannot be read; no read
permission on 'tblIvDetail'." That table feeds the subform
on the main form that I was trying to open.

The same holds true for opening any of the forms that a
data-entry clerk might need to use.

I've checked all the queries for "run with owner's" and
every one is set that way. I've checked the permissions for
that group for tables (front-end), and they're all set for
"administer". Same for all the queries.

I copied the back-end from the server to a local directory,
set the shortcut to a copy of the front-end and re-linked
everything to the local back-end, but the result is
identical.

Am I remembering correctly that the only group that really
needs permissions on the back-end is the admins group?

I can't think of what else could be wrong.

Any thoughts appreciated.

Signature

croy

Reply to this Message

croy - 21 Dec 2007 20:26 GMT

>This mdb pair has been in use for some time, but there have
>been lots of development tweaks (not security) since the
[quoted text clipped - 26 lines]
>
>Any thoughts appreciated.

I forgot to mention that after getting the error message,
the form opens and the data shows. I can even edit the
data(!), but get the error message every time the focus
moves to another record.

Signature

croy

Reply to this Message

Joan Wild - 21 Dec 2007 20:28 GMT

> In getting ready to publish another front end, when
> attempting to open a data-entry form as a "data-entry" user,
[quoted text clipped - 6 lines]
> that group for tables (front-end), and they're all set for
> "administer". Same for all the queries.

The data-entry group needs update and insert permissions (and delete if you wish) on the RWOP query(ies). Just setting them to RWOP isn't enough; they need permission on the query objects. Then they don't need any permissions on the tables.

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

croy - 21 Dec 2007 20:54 GMT

>> In getting ready to publish another front end, when
>> attempting to open a data-entry form as a "data-entry" user,
[quoted text clipped - 8 lines]
>
>The data-entry group needs update and insert permissions (and delete if you wish) on the RWOP query(ies). Just setting them to RWOP isn't enough; they need permission on the query objects. Then they don't need any permissions on the tables.

Thanks for the reply, Joan.

Understood... I even went so far as to give that group
"administer" permissions for all queries (and the message
presists!). But even before going that far, all four
checkboxes on the right side were checked for all queries
(for that group).

I'm beginning to wonder if one of the recent MS Security
Updates has something to do with this. Of course I have no
business making that statement, as I fall far short of
knowing anything about such things... |:-( It will
undoubtedly turn out to be something dumb that I did, but at
the moment I'm out of clues.

Signature

croy

Reply to this Message

croy - 21 Dec 2007 21:22 GMT

>>> In getting ready to publish another front end, when
>>> attempting to open a data-entry form as a "data-entry" user,
[quoted text clipped - 23 lines]
>undoubtedly turn out to be something dumb that I did, but at
>the moment I'm out of clues.

How about code behind the forms... that changes the default
value for a field--does that require the clerk to have
permissions to modify form design?

Or this:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    On Error GoTo Err_Form_BeforeInsert

    Me!NewRecClerk = CurrentUser()

    Exit_Form_BeforeInsert:
    Exit Sub

    Err_Form_BeforeInsert:
    MsgBox Err.Description
    Resume Exit_Form_BeforeInsert

    End Sub

Perhaps the above would require permissions for the table...
but that group *has* administer permissions on the tables
(at least until I get this worked out...).

Signature

croy

Reply to this Message

croy - 21 Dec 2007 21:28 GMT

Another discovery: when I make the db window available,
then log in as a member of the data-entry group, I can run
all the queries, but I can't open any of the tables, which
seems odd--that group has "administer" permissions on all
the tables (links) in the front-end. And like before, the
forms won't open without the message. Even tho the forms do
all their stuff thru queries... hmmm.

Signature

croy

Reply to this Message

Joan Wild - 21 Dec 2007 23:17 GMT

>>Understood... I even went so far as to give that group
>>"administer" permissions for all queries (and the message
[quoted text clipped - 9 lines]
> forms won't open without the message. Even tho the forms do
> all their stuff thru queries... hmmm.

The users have administer permissions on the *links*, but not on the tables in the backend.

I would guess that you have listboxes/comboboxes and it's the rowsources that you may have to check.

Is the recordsource of the form(s) a saved query, or a SQL statement?

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

croy - 26 Dec 2007 15:47 GMT

>>>Understood... I even went so far as to give that group
>>>"administer" permissions for all queries (and the message
[quoted text clipped - 13 lines]
>
>I would guess that you have listboxes/comboboxes and it's the rowsources that you may have to check.

It turns out that *some* of the problem was due to some
"little changes" (hehe) I made in the CBF: domain lookup
stuff that wanted to interface with the tables directly,
instead of thru "owner's permissions" queries. But I
haven't gotten the last of the stickiness out of the
front-end yet.

>Is the recordsource of the form(s) a saved query, or a SQL statement?

Saved queries on everything. Actually, I did find one
combobox that was fed by SQL, which brings up another
question: in that SQL, I noticed it ended with the "owner's
permissions" statement. Does that statement get the job
done even in SQL, or do I still need to bolt it in with a
query (Access 2002 here)? It didn't seem to be hanging me
up, but I'm still not finished getting this front-end up to
100% yet, either.

Thanks for your thoughts, Joan.

Signature

croy

Reply to this Message

Joan Wild - 31 Dec 2007 15:18 GMT

> Saved queries on everything. Actually, I did find one
> combobox that was fed by SQL, which brings up another
[quoted text clipped - 4 lines]
> up, but I'm still not finished getting this front-end up to
> 100% yet, either.

A SQL statement is 'owned' by the user running it, so RWOP isn't useful in this case. You can use a SQL statement, but make sure it is based on RWOP queries and not tables. i.e. SELECT * FROM SomeRWOPQuery

You might find it useful to create a RWOP query for each table, and then base all your recordsources/rowsources on these.

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message