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.

 

[Pat Hartman]

An ADP won't stop users from linking directly to the SQL Server tables.

The solution I use is to have the DBA define individual logon ids for each user and to use the same password for each user. In the Access app, I also manage users and here the user has control over his password. So he logs into the app with his userID and password and behind the scenes, the app logs into the SQL Server using the given user ID and the common password. Since the users are not given the actual password for their SQL Server account, they can't just create a new Access app to link to the BE and muck around directly. They don't know a valid userId and password combination.

You can also modify the userID for SQL server so it is not identical to their Windows ID. For example, insert 123 after character 4 of the UserID. This will prevent the user from making an educated guess but the application knows the trick to modifying the userID.

 

[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.