User level security in access 2010

[Kevin Cockburn]

How is the best way to prevent users from having sight of sensitive data stored in certain tables in my databse. I'd like only administrators level access to all data, and data capturers access to only limited forms, tables etc. The databse will operateon a LAN only. Thanks all!

 

[speakers_86]

First off, make sure you don't operate Access over a wireless network. Your lan has to be wired.

You probably will want a login form that opens with the database, and in your employee table there should be a field defining user rights. Whenever a form is attempted to be opened, it will first check that the current user has sufficient privileges to access the form. Or it can make sensitive fields hidden.

 

[foxtrot1961]

Hi, what is the security problem with wireless? Thanks

 

[GinaWhipp]

IMHO, the only way to get table level security is to put your tables in SQL Server and use that security. As for wireless, you take a chance that your database will become corrupted.

 

[gemma-the-husky]

Kevin, it really depends whether you are looking at "casual" security, or "hard" security.

You can provide forms, that either show or don't show data depending on the user. The problem is that if you really want to protect the data against determined snooping, you need to prevent users trying to get at the database window, or even creating a new database, and connecting/importing your tables. You could possibly encrypt the data in the tables, but this works only if you aren't trying to search the encrypted data.

Hence Gina's suggestion above to use SQL Server - since a SQL database is not a normal "windows" file, and can be protected more easily.

 

[foxtrot1961]

How easy is it to move ones msaccess2007 database to SQL? Will i have to change a lot of my existing stuff?

 

[GinaWhipp]

Only the tables will be moved, so...

1. Split existing Access database
2. Move backend with all the tables to the SQL Server database tables
3. Use the security of the Server

...your forms, queries, etc... will not have to change UNLESS you need to optimize because of complex queries. You will just need someone to assist with security part if you are not familar with it.

 

[speakers_86]

You may need to research the data types in SQL Server, as they are a bit different. Also, hopefully you are not using mult-fields.

 

[gemma-the-husky]

I think others with more experience may say SQL is easy, but I feel that SQL requires a level of knowledge and expertise a lot more advanced than using Access tables.

Converting to SQL is not a trivial exercise.