How do I set up different permissions to different columns in a ta

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

Guest

I have different users with different permissions to different tables. I am
trying to set up permissions not only at table level, but to column level
too. For example, two users are using table A. I need to give first user
write access to column # 1 and block column # 2 and give second user write
access to column # 2 and block column # 1. I appreciate your help and thanks
in advance.
 
Column-level security

I have seen only one database that has successfully implemented column-level security, TurboImage on an HP3000 (oh, for the glory days!) Seriously, the 'modern' way to implement that is to disallow user-level access to the base tables, and create views for the users that restrict what columns they can see.

Kevin
 
Hopping in here (because I can not see the OP's post):

I'd try this. You'll have to be well familiar with Run With Owner
Permission (RWOP) queries, and SQL. I think it should work, but I
haven't tested it.

Say the table is 'T'.

1. Create an RWOP query 'A' that selects all the columns from 'T'
except for column #1. Ensure that query is owned by a user who has
write access to 'T'. Ensure that that no other users have write access
to 'T'. Ensure that all users have read/write access to 'A'.

At this point, no users (except the owner of query 'A') can update
table 'T' directly, but /all/ users can update table 'T' /except for
column #1/ if they go through query 'A'.

2. Now create another query 'B' that inner-joins query 'A' to table
'T', for the purpose of adding column #1 to the output. Give all users
read/write access to query 'B'.

At this point, query 'B' looks just like table 'T', except that column
#1 is not updatable. You will also find that no users other-than query
'A's owner, can change the design of query 'A' (to add the missing
column).

As I said, you'll have to know what you're doing before you can
implement this. Post back & tell us!

HTH,
TC
 
So yah boo sucks to all of those who say that Access/Jet does not have
column level security!

TC
 
So yah boo sucks

Didn't know you speak Thai :-)

It is perhaps arguable, which is different to saying unarguable, whether they
wouldn't be better off by using forms to perform the required doo-de-dads,
with the usual Allowbypasskey lock-downs etc.

?

(To be "nice", the form, if they're using one, should indicate what fields are
updateable and what are not/which can automatically protect them at least
through the form. I also wondered whether slowness would be improved by the
sql method)

Chris
 
Back
Top