Results 1 to 3 of 3
2010-09-10, 19:26 #1
- Join Date
- Apr 2003
- Beaverton, Oregon, USA
- Thanked 0 Times in 0 Posts
I'm building an MS Access application as a front end to an Oracle database, using linked tables and server-side stored procedures. The question I have is with securing the ODBC connection information. Using Access 2010, hovering over the linked table shows everything in the connection string except the password. That's not good, but I can live with it. However, Access also stores the ODBC connect informatin (including the password) in MSysObjects. A savy user could get to this info and gain access to stuff they shouldn't. But the biggest issue I have is calling stored procedures in code. In order to do this you have to supply the connect string (or point to it in MSysObjects).
So, my question: is there a better way? The DB is currently a .mdb, would it be more secure as an accdb? What about mde/accde?
Thanks for any help. Looking forward to hearing what others are doing.
2010-09-11, 02:38 #2
- Join Date
- Dec 2009
- Thanked 1,011 Times in 940 Posts
If the network is a domain you could use Windows authentication rather than specific user/pass?
2010-09-11, 08:48 #3
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 60 Times in 60 Posts
If you were using SQL Server rather than Oracle, what Paul suggests is what we nearly always do. However I don't know the internals of Oracle security and whether it can use Windows Integrated Security - at a minimum I believe you would have to define all of the User Security settings in Oracle rather than being able to add users as you can with SQL Server. See this link which asks the same question. As for calling stored procedures, you do have to define the connect string, although you can define it globally in a VBA module.
As to the Access database format, I don't see any advantage to using an accdb format - in fact Access User Security is not supported in it, so blocking users from seeing the system level tables is not possible. However Access 2010 doesn't support the complete administration of Access User Security, so you would need to drop back to Access 2003 to do the initial setup. I would look at using an mde format for what you deploy to users as that will prevent all but the most dedicated hackers from getting into object designs or the VBA modules. Finally, I would plan on deploying a copy to each user workstation rather than using a shared copy from a server.Wendell