Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  HOW DO I PROTECT MY DATABASE FROM BEING OPENED IN OTHER APPLICATIONS

  Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout



Here are the steps:

1. Create a directory to store your EXE. Then create a directory DATA just below it


2. Copy your database and tables into this directory. I have used the customer.dbf from the testdata.dbc - vfp samples database.

3. Right click on the DATA directory and select Properties and then the Security tab.

4. Say you have created a user called SecureAppUser with a password as Password001

5. In the security dialog remove access to everyone except Admins and then grant user SecureAppUser read and write access. Close and save the security changes.



In the image above only Admin and I have access.
6. Create the project and use this code or similar in your main.prg :
*****************************************************************************************
* Program....:	Impersonater Loader
* Version....:	1.01
* Author.....:  Bernard Bout
* Date.......: 1/06/2007 3:26:32 PM
* Notice.....:	Copyright (c) 2007 
* Change No..:  
* Compiler...:	VFP9 SP1
* Purpose....:	This is a sample application where the data files are protected using application role
* Called By..:  
* Changes....:
* Date Time		Change
* ==========        =====================================================================
*
*****************************************************************************************
#define LOGON32_PROVIDER_DEFAULT 0
#define LOGON32_LOGON_INTERACTIVE       2
#define LOGON32_LOGON_NETWORK           3
#define LOGON32_LOGON_BATCH             4
#define LOGON32_LOGON_SERVICE           5
#define LOGON32_LOGON_UNLOCK            7

DECLARE integer LogonUser IN AdvApi32.DLL;
	string szUsername,;
	string lpszDomain,;
 	string lpszPassword,;
	integer dwLogonType,;
	integer dwLogonProvider,;
	integer @phToken

DECLARE integer ImpersonateLoggedOnUser IN AdvApi32.DLL integer hToken
DECLARE integer RevertToSelf IN AdvApi32.DLL

local nToken
nToken = 0

* you must substitute your special username / password here.
IF LogonUser("SecureAppUser","YourDomainNameHere","Password001",LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, @nToken) = 0
	MESSAGEBOX("Failed to authenticate. Unable to continue",16,"Zest",0)
	RETURN
ENDIF
IF ImpersonateLoggedOnUser(nToken) = 0
	MESSAGEBOX("Failed to authenticate. Unable to continue",16,"Zest",0)
	RETURN
ENDIF

* Access will be now be granted, you are accessing the dbf as a special user
gcDatapath = SYS(5)+SYS(2003)+"\data\"

USE (gcDatapath+"customer") IN 0
BROWSE

RevertToSelf()

RETURN


6. Build this into an EXE.

7. Now to test this, log off the Admin account and log in as any other user other than Admin and SecureAppUser.

8. Run the EXE and see that you can browse the data through the EXE. Now close the EXE and still logged in as this other user try opening customer.dbf using VFP or EXCEL etc. You will get this message:



Your data is now protected from prying eyes. Only the application (and of course you as Admin) can access it as normal. Everyone will have to use your application only.


Note1:
This is the simple guts of it. Of course you must protect the EXE from being decompiled and revealing the password, but there are a number of other tools that will do that.


Note2:
The data does not have to be in a directory below or a directory called DATA. It can be anywhere on the network and called anything. You will need to set the directory security and also the variable gcDataPath as shown to point to this location.

Hope this gives you an idea as to how to protect your data.

Updated 11/06/2007 Important
Note 3:

With impersonation, the user impersonated will have access to whatever directories you give this user. How does this affect you?

In a lot of apps you sometimes give a user the facility to say, export a file to Excel. Now since the user is using the APP, they may have access to directories through the app, that they would not normally have. It is important that you create a special user with very low or "normal user" priviledges and just give this user the additional access to the data directories.

It is best that the data is stored away (buired) because the File-SaveAs dialog also has the facility to right click, select "Copy" and then navigate away and "Paste" and this might compromise your data. Remember that your data is as easy to access as you allow it.

Updated 27/08/2007:
User Vivek Deodhar has come up with a way to protect the directory using the above method but on a network that has no Domain assigned but only a Workgroup.
#define LOGON32_LOGON_NEW_CREDENTIALS   9

use code something like this for logonuser()

LogonUser("deodhar","\\\\new","password",LOGON32_LOGON_NEW_CREDENTIALS, LOGON32_PROVIDER_DEFAULT, @nToken)


where "deodhar" is user on cmputer "new" who has access to the data folder, "password" is password given to user "deodhar" on computer "new".

You can use this code from any computer in the network and get access.

He has tested this with only Xp sp2. For other types, logon_provider options may have to be changed.

Updated 11/03/2007:
If you are using code like EXECSCRIPT() or try to access the current user's TEMP directory, if the "Special" user does not have proper rights, then your code wil fail. I have noticed this with EXECSCRIPT() and STRTOFILE() and COPY TO and with Reports that access data from a cursor etc.

Bottom line is that if the SpecialUser does not have proper rights, then your app will fail when you switch roles.

Therefore if using the method described in this FAQ, to protect your files, you must give "SpecialUser" Domain Admin rights, so that this special user has access to everyone's TEMP directories.

However with Domain Admin, this gives the logged on user extra rights when any Windows File dialog is displayed, allowing them access to areas they don't normally have.

Therefore it is essential that the user impersonation is REVERTED first before using any command like GETDIR(), PUTFILE() etc.

The sequence is :
LOCAL cDefault as string
cDefault = Addbs(Sys(5)+Sys(2003))
SET DEFAULT TO ..\
=approle("Revert")
* your command here
PUTFILE(......) or GETFILE(....)
=approle("Impersonate")
Set Default To &cDefault


