Tuesday, March 15, 2011

SQLite, AUTO_VACUUM and Windows ACLs

This article applies to Windows 7 32/64 and Windows Vista 32/64 with UAC active.

In January an enduser pointed out to me that every time he tried to use the Real Time BlackList tool in NetScanTools Pro, he got an SQLite error message about the database being 'read-only' - it could not be opened. The software was installed on Windows 7-64 bit and NetScanTools Pro is operating at 'asInvoker' privilege which is normally USER privileges.

After doing the usual tech support routines by checking file properties, I was stumped - until yesterday when I was able to duplicate it on two Windows 7 machines.

The SQLite database is copied into our own directory created at install time under c:\ProgramData which is the common user data area. The thought was that any account using the program would be able to access the database. That was the idea. It's not the only database we put in there and the others were opening fine so I set out to find out why.

The only thing different about this database is that it has the pragma "AUTO_VACUUM" set. It appears that with AUTO_VACUUM SQLite moves freed pages around within the tables. This requires write privileges. SQLite error messages should do more than simply state that the database is read-only by checking the file ACLs given the calling process account privileges then stating the incompatibility with the current AUTO_VACUUM state.

To see the file access privs on an account level, you have to go into our C:\ProgramData\NWPS\NetScanToolsPro common user directory and do an "icacls *.*" on the command line. You will see that indeed user level privileges (BUILTIN\Users) only have (I)(RX) - inherit, read, execute privileges while the other higher level accounts have (F) full privileges. Since AUTO_VACUUM requires write access to the database to make changes, it will not have the proper privs for a user level account. So, yes, opening the database fails (I just don't think the message is good enough).

So now, how to fix it. Recreating the database with AUTO_VACUUM off fixes it. But what if you need to write (as a USER) to the Real Time Blacklist database using the tool we provide to edit the database? You can't because the administrators group are the owner.

The solution is to change the directory and file ACLs. I did this by modifying the installer to call a function of my own design which applied FULL access privs (grfAccessPermissions=GENERIC_ALL) to grfInheritance=SUB_CONTAINERS_AND_OBJECTS_INHERIT at our NWPS\NetScanToolsPro directory level. If that is done and you do the icacls command, all files in that directory show the "Everyone:(I)(F)" which means that every account can fully access the files and that includes our SQLite database that we couldn't open. You have to use AllocateAndInitializeSid, SetEntriesInAcl, and SetNamedSecurityInfo so accomplish this. You have to do this in the installer because it is running at admin privileges.

To summarize, if you have a program running Windows 7 or Vista at USER level that needs to access an SQLite database with write privileges contained in the C:\ProgramData common user directory that was not created by your program - you've got a problem. And that problem is even worse if it has AUTO_VACUUM enabled. You have to modify the file access privileges to FULL control in order to allow SQLite to operate on the database correctly.

No comments: