This may have been discussed before, but to me it's quite serious and I
can't use SQL because of it.

I need to have some of my tables encrypted and I thought DBISAM did a
pretty good job. That dream was shattered when I discovered the canned
result set from SQL queries on an encrypted table are sitting in the
private directory without any encryption. I was able to view the
contents of these temporary files with a hex viewer. The data that I
thought was encrypted, is there in plain site. The specifications for my
project requires the data remain encrypted and not be accessible without
the proper password. My boss may have a heart attack if he knew we were
shipping data to clients that is in the clear.

Unfortunately live queries face the same problem. If I delete the .idx
file, and run the application, DBISAM will build an empty index file and
allow the program to run. This is great, but now any SQL with an Order
By clause will be a canned query and you guessed it, the unencrypted
copy is now in the temp directory.

My question is, why can't these canned queries be encrypted with the
same password if the original tables were encrypted? Why strip away
encryption on all canned queries? Even canned result sets from table
joins should remain encrypted if one or more of the tables were
encrypted. Use the same password as one of the encrypted tables, it
doesn't matter which one, as long as the data remains secure.

Can I assume your TnxTables's don't have this problem?

Dave

"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:21A481CB-0F4B-4CD3-B433-9DE3D5292ED5@news.elevatesoft.com...
> This may have been discussed before, but to me it's quite serious and I
> can't use SQL because of it.
>
> I need to have some of my tables encrypted and I thought DBISAM did a
> pretty good job. That dream was shattered when I discovered the canned
> result set from SQL queries on an encrypted table are sitting in the
> private directory without any encryption. I was able to view the contents
> of these temporary files with a hex viewer. The data that I thought was
> encrypted, is there in plain site. The specifications for my project
> requires the data remain encrypted and not be accessible without the
> proper password. My boss may have a heart attack if he knew we were
> shipping data to clients that is in the clear.
>

First of all, is it really that big of a problem if your boss has a heart
attack? You might get his job. Well, just some free career advice, anyway.

Seriously now, interesting problem. Obviously one performs a query to
eventually show unencrypted data to the user, in the form of a report or a
dbgrid. When you say you are "shipping data to clients", are you talking
about actually sending a dataset to another computer, or presenting the
results of the query?

If you go thru the process (more or less) when printing a report

1. Parse and prepare query
2. Read tables (whether or not encrypted), store results in memory or in
temporary files
3. Prepare the final output dataset (which again could be a temporary disk
file)
4. Format each row of the resulting dataset, and send to the printer

At which point should the data be unencrypted?

Remember thet you can "select into" a table, and that table can be
encrypted. I don't really see a solution to the temporary datasets, other
than making the temp folder available only to this user.

Robert

Robert

The obvious alternative is to use memory tables. Cleaned up when the app closes and unlikely to be looked at by someone with a hex editor. Still obtainable if you work at it but so is everything.


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:AAB1741C-C020-4417-B108-AD229ADEC35C@news.elevatesoft.com...
> Robert
>
> The obvious alternative is to use memory tables. Cleaned up when the app
> closes and unlikely to be looked at by someone with a hex editor. Still
> obtainable if you work at it but so is everything.
>

The problem is that more and more customers are requiring that you
specifically tell them of any potential areas of exposure. Example: say I
run a large query, getting all kinds of encrypted data, which I know creates
a temporary dataset. Then I kill the application, the dataset remains,
unencrypted, in the temp folder. Then I FTP the dataset to the competition,
who downloads DBSYS for free and has a field day with my customer's data.
One of those that comes up during acceptance testing is enough to kill a
sale.

The fact that DBISAM is "non standard" (read, non Microsoft or Oracle) makes
it worse. I don't know what the answer is, but I see the potential problem.

Robert

Dave,

<< I need to have some of my tables encrypted and I thought DBISAM did a
pretty good job. That dream was shattered when I discovered the canned
result set from SQL queries on an encrypted table are sitting in the private
directory without any encryption. >>

Use the ENCRYPTED WITH clause:

http://www.elevatesoft.com/dbisam4d7_select_statement.htm

<< My question is, why can't these canned queries be encrypted with the same
password if the original tables were encrypted? Why strip away encryption on
all canned queries? >>

a) Not everyone wants the result sets to be encrypted by default.  Some
developers only want the long-standing tables on disk encrypted.

b) There is the possibility that a query could contain a join between an
encrypted table and a non-encrypted table.  Which do we use to determine
whether to use encryption or not ?

c) The database server doesn't have this issue since the canned result sets
are usually never accessible by clients directly.

<< Even canned result sets from table joins should remain encrypted if one
or more of the tables were encrypted. Use the same password as one of the
encrypted tables, it doesn't matter which one, as long as the data remains
secure. >>