This way the user only ever has access to the directories and files they are set up for.

FEEDBACK

harish poojary @ 6/10/2007 12:51:27 PM
Good solution,

But if I assign the user, that user also should not open the database from backend,, it should open thru only programmatically.

Harish

Ken Murphy @ 6/10/2007 1:22:35 PM
Excellent FAQ. Thanks Bernard.

Harish, You would not assign that user ID to anyone. The app impersonates that user. Your users sign in with their own credentials. When running the app they are impersonating this user - when not running the app, they are not.

Boudewijn Lutgerink @ 6/10/2007 3:18:31 PM
Pretty cool Bernard,

My rating for you.

Rey Magdalaga @ 8/2/2007 7:40:56 AM
Thanks for this!

Before I can only set a password on the BeforeTableOpen event of the DBC to prevent opening of the tables outside of my apps. But still since the folder is shared, the user can copy the dbfs anyway and open them in excel. Now, we can denied even copying of the tables.

Thanks again Mr. Bernard!

Craig Boyd @ 8/7/2007 2:28:39 PM
Very cool and well explained Bernard. A little encryption on the username and password to keep them from being decompiled (or read with a hex editor) from the app and it's a fairly secure solution.

head hair @ 8/21/2007 1:38:09 PM
Very nice.

inyang inyang @ 9/14/2007 8:16:49 PM
Aha! you got it. But this should have come earlier than now? You would have saved us from those "little" stress and threats!! Kudos Benard.

Kind Regards,
Inyang
lionheartsoftwarehouse@yahoo.com

Ronan Masangcay @ 11/8/2007 8:07:06 AM
Thank you Bernard and Deodhar

vivek deodhar @ 2/26/2008 6:24:02 AM
1. You can share a folder programatically by using the following:

? CreateFileShare("C:\SOMEAPP\Options\Cabs", "AppCabs",;
0, "Cabs folder in SOMEAPP on C")

PROCEDURE CreateFileShare
PARAMETERS cSharePath, cShareName,;
nMaximumAllowed, cDescription

LOCAL oService, oShare, nResult
oService = GetObject("winmgmts:\\" + "." + "\root\cimv2")
oShare = oService.Get("Win32_Share")

nResult = oShare.Create(m.cSharePath, m.cShareName, 0,;
m.nMaximumAllowed, m.cDescription)
RETURN m.nResult

**taken from http://www.news2news.com/vfp/?example=351&ver=wmi


2. You can even set the folder permissions programatically by using setacl.ocx.

It is available at http://setacl.sourceforge.net/

They have a command line option also avaliable.
By this way, I have been able to completely automate the process of applying security to my application.

The application is installed and then from "run" section, I share the target folder by using code in 1 above. Then I use setcal.ocx to set access permissions.

Lastly, I retrieve the pre-set password of impersonating user from my application and change the windows password of that user by netuserchangepassword() procedure.

The only remaining point is that I am unable to add a user (which will act as impersonating user) by using "netuseradd". It is failing with error 2245.

So I have to ask the customer to create a user by a specific name (predetermined by me and coded in my prg for further access control entries) and then run the setup.

What the user gets is completely secured application, with all permissions already set.

Vivek Deodhar

Sarojini devi @ 3/7/2008 9:57:55 AM
Really Good this solution. The major problem of VFP is that the data will not be secured. Great.!!!

Anup Singh @ 9/15/2008 12:40:09 PM
I had already work on that but i feel there is only third party tools to protect our database

mehran @ 8/17/2009 10:21:41 AM
hi
my client is free in network have no domain.
other pc is connecting by this solution but my pc is not able connect by this way.
may be my pc use this way for connect to the server?

Noel Crisostomo @ 9/18/2009 9:59:45 AM
Hello Bernard,
I tested your code and it was fine when the protected data is on the same machine I logged into. But when I put it in a mapped drive which is our default, I can access the data anymore. The win32 functions did not return any error, only the path to the data is not found.
-noel

Noel Crisostomo @ 9/18/2009 10:01:01 AM
sorry it should be "I can't access the data anymore"

Bernard @ 9/20/2009 5:11:31 AM
Please post all your queries on the Forum. There is no way to answer anything here.

Yash Shrivastava @ 3/17/2014 10:33:57 PM
Thank you for giving a such a wonderful idea for protecting my databases.
I am very thank full to you......

Rosty Vygovsky @ 2/3/2015 5:46:06 PM
Things I found a hard way for the above scheme to work:

1. The currently logged in user (in security context of whom the application was started originally)
MUST be listed in the "Local Security Policy\Local Policies\User Rights Assignment\Impersonate a client after authentication" group.
Otherwise LogonUser in the code above will produce a valid handle and ImpersonateLoggedOnUser will return 1 (success),
but attempting to access a file on remote server (use a table) will fail.

2. The remote server where database files are located must have UAC turned off, or the following key in registry set to 1:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\LocalAccountTokenFilterPolicy

If that key does not exist then create a DWORD called "LocalAccountTokenFilterPolicy" and set the value to 1

Muhammad Naeem Jhang @ 3/29/2015 6:31:38 PM
sir, i am thank you for this guid line.sir there is an issue when i run software from server the printer do not print, but when i run it from terminal the print working well on those printers which are installed only terminals. please help me to active printer that is installed on server .
nm_jee2003@yahoo.com

Bernard Bout @ 3/30/2015 4:47:47 AM
@Muhammad Naeem Jhang
There are a number of permission issues depending on other settings and profiles. The simplest solution is to revert impersonation, print, and then enable Impersonation again. This can only be tested on your network.



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: