Security in fields

27 Jul 2006 3:04 AM

It'sss Johnny

Hi all! I have a single table that has about 20 fields and with security
clearance just for me. I would like to create a new form (just for Vicky) so
she can see and update only 4 fields from that table.

I don't want to split my table; so I was thinking of creating a query that
pulls out only those 4 fields for her. Then to apply security to that query
and make it the source for the new data entry form. Is this design possible?
Can Vicky acquire permission to update those fields through the security set
on the query, regardless the security on the table? Hope you get what I
mean. Thanks for your analysis and ideas.

27 Jul 2006 7:49 AM

Keith Wilby

"It'sss Johnny" <nowhere@somewhere.there> wrote in message
news:G2Wxg.178782$mF2.53278@bgtnsc04-news.ops.worldnet.att.net...
> Hi all! I have a single table that has about 20 fields and with security
> clearance just for me. I would like to create a new form (just for Vicky)
> so she can see and update only 4 fields from that table.
>
> I don't want to split my table;

If you're going to have more than 1 concurrent user you should split your
*database*.

> so I was thinking of creating a query that pulls out only those 4 fields
> for her. Then to apply security to that query and make it the source for
> the new data entry form. Is this design possible?

Yes.

> Can Vicky acquire permission to update those fields through the security
> set on the query, regardless the security on the table?
>

Yes, set the query's run permissions to "owner's" and give Vicky read/write
permissions to the *query*. She'll then have the same permissions to the
query data as the owner of the query.

HTH - Keith.
www.keithwilby.com

29 Jul 2006 6:00 AM

It'sss Johnny

Show quote Hide quote

"Keith Wilby" wrote:

> "It'sss Johnny" wrote:
>> Hi all! I have a single table that has about 20 fields and with security
>> clearance just for me. I would like to create a new form (just for Vicky)
>> so she can see and update only 4 fields from that table.
>>
>> I don't want to split my table;
>
> If you're going to have more than 1 concurrent user you should split your
> *database*.
>
>> so I was thinking of creating a query that pulls out only those 4 fields
>> for her. Then to apply security to that query and make it the source for
>> the new data entry form. Is this design possible?
>
> Yes.
>
>> Can Vicky acquire permission to update those fields through the security
>> set on the query, regardless the security on the table?
>>
>
> Yes, set the query's run permissions to "owner's" and give Vicky
> read/write permissions to the *query*. She'll then have the same
> permissions to the query data as the owner of the query.
>
> HTH - Keith.
> www.keithwilby.com

Awesome! Thanks Keith! So Vicky won't be able to access the table directly,
but will be able to access it (just some fields) through the query. Nice!

Just one more thing. I will follow your recommendation on splitting the
database. That makes sense. However I do not want to apply security on the
query (or any objects) stored in the Front-end. I would prefer to maintain
all security sets on the Back-end. However if I do that, I am going to have
to find a way so the Front-end (Vicky's form) reference or feeds from the
query on the Back-end. I guess there is no such thing as a Linked-Query.
What are your thoughts about it? I'm new with this Access security stuff,
but I started to like it ;-)

31 Jul 2006 7:13 AM

Keith Wilby

"It'sss Johnny" <nowhere@somewhere.there> wrote in message
news:NPCyg.497447$Fs1.333963@bgtnsc05-news.ops.worldnet.att.net...

> I do not want to apply security on the query (or any objects) stored in
> the Front-end.

But that's the way you do it. You would secure your BE using the same
workgroup as your FE and then there's nothing more to do to the BE, you
control access to the data through security permissions on the FE.

> I would prefer to maintain all security sets on the Back-end. However if I
> do that, I am going to have to find a way so the Front-end (Vicky's form)
> reference or feeds from the query on the Back-end. I guess there is no
> such thing as a Linked-Query. What are your thoughts about it? I'm new
> with this Access security stuff, but I started to like it ;-)
>
>

Keith.

31 Jul 2006 11:57 PM

It'sss Johnny

Got it! I have never implemented security on Access, but thanks to your help
I'm on track now. You have been very helpful... Thanks a Billion Keith!!!

Show quoteHide quote

"Keith Wilby" wrote:

> "It'sss Johnny" wrote:
>> I do not want to apply security on the query (or any objects) stored in
>> the Front-end.
>
> But that's the way you do it. You would secure your BE using the same
> workgroup as your FE and then there's nothing more to do to the BE, you
> control access to the data through security permissions on the FE.
>
>> I would prefer to maintain all security sets on the Back-end. However if
>> I do that, I am going to have to find a way so the Front-end (Vicky's
>> form) reference or feeds from the query on the Back-end. I guess there is
>> no such thing as a Linked-Query. What are your thoughts about it? I'm new
>> with this Access security stuff, but I started to like it ;-)
>>
>>
>
> Keith.

1 Aug 2006 7:24 AM

Keith Wilby

"It'sss Johnny" <nowhere@somewhere.there> wrote in message
news:GNwzg.200447$mF2.176719@bgtnsc04-news.ops.worldnet.att.net...
> Got it! I have never implemented security on Access, but thanks to your
> help I'm on track now. You have been very helpful... Thanks a Billion
> Keith!!!
>

No problem, glad to help. Don't forget that the FAQ from MS is essential
reading (link on my web site) and to always work on a copy of your files
since it's so easy to lock yourself out.

Regards,
Keith.
www.keithwilby.com