How to implement security in access

[jerill]

Hi,



I have upsized from access to sql server 2008. I am accessing my access forms/application from client pc which is in domain and sql server is in workgroup.

So today, users just runs access forms from client machine and views / edits data. That is fine. But I don't want that all users should be able to edit the data.

How can i implement security in access so that only 1 user can change data and than as many user ( it may grow ) everyone just can view the data in access forms but cannot change it ? I think, i have access 2003.

2) I see tablename_local tables in access after upsizing to sql server. Can I remove this local tables in access.

[Toyman]

Hi,

I have upsized from access to sql server 2008. I am accessing my access forms/application from client pc which is in domain and sql server is in workgroup.

So today, users just runs access forms from client machine and views / edits data. That is fine. But I don't want that all users should be able to edit the data.

How can i implement security in access so that only 1 user can change data and than as many user ( it may grow ) everyone just can view the data in access forms but cannot change it ? I think, i have access 2003.

2) I see tablename_local tables in access after upsizing to sql server. Can I remove this local tables in access.

If you are still using Access 2003 you can still use the build-in user level security. However, if you want to eventually upgrade to 2007 or 2010, that feature will not be available. You can always create your own security; I have attached a simple demo in this post: https://www.accessforums.net/access/...vel-18216.html

This gives a sample of how you might use a log on information to decide what access a user has in the db. You can then expand on the sample and create your own.

As for users accessing the tables, queries and db design, I always deploy my db in a .mde or .accde format with the AllowByPassKey disabled. The .mde or .accde format prevents your users from access the design of your db to include codes. The AllowByPassKey disabled prevents the user from accessing the db window by pressing the shift key. Be aware, experience users can always enable the by pass key. All this is just to put up hurdles for your users to discourage them from trying

[jerill]

I have access 2003 and that is upsize to Sql server 2008. So can I use " built in security " as you mentioned without using your code or I will have to use your code to implement security.

[RayMilhon]

Remove the local tables. Assign your Data Security within SQL Server. Use Toyman's suggestion to prevent users from modifying the Access Front End.

[pbaldy]

An option in a network environment, which would allow the app to be converted to later versions:

http://www.accesssecurityblog.com/po...Directory.aspx

[RayMilhon]

Excellent link Paul, good info I've added the RSS link to my list.

[pbaldy]

Thanks; Tom put a lot of work into that, and it worked well when I tested it.

[Toyman]

I have access 2003 and that is upsize to Sql server 2008. So can I use " built in security " as you mentioned without using your code or I will have to use your code to implement security.

I would use both the build in security in Sql Server 2008 and use the code in access to totally control what type of record your user can access and how they access it.

[jerill]

Hi Ray & Paul,

Thanks for your reply.

1) Ray, i will go ahead and delete the tablename_local tables.

2) Security : SQL server is in workgroup. So if i try to add any user on database, it will not identify it. I can create local user ( user with same name and password ) on server machine, and than add to sql server database. But when same user access front end access form / report, how will that security play role from backend.
Right now, user opens access form and runs the form without entering any username or password.

So do I need to add prompt with code given by Toyman in front end to stop users from editing data ? I am bit confused on security.

[RayMilhon]

The Security on the backend will Effect only the Data Stored in SQL Server. You will need to add those users to SQL Server and assign them the appropriate rights to the database.

In MS Access if the user has readonly rights in SQL Server and the user edits the data in MS Access as soon as Access attempts to update the field in SQL Server an error will be generated. Depending on how you're updating the records in Access you will need to account for these errors.

As Toyman suggested both should be utilized. SQL Server is simply a fail safe to absolutely protect your data from users with read-only access.

[jerill]

Thanks everyone for their response. I will implement it and try to update you on the results.