Permissions, RWOP queries not the answer

[Mike MacSween]

Mmmm. Doing some work with Access security.

Let's say I don't want the users to be able to edit the back end tables
directly. Not because it's a 'high security' application but because they
can do damage. I only want them to access the app through my forms.

So I deny them access rights (talking Access, not NT) to the back end data
tables. Then I use RWOP queries in the forms. This is a standard technique,
no?

User Frank I want to be able to read, update, insert, delete records in
table A

User Harry I want to be able to read, update, insert but NOT delete records
in table A

I can't set those permissions using Access security on the form can I? And
owners permission is going to give all users complete privileges (or at
least the greatest freedom that I grant to the 'owner').

How can I set a fine level of granularity of permissions, while still
ensuring that users can't get at the back end tables directly atall.

Mike

[Bri]

Mike MacSween wrote:

Mmmm. Doing some work with Access security.

Let's say I don't want the users to be able to edit the back end tables
directly. Not because it's a 'high security' application but because they
can do damage. I only want them to access the app through my forms.

So I deny them access rights (talking Access, not NT) to the back end data
tables. Then I use RWOP queries in the forms. This is a standard technique,
no?

User Frank I want to be able to read, update, insert, delete records in
table A

User Harry I want to be able to read, update, insert but NOT delete records
in table A

I can't set those permissions using Access security on the form can I? And
owners permission is going to give all users complete privileges (or at
least the greatest freedom that I grant to the 'owner').

How can I set a fine level of granularity of permissions, while still
ensuring that users can't get at the back end tables directly atall.

Mike

See my reply to your message in the earlier thread. The RWOP queries
need to have an Owner that has the appropriate rights, not be owned by
the administrator.

--
Bri

[Joan Wild]

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

I can't set those permissions using Access security on the form can I? And
owners permission is going to give all users complete privileges (or at
least the greatest freedom that I grant to the 'owner').

No, you misunderstand. Users are still restricted based on the permissions
you give them on the *query*. So if you give Frank read permission on the
query, that's all he'll be able to do (regardless that the query owner has
full permission on the table).

Give Frank read, update, insert, delete permission on the query, and Harry
read, update, insert permission on the query.

Actually you should apply permissions to groups; it's easier to manage
security.
--
Joan Wild
Microsoft Access MVP

[Bri]

Joan Wild wrote:

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

I can't set those permissions using Access security on the form can I? And
owners permission is going to give all users complete privileges (or at
least the greatest freedom that I grant to the 'owner').

No, you misunderstand. Users are still restricted based on the permissions
you give them on the *query*. So if you give Frank read permission on the
query, that's all he'll be able to do (regardless that the query owner has
full permission on the table).

Give Frank read, update, insert, delete permission on the query, and Harry
read, update, insert permission on the query.

Actually you should apply permissions to groups; it's easier to manage
security.

Joan,

I originally thought that too, but I then tested it and it doesn't work
that way (in AC97 anyway). I created a test user in a secured DB. I gave
that user NO rights to a table, created a RWOP query based on the table,
gave only read rights to the query. I then logged in as the test user
and had FULL rights to the table via the query, not the read only rights
I thought I would get. My solution was to create a new UserID with the
appropriate rights to the Table and made it the owner of the query. Then
it worked. The query inherites the rights of the OWNER in a RWOP query,
reguardless of what rights you assign to the query. In a regular query,
the rights of it cannot give you more rights to the underlying table
than you have for the table itself.

At least in my experience and testing.

--
Bri

[Joan Wild]

"Bri" <no*@here.com> wrote in message
news:wvWxe.1860786$6l.456831@pd7tw2no...

Joan,

I originally thought that too, but I then tested it and it doesn't work
that way (in AC97 anyway).
Yes it does work in 97. I've never had an issue with this.

I created a test user in a secured DB. I gave that user NO rights to a table, created a RWOP query based on the table,
gave only read rights to the query. I then logged in as the test user and
had FULL rights to the table via the query, not the read only rights I
thought I would get.
There could be a number of reasons like the users group has permissions on
the tables (or any group that the user is a member of).

My solution was to create a new UserID with the appropriate rights to the Table and made it the owner of the query. Then
it worked.
So who was the owner of the query before?

The query inherites the rights of the OWNER in a RWOP query, reguardless of what rights you assign to the query.

That is not correct; they are restricted by the permissions on the query.
The query doesn't inherit the rights of the owner. It just means that,
although the user has no permissions on the underlying table(s), when they
run this query, give them access to the tables as though the query owner
were running the query. However, still restrict them based on the
permissions I assign to this query.

If a user has full permissions on a table, and then creates a RWOP query,
but only gives read permissions on the query, any user running the query
will have read only capabilities while using the query. I have done this
countless times and never seen what you have.

It suggests to me that your test user is getting their permissions from some
group membership that you've overlooked, or that your database isn't secured
properly.
--
Joan Wild
Microsoft Access MVP

[Bri]

Joan Wild wrote:

"Bri" <no*@here.com> wrote in message
news:wvWxe.1860786$6l.456831@pd7tw2no...

Joan,

I originally thought that too, but I then tested it and it doesn't work
that way (in AC97 anyway).
Yes it does work in 97. I've never had an issue with this.

There could be a number of reasons like the users group has permissions on
the tables (or any group that the user is a member of).

It was a member of only one group, that group had the Read only
permission on the query and no permissions on the table.
My solution was to create a new UserID with the

appropriate rights to the Table and made it the owner of the query. Then
it worked.
So who was the owner of the query before?

My Administrator UserID that I used to create the query.
The query inherites the rights of the OWNER in a RWOP query,

reguardless of what rights you assign to the query.

That is not correct; they are restricted by the permissions on the query.
The query doesn't inherit the rights of the owner. It just means that,
although the user has no permissions on the underlying table(s), when they
run this query, give them access to the tables as though the query owner
were running the query. However, still restrict them based on the
permissions I assign to this query.

If a user has full permissions on a table, and then creates a RWOP query,
but only gives read permissions on the query, any user running the query
will have read only capabilities while using the query. I have done this
countless times and never seen what you have.

It suggests to me that your test user is getting their permissions from some
group membership that you've overlooked, or that your database isn't secured
properly.

I went back to test this again it now works as you describe. This is
also the behavior that I had expected originally. I can no longer
explain how my first test did not behave like this. In it, the test ID
had the full rights to the data via the query that it only had read
rights assigned to (no rights to the table). I did it again from scratch
and only got read rights (as originally expected). Must have screwed up
something in the first test.

--
Bri

[Mike MacSween]

"Joan Wild" <jw***@nospamtyenet.com> wrote in message
news:11*************@corp.supernews.com...

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

I can't set those permissions using Access security on the form can I?
And owners permission is going to give all users complete privileges (or
at least the greatest freedom that I grant to the 'owner').
No, you misunderstand. Users are still restricted based on the
permissions you give them on the *query*. So if you give Frank read
permission on the query, that's all he'll be able to do (regardless that
the query owner has full permission on the table).

Ahhh! I see.
Give Frank read, update, insert, delete permission on the query, and Harry
read, update, insert permission on the query.

Actually you should apply permissions to groups; it's easier to manage
security.

Yes, that's what I've been doing.

Thanks Joan

Mike