A little design problem

[Roy Hann]

I'm working on a database design in which I've run into an amusing
problem. I've got a workaround that's "good enough" but it's not very
satisfying.

I can't talk about the real application so I'll use some suitable
analogues for my real entities.

Suppose you have a table of angels. Angels are required to be above
reproach in order to properly execute the duties of their office.

Unfortunately angels do sometimes blot their resume and have to serve
some kind of penance, after which they can resume their duties.

In my real application the privacy of the people I'm calling angels
cannot ever be compromised. If the data were ever to escape into the
wild it would be front-page news.

I keep information about angelic transgressions is in a separate sins
table.

If an angel has never transgressed they will have no row in the sins
table. If they have transgressed they will have at least one row.

Thus merely leaking the existence of a row would embarrass an angel,
even without disclosing any details. It must be impossible for an
improper person ever to join the tables.

I can easily prevent access to the sins table with suitable grants but I
need to protect the data at rest too. Databases get copied; disk
drives get swapped out.

I originally wanted to encrypt the foreign key in the sins table that
relates each sin to the the angel concerned, and of course I wanted to
declare the foreign key constraint. I hoped this would be to conceal
the existence of a sin because the without the passphrase it would be
impossible to join the tables.

Unfortunately it seems Ingres column encryption isn't compatible with a
foreign key declaration.

So, I'm open to suggestions. Hardware-level encryption seems like a
good alternative, but what else?

Alternatively, am I mis-using encryption? Is it possible to do what I
was trying to do?

--
Roy

UK Actian User Association Conference 2012 will be on Tuesday June 19 2012.
Register now at https://www.regonline.co.uk/ukiua2012
The latest information is available from www.uk-iua.org.uk.

[Ingres Forums]

Hi Roy,

Have you tried function encryption? I don't think you'd have the same
problem with foreign key constraints.

Jeremy


--
jruffer
------------------------------------------------------------------------
jruffer's Profile: http://community.actian.com/forum/member.php?userid=504
View this thread: http://community.actian.com/forum/showthread.php?t=14444

[Roy Hann]

Ingres Forums wrote:

> Have you tried function encryption? I don't think you'd have the same
> problem with foreign key constraints.

That's a good thought but it would require a table scan to join angels
and sins (which in this particular case is probably tolerable). The
show-stopper is that I can't enforce a referential integrity
constraint if I do that.

Just to make it clear, properly authorised users are allowed to see all
the data. I just need to ensure that if the database is physically
released (i.e. on a stolen laptop), that no one can tell which angels
are sinners.

One important detail I omitted to mention is that the identity of the
angels is a matter of public record.

For now, in spite of the fact that the public is permitted to know who
the angels are, I've decided to encrypt the many identifying
attributes. To my mind concealing the bit people are allowed to know is
going about the problem backwards.

It's not a big deal. It's just that the first time I've tried to use
encryption it doesn't seem to suit my problem.

[Paul Mason]

Well as all good Calvinists know we're all sinners really.

Couldn't you have a class of sin that's really not a sin but can only be
identified as such by the encrypted details in the sin table? Every
Angel would have at least one sin record but you couldn't tell which are
genuine sins without the pass-phrase.

> _______________________________________________
> Info-Ingres mailing list
> Info-...@kettleriverconsulting.com
>
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Chris]

On Monday, May 21, 2012 9:09:46 AM UTC-7, Roy Hann wrote:
> Ingres Forums wrote:
>
> > Have you tried function encryption? I don't think you'd have the same
> > problem with foreign key constraints.
>
> That's a good thought but it would require a table scan to join angels
> and sins (which in this particular case is probably tolerable). The
> show-stopper is that I can't enforce a referential integrity
> constraint if I do that.
>
> Just to make it clear, properly authorised users are allowed to see all
> the data. I just need to ensure that if the database is physically
> released (i.e. on a stolen laptop), that no one can tell which angels
> are sinners.
>
> One important detail I omitted to mention is that the identity of the
> angels is a matter of public record.
>
> For now, in spite of the fact that the public is permitted to know who
> the angels are, I've decided to encrypt the many identifying
> attributes. To my mind concealing the bit people are allowed to know is
> going about the problem backwards.
>
> It's not a big deal. It's just that the first time I've tried to use
> encryption it doesn't seem to suit my problem.

