MrExcel
.
- Joined
- Feb 8, 2002
- Messages
- 3,417
- Office Version
- 365
- Platform
- Windows
Hello everyone -
On a recent trip to California, I met up with some Excel people at a bar. Lots of great discussion
, but one chap pointed out what seems like a security hole in Excel. I am passing it along here.
WorkbookA has some employee ID. You link to WorkbookB, range A2:S99 to grab employee name from column S of the table. It happens that there is sensitive information in the middle of the A:S employee data.
You share WorkbookA with others. You figure it is no problem since the others don't have WorkbookB. You might have even suppressed the Update Links? message when they open WorkbookA.
It turns out that WorkbookA contains a cached version of all 1881 cells from A2:S99 in WorkbookB. You can hack the VLOOKUP formula, copy to point to just A2, then drag that new formula to reveal all of the cells in WorkbookB's A2:S99. Excel requires you to browse to the missing WorkbookB, but if you click Cancel (twice), they happily reveal that potentially sensitive data.
This chap at the bar discovered all of the sensitive information from the co-workers WorkbookB.
Once I started to think about how Excel handles links to closed workbooks, it wasn't that surprising that they have to cache the last known cells from WorkbookB in WorkbookA. If you really only needed EmpID in A and EmpName in S, it would help to set up a smaller table in S:T to prevent Excel from caching all of the middle columns.
Two interesting points in the podcast:
2:20 mark: Opening WorkbookA on a disconnected computer: Learn Excel - Links to Closed Workbook Stored in Workbook - Podcast 1774 - YouTube
5:02 mark: Cracking open the Excel file to find the cache of WorkbookA values: Learn Excel - Links to Closed Workbook Stored in Workbook - Podcast 1774 - YouTube
Again, once you think it through, it is very intuitive, but if you see a co-worker linking to sensitive data in an external workbook, you might want to set them straight.
Bill
On a recent trip to California, I met up with some Excel people at a bar. Lots of great discussion
, but one chap pointed out what seems like a security hole in Excel. I am passing it along here.WorkbookA has some employee ID. You link to WorkbookB, range A2:S99 to grab employee name from column S of the table. It happens that there is sensitive information in the middle of the A:S employee data.
You share WorkbookA with others. You figure it is no problem since the others don't have WorkbookB. You might have even suppressed the Update Links? message when they open WorkbookA.
It turns out that WorkbookA contains a cached version of all 1881 cells from A2:S99 in WorkbookB. You can hack the VLOOKUP formula, copy to point to just A2, then drag that new formula to reveal all of the cells in WorkbookB's A2:S99. Excel requires you to browse to the missing WorkbookB, but if you click Cancel (twice), they happily reveal that potentially sensitive data.
This chap at the bar discovered all of the sensitive information from the co-workers WorkbookB.
Once I started to think about how Excel handles links to closed workbooks, it wasn't that surprising that they have to cache the last known cells from WorkbookB in WorkbookA. If you really only needed EmpID in A and EmpName in S, it would help to set up a smaller table in S:T to prevent Excel from caching all of the middle columns.
Two interesting points in the podcast:
2:20 mark: Opening WorkbookA on a disconnected computer: Learn Excel - Links to Closed Workbook Stored in Workbook - Podcast 1774 - YouTube
5:02 mark: Cracking open the Excel file to find the cache of WorkbookA values: Learn Excel - Links to Closed Workbook Stored in Workbook - Podcast 1774 - YouTube
Again, once you think it through, it is very intuitive, but if you see a co-worker linking to sensitive data in an external workbook, you might want to set them straight.
Bill
