• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel - Protect confidential data

J

just08in

New Member
Dear Gurus,
My requirement is like this.
I have some confidential data, which is very sensitive to share to other departments and people. We have a common template where, in multiple sheets calculations are done.
I keep my data in one of the sheets and reference it wherever the calculation is done.
I protect the sheet with password. But as you all are aware it is very easy to break password of excel with readily available macros. Could some one suggest ways to achieve the same purpose.
1) Is it possible to keep the data in one workbook in one system and refer to it through file path. I am basically sharing the file out of my office network.
2) Or is there anyway where I keep the data still in the template, but it has to be encrypted so that data is readable only with some security key encryption.
3) Or is there way atleast to not let the password breaking macro to break into the file.
Ultimately what I want to achieve is to ensure security of the data & confidentiality and actual purpose of the calculation. If any other ways other than the 1,2,3 also please let me know


PS: Dear Admins I posted the question wrongly in the excel challenge section. I see it is deleted now there, so posting here.
 
Definitely not 2 and 3. Option 1 is possible only if a) you are the only one who has access to that location on network, and b) file does not need access to that location once you send it out.

Stressing this due to implied sensitive natute: There is No way, No How, Not going to happen, Doesn't exist, Impossible, You just can't do it, way to lock down information within Microsoft Office products from a security standpoint. :p

Assuming option 1 as I described is not possible, then you are left with performing the calculations on your own, and then stripping out the results into a separate file and sending that 2nd file to other people.

At this point, you could also take a moment to think of sensitive your data is. Not knowing myself, I'm tossing this out only as a suggestion. If the data is more like "wouldn't like to get this out, but not critical", then yes, there are options we could use to make it harder to get to. But if this is sensitive, such as payroll, Intellectual property, etc., then keep the sensitive info and the reports separate.
 
Luke, thanks for taking time to reply so long.
Actually the end users do a lot of iterations for which my data is required. It is payroll data actually so cannot be shared at all. To lessen the burden we hide the sheet with password and pass on the excel template for the end users to do their calculations.
Understand that, for a straight forward person just hiding it and not setting a password also will do and for a crooked person..even setting password is no good..so for the worst case , I was trying to explore any better way to manage it. But from your post I understand it is absolutely not possible to do.

I was just thinking like having the master data of payroll in a remote location and have like a database connection with uid/passwd kind of authentication to access the data.. ( I know I sound like expecting Excel to replace Oracle)..but then I wonder if it is technically possible through macro/vbas.
 
Having a remote connection/database would be slightly better, but if the XL file can connect to the database, that means there's a way for Person X to connect as well. One of the simplest ways to access a hidden sheet in a workbook is to make a new wokrbook, and use a formula like:
=[Book1.xls]Sheet1!A1

The password on the XL sheet only protected it from being changed...not from being read. Similar problem if you had a database. Again, you could step up a bit by having a macro within XL give password to database in order to read it, making it harder, but then Person X might be able to break into the VBA (again, harder, but not impossible) and gain access that way.
 
Import your sensitive data into Access, split the data into the backend and front-end, design a form that shows only the fields that are required, a different form can be made depending on what you want groups to see and manipulate.
Put the form one the relevant DT's and the backend on the main server and allow permissions only to those who need access to the data.
You can make a report that would be emailed at the specified time to those who need to know the ongoing payroll costs.
By linking the Excel workbook to the Database updating will be all but automatic.
By splitting the database into front-end and backend users of the forms will not have access to the raw data so will not be able to cause damage.
All your data, manipulate can be done by Excel and the finished imported, your user can input if required or you Excel.
.
 
Luke M said:
Having a remote connection/database would be slightly better, but if the XL file can connect to the database, that means there's a way for Person X to connect as well. One of the simplest ways to access a hidden sheet in a workbook is to make a new wokrbook, and use a formula like:
=[Book1.xls]Sheet1!A1

The password on the XL sheet only protected it from being changed...not from being read. Similar problem if you had a database. Again, you could step up a bit by having a macro within XL give password to database in order to read it, making it harder, but then Person X might be able to break into the VBA (again, harder, but not impossible) and gain access that way.
Click to expand...

You are right Luke, the person if wants to break in, will always break into it.
 
bobhc said:
Import your sensitive data into Access, split the data into the backend and front-end, design a form that shows only the fields that are required, a different form can be made depending on what you want groups to see and manipulate.
Put the form one the relevant DT's and the backend on the main server and allow permissions only to those who need access to the data.
You can make a report that would be emailed at the specified time to those who need to know the ongoing payroll costs.
By linking the Excel workbook to the Database updating will be all but automatic.
By splitting the database into front-end and backend users of the forms will not have access to the raw data so will not be able to cause damage.
All your data, manipulate can be done by Excel and the finished imported, your user can input if required or you Excel.
.
Click to expand...
BOB,
I dont have any form requirement. I just need the data to be used in cells of the end-users..so is there way access can be linked to excel?
I have to confess that your idea sounded like some sci-fi couldnt understand in first-read.
 
I tried your idea BOB but couldnt achieve it still.. in mean time as workaround, I had written vba code to hide sheets and locked the project.
 
You must log in or register to reply here.
Back
Top