We can't just arbitrarily pick an encryption password from one of the
tables.  In the case of the INTO clause, the developer needs to know that
the result set is encrypted with a specific password that can be used to
open it afterwards without having to try and guess which one was used.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert wrote:
> "Dave Harrison" <daveh_18824@spammore.com> wrote in message
> news:21A481CB-0F4B-4CD3-B433-9DE3D5292ED5@news.elevatesoft.com...
>
>>This may have been discussed before, but to me it's quite serious and I
>>can't use SQL because of it.
>>
>>I need to have some of my tables encrypted and I thought DBISAM did a
>>pretty good job. That dream was shattered when I discovered the canned
>>result set from SQL queries on an encrypted table are sitting in the
>>private directory without any encryption. I was able to view the contents
>>of these temporary files with a hex viewer. The data that I thought was
>>encrypted, is there in plain site. The specifications for my project
>>requires the data remain encrypted and not be accessible without the
>>proper password. My boss may have a heart attack if he knew we were
>>shipping data to clients that is in the clear.
>>
>
>
> First of all, is it really that big of a problem if your boss has a heart
> attack? You might get his job.

Yeah, I hadn't thought of that. I'll spring it on him during the monthly
meeting with the client.  

>
> Seriously now, interesting problem. Obviously one performs a query to
> eventually show unencrypted data to the user, in the form of a report or a
> dbgrid. When you say you are "shipping data to clients", are you talking
> about actually sending a dataset to another computer, or presenting the
> results of the query?

We don't show all the data from the table to the user. There are columns
that are retrieved that contain proprietary data and scripts. There is
also registration info, internal passwords and checksums that we don't
want the user to see. There is also encrypted data that is processed
(number crunched) before the user see the summarized data. I also see a
problem if companies charge for their data and the user gets the data
for free if he figures out where the temp data is stored.

>
> If you go thru the process (more or less) when printing a report
>
> 1. Parse and prepare query
> 2. Read tables (whether or not encrypted), store results in memory or in
> temporary files
> 3. Prepare the final output dataset (which again could be a temporary disk
> file)
> 4. Format each row of the resulting dataset, and send to the printer
>
> At which point should the data be unencrypted?

The data should remain encrypted on disk at all times. That's what the
developer expects and that's what the client expects. It's like putting
your money in a bank, you expect the doors to be locked at night and
your money safeguarded. If you see a sign on the bank door saying "Due
to malfunctioning alarm system, doors remain unlocked and alarms turned
off Sunday night." Shouldn't this be a cause for concern?

> Remember thet you can "select into" a table, and that table can be
> encrypted.

Unless of course when creating that table, more temporary tables are
created because of sorting etc., and that becomes unencrypted.

Dave

Roy Lambert wrote:

> Robert
>
> The obvious alternative is to use memory tables. Cleaned up when the app closes and unlikely to be looked at by someone with a hex editor. Still obtainable if you work at it but so is everything.
>
>
> Roy Lambert
>

Roy,
    Memory tables are nice, but not practical for large tables. I'd
have to dramatically increase the minimum requirements for my
application. I'm doing a lot of full text searching on large tables.
There is also the delay incurred when loading thousands of rows of data
into memory.

Dave

Robert wrote:

> "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
> news:AAB1741C-C020-4417-B108-AD229ADEC35C@news.elevatesoft.com...
>
>>Robert
>>
>>The obvious alternative is to use memory tables. Cleaned up when the app
>>closes and unlikely to be looked at by someone with a hex editor. Still
>>obtainable if you work at it but so is everything.
>>
>
>
> The problem is that more and more customers are requiring that you
> specifically tell them of any potential areas of exposure. Example: say I
> run a large query, getting all kinds of encrypted data, which I know creates
> a temporary dataset. Then I kill the application, the dataset remains,
> unencrypted, in the temp folder. Then I FTP the dataset to the competition,
> who downloads DBSYS for free and has a field day with my customer's data.
> One of those that comes up during acceptance testing is enough to kill a
> sale.
>
> The fact that DBISAM is "non standard" (read, non Microsoft or Oracle) makes
> it worse. I don't know what the answer is, but I see the potential problem.
>
> Robert
>
>
Robert,
      Yes, there is also the legal aspect to consider. I hadn't thought
of that. Maybe it's time to increase my liability insurance?

Dave

Tim Young [Elevate Software] wrote:

> Dave,
>
> << I need to have some of my tables encrypted and I thought DBISAM did a
> pretty good job. That dream was shattered when I discovered the canned
> result set from SQL queries on an encrypted table are sitting in the private
> directory without any encryption. >>
>
> Use the ENCRYPTED WITH clause:
>
> http://www.elevatesoft.com/dbisam4d7_select_statement.htm

