Locking down an Access 2007 ACCDE - Database objects, Tables, Queries, Data
Hi everyone,
I am working on a project where the final solution is an ACCDE.
I’m looking at trying to keep prying eyes out of the database. Although the data is stored in SQL Server and there are no linked tables to the real data. There are some local tables that are needed at start up. I have successfully hidden the local tables away from the import window and all other objects listed are grayed out, so far so good. However, the tables can still be accessed via VBA from another database.
Here are my questions:
1. Is it possible to hide the tables in an ACCDE from access via VBA? I created another database and then opened the ACCDE via code and was able to pull all the data out of the tables. Huge security concern here.
2. If I can’t hide the tables from VBA, is it possible to encrypt the database without setting a database password?
3. If you select menus/toolbars from the import window, it will bring in those objects as well. Is it possible to hide these as well? This is low on my concern list, but still a concern.
4. Bonus – Is it possible to change the icon on the Office button?
5. Bonus – Is it possible to change the text in the QAT to remove the word ‘Access’?
Please don’t point me to a link unless it is the exact answer. So many times, people race to get the points and throw up a link. I’ve searched the web and EE for these answers and have not found an acceptable solution.
Thanks,
Mike
I am working on a project where the final solution is an ACCDE.
I’m looking at trying to keep prying eyes out of the database. Although the data is stored in SQL Server and there are no linked tables to the real data. There are some local tables that are needed at start up. I have successfully hidden the local tables away from the import window and all other objects listed are grayed out, so far so good. However, the tables can still be accessed via VBA from another database.
Here are my questions:
1. Is it possible to hide the tables in an ACCDE from access via VBA? I created another database and then opened the ACCDE via code and was able to pull all the data out of the tables. Huge security concern here.
2. If I can’t hide the tables from VBA, is it possible to encrypt the database without setting a database password?
3. If you select menus/toolbars from the import window, it will bring in those objects as well. Is it possible to hide these as well? This is low on my concern list, but still a concern.
4. Bonus – Is it possible to change the icon on the Office button?
5. Bonus – Is it possible to change the text in the QAT to remove the word ‘Access’?
Please don’t point me to a link unless it is the exact answer. So many times, people race to get the points and throw up a link. I’ve searched the web and EE for these answers and have not found an acceptable solution.
Thanks,
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for responding. Just an FYI – I’ve been creating Access solutions since 1990. My current project has over 60k lines of code and it’s only ½ done. I’m trying hard to lock it down from smart “prying” end users.
1. The tables are not visible in the linked table manager. I was able to hide them from that window. However, they are still accessible via VBA from another database. Is it possible to make the tables read-only from a design perspective? Once I'm ready to transfer the application from a dev environment to a production environment. What about prefixing the tables with MSys...I wonder if that would at least add a level of confusion through obfuscation.
2. That's what I thought about the encryption; I do not want to complicate the startup process by adding a password to the mix. The shortcut that I create would need to add the command line for opening it with the password. All the important data is in SQL Server 2008 (currently 146 tables), there are a few local tables that are needed on startup. All the ribbon data is contained with these local tables. This is really what I'm trying to lock down, or at least make it read only.
3. I have custom Ribbons and turn off the Access Ribbons so only my ribbon is active. The problem is that I have popup menus (right click shortcut menus). I use Access 2003 to create them and then import them into Access 2007. They work like a champ. It's a shame that Access 2007 doesn't give you a tool to edit shortcut menus.
4 and 5. I have a custom Ribbon...just trying to remove the final two pieces of the puzzle that show the word Access.
Thanks,
Mike
1. The tables are not visible in the linked table manager. I was able to hide them from that window. However, they are still accessible via VBA from another database. Is it possible to make the tables read-only from a design perspective? Once I'm ready to transfer the application from a dev environment to a production environment. What about prefixing the tables with MSys...I wonder if that would at least add a level of confusion through obfuscation.
2. That's what I thought about the encryption; I do not want to complicate the startup process by adding a password to the mix. The shortcut that I create would need to add the command line for opening it with the password. All the important data is in SQL Server 2008 (currently 146 tables), there are a few local tables that are needed on startup. All the ribbon data is contained with these local tables. This is really what I'm trying to lock down, or at least make it read only.
3. I have custom Ribbons and turn off the Access Ribbons so only my ribbon is active. The problem is that I have popup menus (right click shortcut menus). I use Access 2003 to create them and then import them into Access 2007. They work like a champ. It's a shame that Access 2007 doesn't give you a tool to edit shortcut menus.
4 and 5. I have a custom Ribbon...just trying to remove the final two pieces of the puzzle that show the word Access.
Thanks,
Mike
1. I'm not sure what you mean by "read-only", but users must have read/write permissions on your objects in order to work with them, and Access cannot differentiate between "development" and "production" users. In other words, if a user can see the file, generally they can do what they wish with it. You can prefix them with USys, which would hide them, unless your intruder has ticked "Show Hidden and System Files" in their Access options.
2. You cannot make it read only, unfortunately. You could populate those tables at startup, and then "kill" them when the app shuts down. Seems like a tremendous amount of work for little gain, however.
3. Microsoft is entirely and 100% invested in the Ribbon interface. They are not going backwards on this. Trust me when I say this.
4 and 5. We get this constantly, and the answer is always the same: If you don't want your users to think this is done in Access, then use another platform.
2. You cannot make it read only, unfortunately. You could populate those tables at startup, and then "kill" them when the app shuts down. Seems like a tremendous amount of work for little gain, however.
3. Microsoft is entirely and 100% invested in the Ribbon interface. They are not going backwards on this. Trust me when I say this.
4 and 5. We get this constantly, and the answer is always the same: If you don't want your users to think this is done in Access, then use another platform.
ASKER
Thanks again for your comments.
I was able to make the tables read-only by setting the Validation Rule at the table level to False. I just add this as a step when I take the application from dev to prod. Now, even if they gain access to the tables via VBA, the data is read-only.
Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind.
I love the Ribbon, the menus are gone. However, short cut menus (right click popup menus) are still used in 2007 even by Access itself. It just would be nice to be able to edit those CommandBar objects inside 2007 and not in an earlier version.
Thanks,
Mike
I was able to make the tables read-only by setting the Validation Rule at the table level to False. I just add this as a step when I take the application from dev to prod. Now, even if they gain access to the tables via VBA, the data is read-only.
Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind.
I love the Ribbon, the menus are gone. However, short cut menus (right click popup menus) are still used in 2007 even by Access itself. It just would be nice to be able to edit those CommandBar objects inside 2007 and not in an earlier version.
Thanks,
Mike
Can you please elaborate on your first comment re: the validation rule? I'm not clear on what you mean. A Validation Rule has no bearing on whether data is read/write, unless I'm not understanding something.
"Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind. "
Is that what you were trying to achieve? I'm not clear on your last comment, whether it was a response to mine, or whether it was more information.
"Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind. "
Is that what you were trying to achieve? I'm not clear on your last comment, whether it was a response to mine, or whether it was more information.
If I set the Validation Rule to "False", I get an error. In a sense, that does make the data "read only", which may achieve what you're after
ASKER
There is a validation rule at the table level...not the field level. Open a table in design view and open the properties window and enter False in the validation rule. Save the table and click yet to apply the rule even though the current data violates the rule. Then open the table in datasheet view and try to edit the data, it won't let you because any of the changes violate the validation rule of False.
My response "Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind." was meant for you because you said in your response that "....You can prefix them with USys, which would hide them, unless your intruder has ticked "Show Hidden and System Files" in their Access options." Even if the user has those options checked, none of my tables (system or user) show up in the import object window.
My goal was to keep the user completely out of any of the tables in the ACCDE. However, since that doesn't seem to be an option, I will have to settle for all the user tables being Read-Only.
Thanks,
Mike
My response "Also, none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects. Out of sight out of mind." was meant for you because you said in your response that "....You can prefix them with USys, which would hide them, unless your intruder has ticked "Show Hidden and System Files" in their Access options." Even if the user has those options checked, none of my tables (system or user) show up in the import object window.
My goal was to keep the user completely out of any of the tables in the ACCDE. However, since that doesn't seem to be an option, I will have to settle for all the user tables being Read-Only.
Thanks,
Mike
ASKER
Please read the entire thread as i did find a solutiion that allows you to make the tables read-only when accessed via VBA.
Thanks,
Mike
Thanks,
Mike
2. You cannot use native encryption without using the password. You must instead use your own encryption as I suggested above.