Security Design Access 2007 FE and SQL server 2008 backend

[ChiOscar]

Can anyone please advise on a security design to use Database Roles in SQL server 2008 when using MS Acess 2007 as a front end.

How can I integrated DB Roles into MS Access when the security use to be handled by the forms when the database was is Acsess?

I link the database through ODBC. I would much like to use .ADP but since Microsoft will stop supporting it I don't see it as a viable option.

Thanks

 

[michaeljryan78]

I created a Users table on the front end and I have a function that looks at the user table for the user ID and assigns privleges. I use the TempVars collection.

tblUsers
userID (PK)
AccessDev (boolean)
Access Supv (boolean)
Access ReadOnly (boolean)

I then assign the TempVars!AccessDev to True if selected in the table. On the load event of the forms, I check each permission and load the security (enable, disable locks) for each control, each role.

 

[ChiOscar]

That tells me that you are using the forms to control security which works great. I did the same thing and worked beautifully when my database was in Access.

My new issue is that I need people to enter data specifically the way the form allows them to which is designed based on our processes and work flow. However, whats to stop someone from linking ODBC on another access application and entering data without regards to out process. This is why I wanted to use .adp. Its a shame that Microsoft is not going to support it anymore.

 

[ChiOscar]

ADP maybe will not stop people from linking to the SQL server but i can stop anyone from doing anything by not giving them access to the database and in turn use an APP ROLE to process the data in a controlled front end application.

I think your method is an excellent to secure the back end. Unfortunately, we use Windows Authentication to log in and in no way is the DBA going to change.