That's great. I'll implement it on all my queries that involve sorting
or table joins.

I meant to say "Can I assume your TDBISAMTables's don't have this
problem?". Your TDBISAMTables don't created temporary tables do they? If
so, I'll likely use tables instead of queries whenever possible.

>
> << My question is, why can't these canned queries be encrypted with the same
> password if the original tables were encrypted? Why strip away encryption on
> all canned queries? >>
>
> a) Not everyone wants the result sets to be encrypted by default.  Some
> developers only want the long-standing tables on disk encrypted.

My interpretation is, if the data is encrypted on disk, then it should
stay encrypted in the temporary files. If you ask a programmer "Is it ok
to remove the encryption on the data for intermediate tables?" I'll bet
you the answer is a resounding "NO!".

>
> b) There is the possibility that a query could contain a join between an
> encrypted table and a non-encrypted table.  Which do we use to determine
> whether to use encryption or not ?

If any of the tables are encrypted, then the temporary tables should
remain encrypted.

>
> c) The database server doesn't have this issue since the canned result sets
> are usually never accessible by clients directly.

You're assuming of course that the client is using a dedicated database
server that is in a secure location. Often that is not the case.
Client's are "cheap" and often put the DBISAM server on machines that
are used for other purposes, which may allow users access to the
temporary unencrypted files.

>
> << Even canned result sets from table joins should remain encrypted if one
> or more of the tables were encrypted. Use the same password as one of the
> encrypted tables, it doesn't matter which one, as long as the data remains
> secure. >>
>
> We can't just arbitrarily pick an encryption password from one of the
> tables.

For temporary tables, I think you have to.

> In the case of the INTO clause, the developer needs to know that
> the result set is encrypted with a specific password that can be used to
> open it afterwards without having to try and guess which one was used.
>

When using "Into", if the new table is permanent then you're right, the
user needs to explicitly specify the password like he would for any
other permanent table. But for temporary tables that were created from
encrypted data and is beyond the developer's control, these tables
should still be encrypted. Otherwise this is a hole in the database's
security.

It's going to be embarrasing if DBISAM developers now have to disclose
to their clients (I think they are legally obligated to do so), that the
data is no longer encrypted if a canned result set is produced. That
means the client's data can be sitting on the disk unencrypted when a
table is sorted on a non-indexed column or when tables are joined.

There is mounting legal pressure and legislation enacted to force
companies to tighten security to stop hackers (& employees) from gaining
access to their critical data. Release of "secure" client information
could be embarrassing to the company and can produce a litigation
nightmare that could see fines in the tens of millions of dollars. I
don't see any alternative but to fix the problem. Sure we can both argue
the technical merits of whether the temporary data should remain
encrypted or not, but in the end, it's the legal ramifications that will
determine whether the problem is fixed or not.

Dave

"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:40819DCC-ABCF-4BD7-8E10-F5CA36AE3A72@news.elevatesoft.com...
<< Even canned result sets from table joins should remain encrypted if one
>> or more of the tables were encrypted. Use the same password as one of the
>> encrypted tables, it doesn't matter which one, as long as the data
>> remains secure. >>
>>
>> We can't just arbitrarily pick an encryption password from one of the
>> tables.
>
> For temporary tables, I think you have to.

Why? There's no reason for anybody other than the engine at that time to
know what the password is. Create a password on the fly, it will only be
used to encrypt/decrypt that temporary dataset. If the application crashes,
the temp dataset is unusable, but who cares? In fact, this issue is giving
me the creeps, I'd rather see all temp files encrypted as a default. Do a
google "sox compliance" and you'll see the importance of this issue. I wish
I had not read this thread Temporary files remaining on the workstation
were an annoyance, now I see they can be a liability.

I wonder if one can force the temporary folder to be some sort of secure
folder? Can you encrypt at the folder level? Maybe that's the solution.

>
> It's going to be embarrasing if DBISAM developers now have to disclose to
> their clients (I think they are legally obligated to do so), that the data
> is no longer encrypted if a canned result set is produced. That means the
> client's data can be sitting on the disk unencrypted when a table is
> sorted on a non-indexed column or when tables are joined.
>
> There is mounting legal pressure and legislation enacted to force
> companies to tighten security to stop hackers (& employees) from gaining
> access to their critical data. Release of "secure" client information
> could be embarrassing to the company and can produce a litigation
> nightmare that could see fines in the tens of millions of dollars. I don't
> see any alternative but to fix the problem. Sure we can both argue the
> technical merits of whether the temporary data should remain encrypted or
> not, but in the end, it's the legal ramifications that will determine
> whether the problem is fixed or not.
>

I'm certainly not a lawyer, but I do work with large companies and tend to
agree with you, though I had never previously considered this issue.

Robert

> Dave