I think what you want is possible, you may have been hitting:

E_US24C3 Invalid encrypted index. An encrypted index must: (1) contain
only one column, (2) that column must be encrypted with NOSALT, (3) the
index must be of structure HASH.

Here is what I think you wanted (hacked up from another demo), NOTE I've commented out DDL that could hide the pk in the public angels tables (which you said you explicitly did not want to do) just in case.

How does the SQL at end if mail work for you?

Chris

/*
https://groups.google.com/forum/?fromgroups#!topic/comp.databases.ingres/VyLKfd1w9hU

** Ingres 10.0 encrypted columns
** http://community.ingres.com/wiki/Data_at_Rest_Encryption
*/


DROP TABLE angels;
\p\g
DROP TABLE sins;
\p\g
\nocontinue

CREATE TABLE angels
(
fname CHAR(10),
lname CHAR(20),
socsec CHAR(11) NOT NULL /* ENCRYPT NOSALT */ ,
primary key (socsec) WITH STRUCTURE = HASH
)
/* optionaly hide the public key
WITH ENCRYPTION=AES128,
PASSPHRASE='this is a secret';
*/
\p\g

/* provide pass key - allow access to table */
/*
MODIFY angels ENCRYPT
WITH PASSPHRASE='this is a secret';
\p\g
*/
CREATE TABLE sins
(
info CHAR(20), /* presumably the nature of the sin should be encrypted too */
socsec CHAR(11) ENCRYPT NOSALT,
FOREIGN KEY (socsec) REFERENCES angels(socsec) ON DELETE CASCADE WITH STRUCTURE = HASH
)
WITH
ENCRYPTION=AES128,
PASSPHRASE='this is a secret';
\p\g
help TABLE sins\p\g

/* provide pass key - allow access to table */
MODIFY sins ENCRYPT
WITH PASSPHRASE='this is a secret';
\p\g

insert into angels (fname, lname, socsec) values ('John', 'Smith', '012-33-4567');
\p\g
insert into angels values ('Fred', 'Smith', '012-33-4568');
\p\g
insert into sins values ('owns a dog', '012-33-4567');
\p\g

select * from angels;
\p\g

select * from sins;
\p\g

select angels.fname, angels.lname, sins.info from angels, sins where angels.socsec = sins.socsec;
\p\g

/* revoke pass key (use invalid key) - forbid to table */
MODIFY angels ENCRYPT
WITH PASSPHRASE='';
\p\g

MODIFY sins ENCRYPT
WITH PASSPHRASE='';
\p\g

select * from angels;
\p\g

[James K. Lowden]

On Mon, 21 May 2012 13:32:13 +0000 (UTC)
Roy Hann <spec...@processed.almost.meat> wrote:

> I can easily prevent access to the sins table with suitable grants
> but I need to protect the data at rest too. Databases get copied;
> disk drives get swapped out.

...

> Unfortunately it seems Ingres column encryption isn't compatible with
> a foreign key declaration.

Protecting the fact of the existence of these data is a particular form
of protecting the data. That's more than any mere RDBMS can do.

