Access 2003 field protection
I am having an access table which have few fields. Some of my user had accidently deleted few fields in the table.
Is there any way to delete protect the table. But I want my users to read & write. Also I should have the access to delete the table without any issues.
Is there any way to delete protect the table. But I want my users to read & write. Also I should have the access to delete the table without any issues.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
therealmongoose
In short you need to create a workgroup infromation file that is associated with your database - this defines the permissions that different users have over the database and can control wether a user is or is not able to delete a record...
As TRM said, the only way to control this is through User Level Security, but please be VERY careful if you go down this path. This is a complex and difficult subject to understand, and is not even available in Access 2007 if you think you might upgrade this to 2007 and use the new format at some point.
The link provided gives a good background, but Joan Wild's page (www.jmwild.com) is a great source for more info, and includes a step-by-step method for correctly securing your database. Merely running the Security Wizard is sure to cause more harm than good, and a thorough understanding of what ULS does (and what it cannot do) is important before you venture down this path.
Note, however, that you cannot specifically protect an individual column in a table. Access doesn't have that sort of granularity. You can move to a more powerful db engine, like SQL Server Express, which has a much more robust security architecture, and would allow you must greater latitude in how you control these things. SSE is free, easy to install and maintain, and integrates very nicely with Access.
Even in a secured Access database, users can still get to and delete tables unless you implement Run With Owner Permission (RWOP) queries for all data access. RWOP queries allow users to get at the data while also allowing the developer to remove all permissions from the tables (so the user, in effect, has no permissions on the tables and thus cannot delete that table, nor any portion). This, of course, increases the workload on you, the developer, but that's the tradeoff for security.
That said: A workgroup file doesn't contain ANY permissions ... all it does is contain Users, Groups, User+Group associations, and Passwords (encrypted, of course). The database you're securing contains all Permissions, and the Workgroup + Database entail the whole security package. Basically, a workgroup file does little more than authenticate the user, define the groups they are joined to, and provide the db with a SID that it can use when reading/writing permissions. The database then uses this SID to determine whether that user (or a Group that user belongs to) has sufficient permissions to perform the requested action.
The link provided gives a good background, but Joan Wild's page (www.jmwild.com) is a great source for more info, and includes a step-by-step method for correctly securing your database. Merely running the Security Wizard is sure to cause more harm than good, and a thorough understanding of what ULS does (and what it cannot do) is important before you venture down this path.
Note, however, that you cannot specifically protect an individual column in a table. Access doesn't have that sort of granularity. You can move to a more powerful db engine, like SQL Server Express, which has a much more robust security architecture, and would allow you must greater latitude in how you control these things. SSE is free, easy to install and maintain, and integrates very nicely with Access.
Even in a secured Access database, users can still get to and delete tables unless you implement Run With Owner Permission (RWOP) queries for all data access. RWOP queries allow users to get at the data while also allowing the developer to remove all permissions from the tables (so the user, in effect, has no permissions on the tables and thus cannot delete that table, nor any portion). This, of course, increases the workload on you, the developer, but that's the tradeoff for security.
That said: A workgroup file doesn't contain ANY permissions ... all it does is contain Users, Groups, User+Group associations, and Passwords (encrypted, of course). The database you're securing contains all Permissions, and the Workgroup + Database entail the whole security package. Basically, a workgroup file does little more than authenticate the user, define the groups they are joined to, and provide the db with a SID that it can use when reading/writing permissions. The database then uses this SID to determine whether that user (or a Group that user belongs to) has sufficient permissions to perform the requested action.
Split the database into a front-end/back-end setup and park the back-end on your server. Build a user interface in the front-end with forms that are bound to the specific tables. There you can control what the user can or cannot do to the linked table using the form. You can even create your own user and group tables thus bypassing the outdated built-in Acesss User Level Security.
ET
ET