Software
I want to secure a column/field in a table so no one can Edit it but me, but all other columns users can edited. This column can be viewed, but just not edited.
I have security set up with workgroups, but no options to prevent users from editing a column.
Can some one help?
by bbg
by bbg
by bob sellman
One way to do it is to set up two forms for viewing/editing the table. One form would be for you only (and through the Access security control not available to other users) and would allow editing of the control containing the column in question. The second form would have the control for that column locked so users could not edit it, but they could view it.
Another approach is to use one form. If you can identify the workgroup and/or user when the form opens, you can have just one form that sets the locked property of that control to false only when the workgroup or user is you. (Define the control as locked when you design the form.)
by bob sellman
As far as I know, you cannot assign rights to individual columns in a table using Access security. (Go to Tools, Security, user/groups security, and you will only find tables, forms, reports, etc, listed, but nothing for individual columns in tables.)
You mention controlling users' access to columns in the database window and the query builder. Actually, you should not make either of these available to users. EVERYTHING should be controlled through your application.
A properly written application will always control what the user can do, so the database window and the query builder are off limits because the user actions are not controlled adequately, particularly in the situation you mention.
I've actually written a generic query builder that I use in many applications I write. It's a little tricky to write, but worth the effort because you continue to maintain control over what the users can and cannot do but it still allows users to build custom queries.
For additional security you could define all important tables as hidden (table properties) so that if users manage to bypass the startup that hides the database window those tables will still not be listed.
If you must have the database window/query builder available to users, the only way to secure that column is to define a separate table with that column and a second column that contains the primary key column of the main table. Access to the second table could then be controlled through Access security. (But other things could get more complicated.)
by bbg
by bbg
Answer 1 - I need to know how to do this for the Tables in the Db window and not the forms themselves - can this be done and if so then how?
(I will wait 2 days and if no answer/comment from you then I will reject your answer, so others will know that your answer is not what I need and maybe they will then respond - I guess it works this way).
by bbg
But please tell me how to do this for the Db Tables, (when user opens a table from the DB window or runs a query) or show to me that it cannot be done.
Answer 1 - I will wait 2 days to see if you will respond to my comment - Then I may have to reject it so others know that I need more info (I guess it works this way in this forum). Your suggestion to do this on a form may work, but what about in the rest of the application such as the query or db window?
by sl-campbell
Even though DAO/Jet/Access does not have the same capability to lock a column/field as a SQL server, you can still mimic this through DAO/Jet/Access - it is only a matter of using the right logic.
However, best is to do something like answer #1 suggests or like the poster of answer#1 and I suggested in the below thread:
https://www.techrepublic.com/forumqa/thread_detail.jhtml?thread_id=76372&src=bc
Even though this type of security takes some time to set up, once set up, it is much easier to maintain, and is portable and scaleable - unless you are only talking about locking columns for a small amount of tables.
IF you do not want to go the route suggested above, let me know and I will post additional info on how to mimic "locked" columns in Access which would also prevent users from changing those fields in the database window.
NOTE: If you have a Db already created with many tables that you want to lock columns/fields on then it is best to use a self made security model - unless the Db is small, or you are starting from scratch, or you only have a small number of tables that you want to lock columns on - otherwise, there would be too much work too change everthing.
Let me know!
by sl-campbell
Watch the URL that I mentioned above - it has a space in it between "detail.jhtml?" and "thread_id" - remove it otherwise you will end up back at this thread.
by bbg
by bbg
Sent e-mail to SL Campbell more more info.
by bbg
This discussion is closed to new replies.
Sort by Trending