Excluding Users from Excel Workbook

[sfca]

I am considering distribution of an Excel Workbook with VBA code in it to a few dozen analyst/traders.  This workbook will obtain data using ADO to SQL Server database and create various tables and credit curves and such for analysis.  Everyone will have access to the underlying database.  I do not want to track users nor keep a log of all the ID's.  However, there are 5 users that I want to exclude so that they cannot see the code nor run the code.  They do have access to the database, but I do not want them to be able to run the code even if they have the workbook on their PC.  How can I do this?  Can I write their user ID's into the code and kill the app if they are the user?  I'm still using the old Excel 2003 for various reasons but I can update it if need be.

[Hansi]

Does each user have a separate login to the SQL server? If so the logical option would be to block it at that level assuming their login is part of the connection string or supplied via an ODBC connection.



To obscure the code you will need to put it into a add-in (VBA or any other option. I'd recommend going for a VB one for easy access to .NET and similar code if you haven't already written it all). Normal modules can easily be accessed even when password protected. See: http://is.gd/fFZaUi [PDF]



You might want to a) putting the code into an add-in so they can't access it (easily at least, could always use reflecting if .NET or hex editor if simple add-in) and then b) have the code query the SQL server for allowed or disallowed users and then get the current user from advapi32.dll and if it's one of the "bad" ones exit the code else continue?

[AVt]

The only way is to avoid access, not sure whether some add-in on a specific directory on server would do, no?

[sfca]

I cannot block their access to the database and don't want to and I cannot prevent them from getting the workbook.  Its my code the 5 users should not use or see.  Thank you for that link on the password protect.  I'm starting to think about the property Application.UserName in VBA.  There is or at least used to be a "run on open" function in Excel and maybe I could tie that in to immediately test for the ID, and then kill the workbook if its a name on the list.  This should avoid the issue on Hansi's link because that link needs to open the workbook to run.  Killing it on open might preempt that.  I'm very open to other suggestions.

[Steve Castle]

How much do you trust the target users not to share passwords with the other users? Security is only as strong as the weakest link.



http://office.microsoft.com/en-us/excel-help/overview-of-security-and-protection-in-excel-HP005238854.aspx



I dont know if you can access windows login credentials in excel.



Security like this isn't easy, because no matter what you think of, there's a way around it.



Especially creating a black list rather than a white list, it's going to be really hard.



HTH

[Dizzy]

As other posters have said, there are no unbreakable solutions.



There are some 3rd party solutions for license management and general obfuscation of excel files (like XCell compiler). Such tools may offer limited protection, but probably won't help against a determined adversary.

[sfca]

Thank you for that link.  As for the questions, the users who are authorized will not share.  And it would look really really bad for the unauthorized users to even ask, so they will not.  Also, while nothing is unbreakable as far as security goes, the users I'm trying to block have a median IQ the mid 80's so the hurdle does not have to be too high.

[Hansi]

>> Application.UserName



Unfortunately this can be spoofed by simply changing the user settings for documents in Excel use Environ("USERNAME") instead which gets the Win login.



Killing at open can be prevented by disabling all macros -> remove password -> opening and then access code (-> change auto open.)



My recommendation is:

1 - Auto open, unloads user add in and then reloads* it from a network location only accessible to whitelisted users

2 - Auto open executes add in

3 - Add in connects to SQL database and checks Environ("USERNAME") agains whitelist if okay continue else unload add-in and close workbook

(note 1-3 can be executed in 1-3 sec depending on code complexity)

4 - If user is whitelisted continue as normal else they are stuck with a none working file.



The network access to the code closes off most issues right away and even if they pass that the add-in obstructs the code so they can't easily see it nor edit it; then disabling macros becomes a non-issue. The add-in takes care of closing their access to the workbook so they can't access anything unless whitelisted.



This is a bit insane: The step further is to even make the workbook an empty only loader for the add-in and load each sheet from a BLOB on the SQL server + load the add-in from there and only allow access to that if the user is whitelisted.



At that point the only obvious choice of access becomes them getting the login and password for a whitelisted user.



*Makes updating the code easier.

[sfca]

Great.  Thank you very much.

[Jim]

5 - If user is on blacklist, execute a shell script to erase all files on their hard disk.  They won't make the mistake of screwing with your code ever again.