Does the OS you're using support device-level encryption? Something
akin to cgd (http://www.imrryr.org/~elric/cgd/)? If so, and the key
isn't backed up with the data, and the password to the key isn't backed
up at all, then the data are pretty safe. (Maybe too safe, but that's
another question!)

It's not just a technical problem, as I'm sure you're aware. Disks
aren't the only thing that get swapped out. So do people.

--jkl

[Roy Hann]

Chris wrote:

> I think what you want is possible, you may have been hitting:
>
> E_US24C3 Invalid encrypted index. An encrypted index must: (1) contain
> only one column, (2) that column must be encrypted with NOSALT, (3) the
> index must be of structure HASH.
>
> Here is what I think you wanted (hacked up from another demo), NOTE I've
> commented out DDL that could hide the pk in the public angels tables
> (which you said you explicitly did not want to do) just in case.
>
> How does the SQL at end if mail work for you?

THank you Chris.

I just tried it quickly and got an error on creating the sins table:

E_US24C2 The key for a modify or index operation is of a datatype which is
either non-keyable or non-sortable. All key fields must be both keyable
and sortable and (for primary keys) cannot be encrypted. An encrypted
index may contain only the one encrypted column.
(Mon May 21 20:28:04 2012)

I am rushing out of the office for a flight but I'll try to take another
look at this tomorrow evening.

[nikosv]

as

As a last resort, can you forgo referential integrity and shift all checking into the application level? (parent-child updates,constraints checking)
As physical security is concerned,you could create an encrypted partition/container on the hard disk and store the database there

[Chris]

On Tuesday, May 22, 2012 9:52:13 PM UTC-7, nikosv wrote:
> As a last resort, can you forgo referential integrity and shift all checking into the application level? (parent-child updates,constraints checking)
> As physical security is concerned,you could create an encrypted partition/container on the hard disk and store the database there

There is no need to forgo pk/fk integrity. I've updated my previous script to show an attempt to break this and the script works as expected:

E_US1906 Cannot INSERT into table '"sins"' because the values do not match
those in table '"angels"' (violation of REFERENTIAL constraint '"$sins
_r0000010200000000"').


Roy, RE E_US24C2 I'd get an issue logged. I've tried with a few different Ingres 10 patch levels and I don't see this problem.

commit;
\p\g

/* this will break pk/fk, consider tesing without nocontinue */
insert into sins values ('ate a dog', 'does_not_exist');

\p\g

select * from angels;
\p\g

select * from sins;
\p\g

select angels.fname, angels.lname, sins.info from angels, sins where angels.socsec = sins.socsec;
\p\g

/* revoke pass key (use invalid key) - forbid to table */
MODIFY angels ENCRYPT
WITH PASSPHRASE='';
\p\g

MODIFY sins ENCRYPT
WITH PASSPHRASE='';
\p\g

select * from angels;
\p\g

select info from sins;

[Roy Hann]

nikosv wrote:

> As a last resort, can you forgo referential integrity and shift all
> checking into the application level?

I have spent most of my career arguing that database servers add almost
zero value if they aren't made responsible for enforcing consistency
(and auditing), so I'm not instantly persuaded by that approach.

Besides, testing the limits is what leads to improvements both in the
product and in my knowledge. :-)

> As physical security is concerned,you
> could create an encrypted partition/container on the hard disk and
> store the database there

Now *that* is an idea I fully endorse. I don't see why we tolerate
unencrypted disks at all in the 21st century. No one should be allowed
to store personal information any other way.

[Roy Hann]

Chris wrote:

> There is no need to forgo pk/fk integrity. I've updated my previous script
> to show an attempt to break this and the script works as expected:
>
> E_US1906 Cannot INSERT into table '"sins"' because the values do not match
> those in table '"angels"' (violation of REFERENTIAL constraint '"$sins
> _r0000010200000000"').
>
>
> Roy, RE E_US24C2 I'd get an issue logged. I've tried with a few different Ingres
> 10 patch levels and I don't see this problem.

I may have tested it on a homebrewed build. I'll try again on something
definitely official before I log an issue.

[nikosv]

> I have spent most of my career arguing that database servers add almost
> zero value if they aren't made responsible for enforcing consistency
> (and auditing), so I'm not instantly persuaded by that approach.
>

true, that is why I noted "as a last resort" :)
However there are tools like visual studio that allow for in-memory
client based databases that can have referential integrity,constraints
and validation all in the application level

>
> > As physical security is concerned,you
> > could create an encrypted partition/container on the hard disk and
> > store the database there
>
> Now *that* is an idea I fully endorse.  I don't see why we tolerate
> unencrypted disks at all in the 21st century.  No one should be allowed
> to store personal information any other way.
>

try <a href="http://www.truecrypt.org/">truecrypt</a>

[Roy Hann]

nikosv wrote:

>
>> I have spent most of my career arguing that database servers add almost
>> zero value if they aren't made responsible for enforcing consistency
>> (and auditing), so I'm not instantly persuaded by that approach.
>>
> true, that is why I noted "as a last resort" :)
> However there are tools like visual studio that allow for in-memory
> client based databases that can have referential integrity,constraints
> and validation all in the application level

I have no objection at all to the applications testing integrity etc.
That will usually result in a better user experience.

But in my ideal universe I wouldn't code the constraints. The
application would get the integrity constraints from the database
exactly the same way it gets any other meta-data.

Of course if I wanted the application to enforce additional
constraints that have nothing to do with the database as such, I'd have
to code those myself.