How to apply security to a column in a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for a way to let the bulk of the users of a MDB read/update most
of the fields (columns) in a table, but only allow a select few users to
update some of the other columns of that table. Is there a way to do this in
a single table, or must I break that into two tables? If in two tables, can
I handle this thru Jet and the Security Wizard?
I am working with Access2000.
 
Ed said:
I am looking for a way to let the bulk of the users of a MDB
read/update most of the fields (columns) in a table, but only allow a
select few users to update some of the other columns of that table.
Is there a way to do this in a single table, or must I break that
into two tables? If in two tables, can I handle this thru Jet and
the Security Wizard?
I am working with Access2000.
Click to expand...

You would not handle this through table security. You should create queries
for the data interaction. You then have control over what columns are
displayed.
 
Thanks, Joan, for your response. I am not an expert in Jet security by any
means, but are you suggesting that I can somehow "lock down" the tables
themselves, but drive the access to the data via queuries, where your
suggestion would split the access appropriately? My initial concern about
your response was how to prevent a user from just hopping into the table(s)
directly, even if the forms are driven by queries.

Ed
 
Ed said:
Thanks, Joan, for your response. I am not an expert in Jet security
by any means, but are you suggesting that I can somehow "lock down"
the tables themselves, but drive the access to the data via queuries,
where your suggestion would split the access appropriately? My
initial concern about your response was how to prevent a user from
just hopping into the table(s) directly, even if the forms are driven
by queries.
Click to expand...

Yes. Whether it is split or not, doesn't matter as far as this is
concerned. However, you should still split the database anyway.

You need to implement user-level security. It isn't trivial, but if you
take the time to study before you plunge, you can accomplish what you want.

Security FAQ - download and study this.
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Although the whitepaper is old, it contains information to help you
understand security.

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm
Since you are using 2000, do not use the security wizard.

You can deny all permissions to the tables, and instead use 'run with owner
permissions' queries. You give the users the required permissions on the
queries/forms/reports. When they run the query, they will be able to access
the underlying tables based on the query owner's permissions on the tables.
So even though they don't have any permission on the tables, when they run
the query they can access the data.

You just ensure that the owner of any query has full permissions on the
tables. The users will still be restricted to the permissions you grant on
the queries. More explanation in the FAQ and white paper.

I suggest you practice on a copy of your database.
 
Thank You, Joan!

One last question - you noted that I should NOT use the Security Wizard in
Access 2000. I'm holding the Getz, Litwin, Gilbert Handbook where they
recommend it. What are the principal issue(s) with use of the wizard?
 
Ed said:
Thank You, Joan!

One last question - you noted that I should NOT use the Security
Wizard in Access 2000. I'm holding the Getz, Litwin, Gilbert
Handbook where they recommend it. What are the principal issue(s)
with use of the wizard?
Click to expand...

It doesn't work. (tsk, tsk, you haven't even made it to page 2 of the
security FAQ?) I quote:
"The Access 2000 Security Wizard removes permissions to the point where they
are not visible on the security menus, but testing has revealed that in
Access 2000 it is possible to open a database by using the default workgroup
information file regardless of the menu settings."

You need to secure it manually. See the steps at
www.jmwild.com/AccessSecurity.htm
 
Back
Top