[Music]
thank you
[Music]
all right
foreign
University in Chicago
and
I'm gonna warn you right now I'm gonna
compare to all of the cool hacks and
applications that you see here
25 years I don't know a while and it's
just not that exciting like database is
a big on the back end of the actual
application you're using
um so people in graphics and HDI inside
to get through such cool preservations
okay but we'll try to make it as fun as
possible
um so the specific thing that my
research has kind of led me to
we started out with doing a lot of
forensic work and doing like storage or
Construction in fact I was inside for
cloud 2.0 talking about our forensic
tool for databases uh but as we try to
figure out essentially what do people
have to do they're trying to find good
applications for this work turns out
that
most people care about compliance mostly
because they're made to care
so in essentially you have to check the
boxes you have to be able to show
that you're following various rules and
I think the everyone's favorites from
the gdpr
and it's equivalent ccba in California
but in general there's a lot of policies
and a lot of
um
requirements about how the data should
be managed stored controlled processed
and so on that might be coming from
internal organization requirements or
from all the fund laws and sometimes the
preparation after above you might also
have some custom requirements say like
if there's a pending lawsuit you may
have to preserve the data
and so
the part that interests me is kind of
crossing the boundary within the policy
and the actual implementation so that
within the database where the data is
actually kept as alarm in a second the
policy is easy to implement because you
kind of have this very interesting okay
um Separation on one side you have
people sending policy saying those are
the rules and one of my colleagues
um is is an attorney and so she can
always present that type but on the
other side you have the actual technical
people managing the data mostly people
like David's administrators or otherwise
like anyone technical who
would be happy to follow the rules but I
don't necessarily have the right tools
to translate
the policy did that as well yes oh I
should note please if you have any
comments or questions you can just
say it in any time and yes also yes
is always a concern so you want
something practical
um and that doesn't require a lot of
custom Solutions
and I'm going to talk
technically there are three pillars as
per the title but we've made the most
progress on their attention than purging
um and then consent is a much shorter
and stuff air pillar at the moment by
this work in progress uh so
um just as a brief definition a
retention refers to we have a policy
that says data must be kept for three
years five years depending on the type
of data like employment data or
interview data might have to be kept for
X number of years according to either
lower policy or some State requirement
who knows
um on the other side you have purging
that says that the data is to be
destroyed and not retained Beyond a
certain direction
amen there are a lot of those rules I
think one of my favorite it's like
there's a lot of different laws and they
obviously might conflict or overlap I
think my favorite is if you look at um
the camera data that people use for um
like the automatic video recording that
the police cars have those are
particularly fun because the retention
rules the region you're within like two
minutes I think in Massachusetts or New
Hampshire to like a couple of years and
some other states so like you could
actually for the same thing it might be
state by state that dictates a different
duration of how long data may be
detained and after which one if it has
to be destroyed
um and then consent is something that no
one quite understands what it is but the
idea is that gdpr has established a rule
requiring
um owners of the data to have a say and
how that data is used
so specifically you have multiple
purposes and
you might
they're obviously like if your data is
collected as part of the purchase then
it can be used for order processing but
unless you explicitly request to use it
for marketing uh it can they use cannot
be extended nor are you even allowed to
ask for blanket consent according to
gdpr rules I mean I imagine eventually
others are going to catch up I think
CCPA in California already has
um so
there is extra complexity to that and
I'll talk a little bit about it at the
end but
this part is the one progress so let's
talk about
and before I get to that I want to kind
of motivate
that ultimately as I'm flashing as
databases are I would argue that they're
always at the back end like anything any
store anything you interact with any
website essentially uh would have a
database on the back end which you don't
necessarily see of course because you're
just dealing with a web page but
it's there in the storing day so I'm
approaching this part because this is my
research area but in part because
ultimately almost regardless of how the
data is processed from the user
perspective on the back end there is
Davis that actually does that
and
I just never miss an opportunity to find
some pictures just like in my classes
but there's also a lot of like database
means a lot of things and
essentially
we work with a different number of them
there's a bunch of relational databases
that see it supports SQL there's much of
those SQL databases
it's a short summary of The many systems
you might be dealing with um
but in general there's extra couple X in
there and the special databases it was
by Design
that they take over how the data is
managed and we've got to dig into that a
lot whether we're doing forensic work
because when you look at the stores
turns out that all sorts of interesting
things might be happening in the back
end and
It Is by design that the user just
issues a SQL query and that's what I
want to get and then the database pen
optimizes and organizes data and creates
copies indexes is sort of as it sees fit
um and there is not always even any good
way to control what exactly happens
I'll talk about the motivation of that
in a second
so there is not a lot of support to to
actually Implement data governance
um and to be clear I differentiate
databases from like file system or
objects because if you're dealing with
like files like documents then a lot of
those policies become relatively simple
arguably because let's say I have an
object like a Word document with some
information and you can just say well I
have to keep it for a year and then it
needs to go away and AWS object storage
and a few other systems will allow you
to do that because it's pretty easy if
you just put an expiration date on the
document to create
the fun starts when the underlying
storage is more complex
so specifically on I'll mention some
examples of that you know what happens
if you have several tables and the
policy covers multiple tables so our
customer order actually includes both
the customer table and the order
information and some address information
and on top of that
some of the columns in the table are
covered by the policy but that was
aren't
this is where like doing it manually
becomes completely practical and that's
one example
my attorney colleague UCLA saying that
well you can just call all of the
columns to be part of the policies it's
a key then it's something that the
database has created
it's not user data so when your data is
recorded the customer ID is assigned by
the company so it isn't customer
statement therefore it can be included
that the policy it has to follow
different rules because it hasn't been
collected from them
amen
the the other
fun detail is just this idea that
multiple mechanisms could come into
conflict that includes retention and
parking obviously because they have a
different goal retention test key
project says destroy it but also
you know backups
are are the fine example because
all good I.T policies require having
backups right so that they're able to
restore data but that directly conflicts
with actually being able to purge the
data
um specifically
um if you have a good bag of policy then
you have backups that are replicated
that are on site that are maybe on tapes
and once that happens like if you have
your backups stored in the stack I'll
take somewhere and then there's actually
no way to destroy the data when it's
time arrives because
it's on tape you don't know where it is
and generally speaking like backups are
not created to be edited because like
the idea is to make a backup and then
you apply at a store but it's not really
like a active system and they're not
designed to be altered
[Music]
um and so this is kind of the things we
try to solve
to solve the way support within ideally
all database systems we mostly data
analysis on SQL based regulational kind
and we've expanded a little bit into
nosql systems in such a way that it's
transparent to the user
and although I'm coming from Academia
the one thing I've learned is that if
you come in you say all right here's how
it's going to work you're going to
change your system uh people are going
to laugh at you
um so we really kind of tried to focus
on like using existing Machinery to
support all of those
policies in such a way that the user
doesn't have to know I mean obviously
something's happening in the background
then you might have to add something to
your database to make it happen but the
idea as the user can see it in fact
there's a little bit of Prior work where
they've added the mechanism that
basically said well and you're about to
violate the policy throw an error and
that
will work in terms of protecting the
policies but like what happens with all
of your existing systems and scripts and
users who will be complaining about an
unknown error because they have no idea
what this doubled into
um so I want a big motivations is to
keep it completely transparent and
in the shadows and in the background of
the Davis
and of course using existing things
because again we're trying to make it
generic in the sense that it's
applicable to whatever database or
database systems you might have which
means you can't assume some special
tools or Special Steps you want to use
existing mechanisms that can be
leveraged
whatever you happen to use
[Music]
so to revisit the point of a business
record so as I understand that and again
I work as an attorney but
far from being an attorney so
sometimes I feel like we speak a
different language and like even
terminology has to be agreed upon but
in general the policies are defined in
units of business records because
obviously when someone writes a policy
they really don't know what database you
have or what tables you design
what they actually have is an entity a
business record that corresponds to an
event that has actually been recorded
like a customer purchase or some sort of
transaction whatever it is it's known as
a business record and while in
the simple world of files and documents
it might just be a file like an email in
an actual database relational or even no
SQL it will be some combinational things
like the data can spread especially in
relational SQL based databases there is
a process called normalization that
designs tables in a certain way to
minimize redundancy and as a part of it
it splits it up into Separate Tables
essentially
Outsourcing all of the pieces that might
repeat
um so it's pretty natural for this
single business record entity to be
split into multiple tables and as I
mentioned before what makes it
particularly fun is that it's also
possible that different columns fall and
they're different
rules
and so obviously you have to be finite
enough to actually match that
now the one part we try to make it
transparent and we've tried to automate
it as much as possible the idea is that
you should be able to set up your
policies in the database
um and then you should be able to just
let it not and hopefully even be able to
verify that you are compliant with
whatever rules are in place
so the what was the one thing that we
can't really automate at least we
haven't gotten to yet is the mapping of
the policy into the database because
that's
you know that's where you translate
essentially things written in English
into something in database service but
the way we built the idea is that you
have the
domain experts who actually understand
what the data looks like and what things
mean and what words translate to within
the database storage
and possible illegal console depending
on who you're working with to to sit
down and map things to say Well when
we're talking about customer name it's
this column in the table when we talk
about the purchase it maps to that table
into those key columns that part
maybe one day we'll figure out how to
automate it it's on the to-do list but
that's the one manual part where someone
has to kind of make this translation
and map one is a business record uh
described in the policy into database
terms
but the idea is like once you get to
that point it should all be um
automated in actual enforcement and
maintaining the rules
and
once you happen
um as an old sergeant the second I said
the idea is we want to Define it in such
terms so that the database administrator
or anyone Imaging your data understands
enough
right like we wanted to move it
into
um into people language essentially
um so that it's it's in terms that
um someone who manages the database can
understand
and I definitely one of my PhD students
who recently started used to work as a
you know in compliance
um and and she was saying that's one of
the common things you know someone comes
to the database administrator and says
here's the policy and make it happen at
the first sense it's like well life is
supposed to do right well what does it
mean it's equal you tell me that it
might be able to make it work
Okay so
to give you a high level idea first so
I'll I'll start talking from I'll start
a discussion for retention and then I'll
talk about purging depending on how much
time we have we can talk about consent
as well
um all of those things kind of Interlake
so the idea is that
retention means data must be kept
for a certain amount of time
um therefore we want to make sure that
no protected data is destroyed by
accident and again like there is no
chance that the user would know anything
about that or would do anything to help
you
and as I said with documents you might
be able to get somewhere but with with
the database there's just no way to
track things especially because ball is
this thing overlap like it's possible
that the column
and the database table falls under
multiple policies with different
durations in which case you have to pick
the minimum or the maximum depending on
whether it's retention or purging so
even if model is willing to manually
after Paula says you still like you
would have to track multiple effects you
think conflict and things like that
um so the idea is that you set up your
policy
and I'll talk about it
um
[Music]
yes I'll talk about it in a second
um you set up your policy in some SQL
terms of databases around the stands and
then
whenever the data is deleted or updated
depending on the policy um some policies
also require you to keep track of any
changes others might just say well this
data can be destroyed but if it's
updated it's okay it depends on the
system on the policy uh but the idea is
that anytime essentially something that
should not be deleted is deleted by the
user you quietly move it into an archive
table
and you let the original query proceed
as a point so I usually delete
the database checks whether something
needs to be debated if not nothing
happens if it does need to be obtained
you make a copy of only other element
pieces into an archive table
and then everything proceeds
as necessary and most importantly the
user does not know any better because
either way their delete succeeds it's
just that if they needed something they
should be around it's
it will survive in the archive
and you know then the archive tables and
as a kind of small and unexpected bonus
it turns out that you can expect
performance would be a little bit better
because if the data that's been archived
it's not going to be actively used
and it's actually better to keep it away
because then your active fables are
smaller as a result
um so it's essentially a win-win and
then should you receive a request for
data that they no longer happen if you
go into archive and type and yeah this
so for example and
if you're not familiar with SQL
SQL is very convenient and easy to read
because
well it was designed as such it's
supposed to stand for Structured English
word language
um and so
for example you you essentially do
something like this like this is an
example of attaining
keeping indefinitely all the information
about interviews for applicants who are
hired
most importantly though
um the idea is that like someone has to
explain to the database administrator
what you're trying to do and let's say
the thing that you're trying to do is to
keep certain columns like in this case
the name date of birth the position the
application date and the real date an
outcome for anyone who has been hired
and you do it in depth to explain that
and then the database administrator can
write a SQL query
that describes what you're trying to
capture
and so like this seems Seems like the
most efficient way because essentially
instead of creating some sort of
different mechanism that can try to
enforce the same type of rules
uh you could simply use the database
query itself
to instead of fetching data for the user
to identify what rows and columns are a
good thing and you can kind of check
yeah you can do a sanity check you can
just run the query and see if it Returns
what you expect would it take
and of course if it's not indefinite
then you can either time limit or things
like that but again all of those would
be simple SQL operations
rather than anything you the only thing
really new is created the game that's
the part we made up but the rest of it
is just the regular part that's valid by
itself and so this way it kind of as
intuitive as possible because it is
defined in the same terms and the same
units as a database would know anyway
uh so that's an example of the idea of
that
um and so as far as how it would operate
in practice
here's something to to give you an idea
so let's say a user comes in
and deletes rows four and six
now there is a retention policy that
says that the name
the name needs to take
note that in my contrib example the
social security number does not have to
Detective uh
for whatever reason right like there's a
policy that says names must be kept
indefinitely a lot of time but the
social security number isn't so the lead
proceeds as usual
however because this information needs
to be retained a um
database trigger would make a copy
of the relevant data preserving a rose
foreign sex in the archive table
table is the same animal except that it
has two additional columns one of them
indicates the name of the retention rule
that caused the data to be stored that
would be the column called Rule and the
policy would be called Intervention
names and the
transaction ID is something that is
needed to keep asset rules in the
database
essentially there's electric correctness
tools and database to make sure that
recovery is possible
and in order to not violate any of those
we are also storing a transaction ID in
the table
but all of this information is available
in the sense that there's nothing here
that we create or use especially for our
mechanism we just implemented this way
using database to
and as a little deeper if you're not
familiar with what triggers are triggers
are an existing
common mechanism in database systems
completely Universal in relational and
SQL based databases
a little bit fuzzier but is being
developed for most nosql systems as well
as we discovered but at all of SQL based
like horrible opposers bicycle all of
that it's a completely standard
mechanism it's meant to to be used for
enforcing custom rules
um and the idea is it's um it's a
function that essentially sleeps and
waits for a condition to be met so you
can attach it to certain conditions and
when the condition
is met the function executes so you
could use it for things like make sure
that the
bank balance can never drop below zero
you could have something to check every
time you update the bank balance the
trigger wakes up and if the action would
have dropped it below zero it causes an
error to happen
my quiet being that this is a mechanism
that is universally available so all you
have to do is to encode with what you're
trying to achieve here using the same
system
and yeah
let it do what you need to do
all right
so
for all of those operations
as I said before so you can attach a
trigger to an event and the event could
be things like delete or update or
insert on the table
so that
we can attach a trigger for delete and
update operations and any type of delete
the update happens we can
um check against the policy
whether the value is under policy and
again if it's not then you're done
and the policy is just a SQL part so
the good news is that there you can go
around like okay and the database would
have to tell you
um exactly what's currently covered by
policy and then anything that needs to
be retained is is shuffled into archive
and at the moment to keep things simple
we are if there are multiple policies
covering the same piece of data we
simply make two copies
and and this way it's not possible for
policies to conflict
if you're wondering you know why that
would make sense like there there is
some performance overhead to that
um so there's definitely not something
to be optimized here but in practice it
might actually make sense because
value might be under multiple policies I
know it might be different durations
so customer information might have to be
detained for three years due to a state
law and for five years due to a company
policy
um so
in an Ideal World we'll figure out a way
to not you know create extra storage
um when things overlap but you do have
to wait for that because essentially
um you know if the data is being
destroyed after three years
you would buy like a five-year attention
so you have to keep an eye for
overlapping policies
and then one same thing is an archive at
least at the moment the idea is that
this all of this data is made only you
shouldn't really be modify America but
this week we're not aware of the reason
why you might have to mess with the
archive data and then in order to
not let data stay for too long
you need to have a process that
periodically gets rid of data that's at
the end of its life cycle
because like let's put something into
that cabinet with data and definitely
unless you get rid of it but
that part disease you can just go
through and Destroy things that have
exceeded their retention period
all right so let me talk about purging
marketing is going to be more
interesting
because there's a little bit of extra
additional complexity
um to that and we recently actually
tried have implemented a
combination of both because it turns out
that you have to also support purging
within everything
on data because data might be subject to
both retention and purging at the same
time but
just to clarify what what the challenge
is there's two pieces here like one is
just the fact that data almost never
goes away that's something we've learned
from our forensic work you'd be
surprised just how much data stays
around
it makes a makes for very useful
forensic analysis you can discover a lot
of things because things just stay
around like database indexes data never
disappears unless you to build the index
in general all systems are kind of lazy
in the sense that when you issued the
leak an item is flagged as they needed
but not actually erased because erasing
it does more work
therefore
it's true of file systems as well but in
databases it's particularly interesting
because you get this like Patchwork of
things where a bunch of deleted but
still remaining stuff is hanging around
in various ways and you could learn a
lot if you're doing forensic analysis
with that
um so there is
there is that concern
however in general
we considered almost a separate problem
or it's basically optimistically assumed
that if you delete the data it gets
deleted
although
that's not the case however the part
that's definitely an issue is the
problem of backups because
if you have a good policy and
you know making sure your data is not
going to be lost due to some sort of
issue then you have to have regular
backups and they might be you know
the local historic backups but for you
know good data survival you probably
have some cloud-based or offside backups
and if you think tapes are not being
used and
one of my PhD students as an argon
National Lab where he works they have
piles and piles and piles of things
I'll be able to send me a picture
sometimes I can include it so
essentially like even tapes are still
around mostly because they're much
cheaper and so once something goes into
backup there is just no way to keep
track of where it went and you might
have multiple copies because YouTube's
Black Ops regularly
therefore who knows in the coming places
your data makes inflate
so the primary thing that we're trying
to solve here is how would you delete
something that might have been backed up
at some point
and you know to be fair the actual
solution for that is not something we
invented so something that's known as a
cryptographic Erasure which is basically
this idea of encrypting something and
then erasing it by deleting the
decryption
because like you're not physically
deleting the data but since the
decryption key is gone then it's no
longer accessible
um and so here it actually works very
nicely
and
one of my colleagues does cryptography
work and research and he was telling me
that well it has been kind of an
exciting topic for a while but then they
abandoned it mostly because you can't
trust their mobile server to um to
actually follow through like once you
send something to remote server whether
or not that's encrypted they could have
made a copy but here it works
surprisingly well and specifically the
idea is that we set up
a collection of keys encryption keys for
different policies and different time
ranges I'll talk more about that in a
second and then when the data is
inserted into the table that is subject
to some policy
you encrypt the values that are subject
to policies according to when they
should expire
and you put that information into the
shadow table which is a game like
invisible copy of the active table with
which the user attracts
and when you go to the backup you back
up the shadow cable instead of active
one
and so that the idea is that anytime you
feel like destroying some data when it
passed up goes past this expiration date
you destroy the matching key in your key
table
and then the next time the data is
restored because all of it is encrypted
for the pieces for which you no longer
have to keep you just get rid of the
data and now it's gone right then like
if you restore a table from a while ago
or some of the values have expired then
the user is just going to see a bunch of
nulls where values used to be because
you're not able to decrypt them anymore
um and the bonus or kind of the nice
firewall of this
um is that it doesn't matter how many
pockets there are it doesn't matter
where they are right you just destroy
the key that matches your policy and
that automatically and remotely erases
any piece of data as long as you
properly encrypted it
on your behalf
all right so it's actually
surprisingly effective mechanism
although there are some implementation
issues and it's not entirely cheap we
didn't do it we did do some evaluation
it turns out encryption is actually a
big part of the cost
[Music]
oh
um there's also extra storage so it does
expand the data and you do have to store
the key identifier but
it might be a system specific thing as
I'll mentioned we did our experiments in
posters
and then in postgres I guess the
whatever encryption function was
available is actually pretty slow
compared to like
database to databases might be better
but the avoidable part of course the the
data is expanded so
we worried about this like one or two
papers on that and
like our goal is to make it moderate
expensive right it's not gonna come for
free
but our goal is to make it moderately
affordable I think storage is not a big
deal
uh performance and slowdown of Aquarius
as a bigger deal
but
that's kind of you know what we're
working on to see what's practical but
so I'm going to be free for sure it
seems to me that energy the key table
was probably one of the really tough
Parts about this you got to back it up
otherwise you lose the table yes yes but
it's hard to keep the backups around too
long you essentially still have your
data
absolutely so that's an absolute
excellent book which is what about the
encryption key table I can't tell you
how many fun discussions we had about
that first like my student wrote and
said hey we're just going to exclude
those vehicles from back up in the paper
I'm like no no then we're not gonna
argue that it's like I know this work
got media to say what well it's a
problem so let's just start back it up
but that would be terrible because when
you lose your data like everything would
just disappear then we had all sorts of
fun you know Turtles all the way down to
say that you have an additional tier of
an encryption a super encryption key
table that subscribe we were kind of
disgusting to see how far you know me
personally you could take it
um but the actual you know currents go
to solution we haven't evaluated
performance wise but our actual go-to is
that
I mean obviously you have to separate it
goes without saying it needs to be in a
separate database
but then grips and key table is actually
relatively small so for that you can
afford to partition and redo your
backups which is not something that's at
all practical with your action database
like it's sufficiently if it's small
that you could load and partition the
backup so that you could erase the
pieces that fell into expire time range
and then read back that up
uh so but yeah absolutely it's a concern
and as I said I was almost about to
build like a chain of encryption key
tables to get progressively smaller
but yeah you've caught on kind of the
biggest issue which initially we didn't
even think about it was it was really
fun we said well that's perfect and then
someone said oh wait a second one you
know what a few like as long as you have
the as long as you have the key table
you could still recover the data anyway
um and
there is also somewhere there's an
element of plausible deniability where
like if it comes a boy where you say
well like I made the reasonable effort
they destroyed the data
um
however like our actual long-term
solution of course the proper security
and privacy says that you cannot get
data and so our goal is definitely to
we just need to figure out the
performance practical way
to do this
um to make sure that you might mentioned
the encryption key table separately in
such a way that you actually do destroy
the key and although you keep backups
you need to kind of recreate and clear
out those backups as you go
um so another example of what
the purging rule looks like and in this
case all of the Super Saver data must be
purged after 20 years
again the important part is that
starting from the word select this is
just a SQL query which means the
database administrator understands what
it does and you could run it to see if
it even matches what you're trying to
achieve
um that that takes the customer and
sales table joins them together and
that's a condition
of one year inch so this is kind of an
example um and the important part that
all of those policies operate on
business records and use straight up SQL
so that you're not inventing any new
rules or any requirements that wouldn't
be supported in some other database
so to illustrate how this works
I have a customer table that has user
information and then
I have policies that require me to purge
social security number data
and it data in this case but not name
information so the shadow table has all
of the same columns plus the expansion
part is that for every value you have to
have the
not the decryption key but the
identifier that points to the encryption
key table that essentially says what key
is to be used to decrypt the value
customer information on the right you
have
[Music]
um
you have the three columns plus the
three additional columns that specify
that for example to to decrypt the
social security number from rows two and
four you would have to use the key
identifies as access key in this case
let's say they're both 500 same policy
which means that when this key in the
actual table is gone both of those will
be merged
and so the customer Shadow table the one
with encrypted data is going to be
backup
and as of now the encryption idea of
minus one means that this data is not
subject to any parking rules so you can
just have it as is it doesn't matter it
will never be distracted
and then
um should you at a store at the time
where X the the ones highlighted in X3 x
15 and x22 are gone
then you're not able to equip the data
and so you restore the data accordingly
the key was also purged
um and essentially like if either the
whole row or some critical parts of
their own like I identifier itself is
gone then the road goes away from
anything
however for for other data the social
security number has been purged and the
rest of the data is fine so when you
restore the table you decrypt it back
into the user facing table and that's
what you get from a restore
past the expiration date
and so all this requires
and this is something we actually have
done tested is tweaking the backup
process a little bit
so that instead of backing up the actual
tables you back up the equivalent Shadow
table and your restore process restores
the shadow tables and then decrypts it
back
according to the policy
this part is also potentially costly but
it's a much lesser concern because
backup and restore
well backup just backs up a little bit
more data it doesn't have to do any more
work it's just a slightly bigger table
which is not a huge concern the restore
process requires decryption but in
general speaking your start process is
not the interactive part of the database
like presumably I mean it might be a bit
slower but it's not like interactive
queries that have to be very quick so
our biggest concern is the encryption
part because that affects the query
itself
and speaking of that so um
whatever stuff happens to your database
that applies to insert and update
not so much for
not so much for delete for purging the
idea is that the user updates a value
and should that be under some policy
then a trigger wakes up checks and
propagates the updates to the database
when you insert the value you just store
the encrypted value in the shadow table
when you update you have to
well encrypt the new value and then
update the corresponding encrypted value
and shut things like essentially all of
the changes you make to the table you as
the user to the table are propagated the
shadow copy of the table so that the
shadow always reflects what the user
facing table is
because that's the one that's going to
be backed up obviously has to be up to
date in order for that to make sense
and this is also working with triggers
an existing mechanism and again most
importantly the user doesn't have to
know that all of this no no sorry the
only kind of concern is of course that
there's some extra work happening behind
the scenes and that can slow down
queries
somewhere I have a slide but basically
it's more infection is not cheap and but
it's more a function of how much data is
affected
which
so it's not really a multiple concern if
you update a couple of rows and I can
practice no one being updates millions
of rows
most of the time I'm going to say
Endeavor it's quite most of the time so
if you updated the large number of rows
that's probably something that's not a
school because obviously like the
trigger has to do its work and that will
contribute to the total for your runtime
uh but it happens entirely transparently
and again our biggest you know goal in
the biggest battle is to make sure that
there is no like weird errors there's no
like existing scripts that break
none of it is effective so like this
operation just like the previous one
kind of failed
um
and in general like all of the
transaction stuff would just uh work the
same way but both further attention
purging the transaction support that
exists in databases would operate
uh in the same way
so in some I think we've already covered
that but
if something doesn't fall under your
policy then your job is done although
you still have to propagate it into the
shadow cable
I guess I should mention that
um yeah like over here so if someone
updates a name it's not under any policy
so you don't have to do an encryption
but of course you do have to update the
shadow table so that the name matches in
both tables
oops
um and then should there be multiple
policies
unlike with the detention where at the
moment at least we just store multiple
copies for purging it's even simpler you
just have to reconcile
um multiple policies to see which one
wins out
and
hopefully I got it correctly we always
likely we always double double check to
make sure we're on the right side of
things like wow one of them goes by Max
and one by minute so for purging the
shortest purging period like yes
question why wouldn't you just do that
with retention as well just in the
opposite direction so if you have a
policy if you have a law that says you
have to retain for three years and your
policy is to retain it for five but
there's no Purge policy why not just
keep one cop copy that it keeps them for
five years
that's an excellent point
you're asked an excellent questions I'm
not sure I feel like my best to have the
reason because obviously this is easier
right so you're saying that if you have
a three and a five year retention policy
you just do a day in four five or unless
otherwise requested
that does make sense we do make multiple
copies
um so
yeah that sounds right I you know I have
to go back perhaps for some reason that
I'm forgetting
or maybe we just didn't realize that
that obvious was scaring Us in the face
but yeah I can't think of a
just to keep the policies independent to
each other
right so it makes sense if they have
multiple policies and
I feel like I remember having some
discussion it's possible that like you
could have an update to a policy that
was some debate
that we had like what happens if someone
changes the policy can you still affect
things that were already archived
but
yeah you're right
sorry no no no no no no no you're
absolutely you're absolutely right
thanks
good evening
um you're absolutely right and I'll just
have to go back and look at the paper to
see if there is actually some clever
reason that's like written there but
at the glance yeah it seems to make
sense
um
maybe because this paper was written out
there and like in the first paper we
didn't think of it yet
um yes so I'll double check and if so we
will really simplify the retention
policy as well
for parking we're definitely doing that
at least and apply the the longest
surround policies and again some of the
fun comes from the fact that
you could have multiple overlapping
policies within the same table so like
each individual value within the same
record might be subject to different
things
but you know obviously all of this
applies to individual values you can't
operate them they're always because you
don't know how much it covers and um
you could have two values side by side
that are subject to different things
um and then as far as the key management
there is you know a little bit more to
it
um specifically you can't really afford
to um
essentially have an individual key for
forever value like that is also a
discussion we had I said well like
technically it's a keeper value right
but the problem with that is that now
you have double the database at least
yes if you're there based on 10 zillions
of values it's exactly it's earlier that
he's where you literally have a key for
every value or at least every value on
their policy
um so the way that
we have that seems reasonably practical
is to actually create time range buckets
so that things under the same policy
within the same bucket of time share the
same key
so if you have two customer records on
the same day
that fall under the same policy but
they're separate records and they're
like half an hour apart then you have
them share the same key uh that is not a
completely arbitrary invention because I
believe summer in gdpr they actually do
mention that like a database thing is
acceptable
somewhere I have to look it up but my
student has followed somewhere where it
says like at least typical or by default
or whatever that like at the day range
meaning that your expiration date is not
like down to seconds but rather it's you
know in granularity of days
um and the other nice thing of course is
that once you establish the bucket thing
imagine there's some you could do that
with a nice Trader where you can make
your bike it's you know one second long
um and then you're gonna have keys for
everything or you could make it more
like month based and then it's just
going to be a much smaller table a key
table to message
it's always nice to just have a tunable
system because you like it's all the
requirement dependent I think I
Catherine assume what the exact
requirements are and it's much easier to
just say
um depending on what you need like what
the granularity is
[Music]
um so we have evaluated all of this in
postgres although again I've been
careful to check that all of this uses
standard database tools which is
essentially
the user or SQL queries and triggers
that that's all you're needing and in
terms of performance on postgres
not surprisingly the
policy size
um
is pretty much the thing that determines
your costs
and that seems reasonable so it's not
really the size of your database
necessarily but the scope of how much
retention you have to do so if you have
to retain a fraction of your data
and then it's not that bad but you know
if you have to retain all of your data
under various policies then it's just
going to be costing
and if you have multiple policies
obviously that's going to add up because
I have to check each one of them
um and then we tested purging
encryptions really costly
we're working to optimize it
but otherwise it all works
[Music]
and we can manage
all right so I'm not going to go too
much into that I have another slide to
talk about consent
but since I don't have ah yes I don't
have a lot of time
all right
in that case I'll just briefly mention
that so
[Music]
we make sure all of this is compliant
with transactional mechanisms within the
database so that
um
so nothing goes sideways because
obviously you can't violate existing
guarantees and we're currently working
on on tweaking
um improving performance although for
retention we might have just gotten the
solution right here to just
go by the longest policy but we were
looking at trying to eliminate
redundancy in some clever way and having
something maybe more efficient
to to propagate and encrypt data instead
of using triggers because trigger
operates immediately if you did
something asynchronously in the
background it would be more efficient
but then you really have to juggle the
um
to make sure that you're not breaking
any of the database existing guarantees
so I'll just add one more thing as a
little example so consent is something
we're still working on
we have one paper on the review
um it's quite strict here but just to
give a sense of the at least of the fun
and complexity behind it
so small example that we used so let's
say you have customer information that
you've collected such that the customers
um
give you permission
do not give you permission to use the
data for marketing so the consent is
applied in the sense that if someone a
customer gave you information when
they're placed an order then they
implicitly are giving you permission to
use it for order processing so you don't
have to ask again but if you want to use
the customer data for marketing you
essentially have to have explicit
permission and I encourage you to go
especially urus.com we use the picture
from their website because the thing
that pops up has about 50 different
things to choose from permission a
permission B and then the whole list of
like language our data with the
following Partners in the list of that
as well
and so to just see why it's not that
simple as far as consent goes so let's
say you have this customer data and they
have
for the data you do not have permission
to use for marketing
now I have a query here
contrived the made up query that's
looking for people who are older than 40
and come from household
of
[Music]
I get greater than two I'm not sure what
I was going for in the policies but a
household above certain size the point
is aggregated data so essentially
because the consent refers to revealing
individual data
anyone who makes it
any particular user wouldn't give you
permission and makes it into output
is not
you should not be using or accessing
their data but any user who did not give
you their permission but who was only
used to perform an aggregation
like some sort of average for the for
the city and such that is still allowed
so even though like the the query is
used for marketing if you didn't if none
of individual user data made it in
college but if it might have been just
aggregated
um then it is in fact acceptable
and therefore the point is that in order
to answer some of those questions you
have to actually look at how the data is
processed it's not there enough to tell
what it's for
um and just kind of as an additional
example in the same category
um
the one of the permission types that you
might give refers to combining the data
with other sources so you might collect
the data that you can use but you're
explicitly not allowed to combine it
with the same person's data collected
throughout the means like customer data
versus like web Analytics and therefore
again what makes it a real challenge is
that you have to actually look at what
the database query is doing it's not
enough to say well like you can't use
marketing because this is marketing
query you have to look and see well what
am I actually doing with data and is it
or is it not violating
existing rules
all right
so
I think we've pretty much covered all of
this but as a summary our goal is to
incorporate all of those cool things
into database systems
all possible database systems are
generally as possible in such a way that
transparently operates doesn't break any
rules
and
is easy to support and hopefully
somewhat affordable overhead
at this point if there are any more
questions
I'd be habitats or if we have a few more
minutes yes
calculating that out
so every time that I needed to meet goes
into budget and I think of Hardware all
that other stuff
back so that
the hard work
meaning that objective I can actually
calculate about it so well I know
this
not only that and then extra encryption
piece because that's where we're going
we're using that to add into the formula
to determine can I meet the objective
with
[Music]
that piece
coming out to the people who are
I see that's an excellent point so the
question is how does the decryption cost
factor and the objective of recovery in
the store runtime and this is actually
one of the reasons I come to
Real World videos because like this is
not the first question that I come up
with you know being an Academia and I'm
aware of like additional real world
requirements so like the answer is no we
did not try to measure that uh but yeah
it sounds like the fact we really should
one of the problems is that it's a
function of how much of the data is
impacted which you would have to measure
but
I'll you know all that into the to-do
list uh to try to figure out so try to
estimate the extra overhead which would
in fact extend the recovery time
you're absolutely right would have to
have some sort of estimate because
unfortunately it's not even the function
of your data right but like the amount
of stuff covered under policy
yeah whatever is going to be done then
gives you a number and let you know what
that is right
right yeah see this is again like the
the tricky part I I'm mostly worried
about kind of the user query being
slowed down because I at least clearly
understand that
um recovery seems see that was amazing
thing to say recovery seems like a
lesser concerned because you're not
going to have like a user sitting in
front of a terminal but
recovery guarantees having an actual
deadline to me while that is just as
much of a problem uh but like a
different issue because you're doing the
overall Data Center
yes so if I understand correctly when
you're talking about making the policy
and mapping that to the technical
control it's easier if the database
administrator and the policy maker are
in are in Communications on how to
describe the policy like the SQL query
inside the policy too
to make it easier to map to an actual
great so the question is you know is it
is it better to have the database
administrative and policy maker you know
sit down and translate the textbook the
policy into SQL query and the answer is
yes no how is it easier I think you
pretty much have to do that and that's
perhaps the least unrementable part
because once it's like our goal is once
it's in SQL form we can automate it
through database tools but if the rule
says you know customer data must be
retained for three years someone's got
to tell you what that means and they
would have to put it into terms the
database administrator can write a SQL
part in fact it's required then it's
like the manual step that we currently
have especially because it's not just
policy maker oftentimes as I understand
at the larger scale it's also a legal
counsel and sits down it says well is
this column you know really a part of it
or does it pull into this policy
um so it might not even be enough to
have a policy making a database
administrator you might have a lower
sentence as well so yes absolutely you
have to do that and one day we hope to
make it at least semi-automated but you
would have to do text analysis and who
knows what else
[Music]
have enough resources to do that
database
oh absolutely yeah
I'm not sure how it works though to be
honest like I don't have a clear
understanding of the database and the
technical side of it and I like I can
tell you for a fact that for certain
things like there's just no way that you
could meet those requirements
[Music]
or managing the data not know how much
data they actually have to save
money to make those calculations
how much database or Hardware do you
need to have or save those things how
much is that going to cost
[Music]
right absolutely
right then again it might be a little
bit naive but like sometimes I'll look
at those pulses and I can just react and
say like as far as I know how database
works you can you can't beat this
requirement and then like from forensic
analysis perspective even if you think
data is being deleted it's just not like
I know how much of it stays around that
there is just no physical way to uh at
the military part so the part we're
trying to solve is you still have to do
that translation part you still have to
sit down with database administrator but
the idea is like once you can kind of go
back and forth and agree then the whole
business like database administrator
could both verify what is happening and
have an easy tool to actually enforce it
but I mean actually jumping that
boundary probably be a new research
project of helping translate policy text
to to require because
equivalent of the policy you still have
to actually enforce it and there is no
built-in tool to do that
if I say that you're always asking how
can I save space
you know and that's always where that
comes back takes
yeah unfortunately yeah this is not
gonna save space I can tell you that
like it's going to cost extra like all
we're trying to do is to kind of keep a
lid on how much overhead it is and
hopefully have some sort of proof not
just the implementation where we say
trust this but something which you can
validate do you have like someone comes
in that you could generate a report and
say like we are compliant here's a list
of things and like we can confirm it but
obviously
looking at the overhead and like
ultimately it's like well it's going to
cost you a complex it's going to cost
you and
you know hopefully and again my my
attorney colleague said that it's a
process and like people keep pushing and
eventually
you know muscle declined because again I
can tell you for a fact that if you're
having sort of urging policy then like
every backup must be in violation of it
I'm not a lawyer but as far as I can
tell this is just the case then like
even if you have the best intentions you
can feel it
um
delete data for which you don't have a
full inventory I guess something's okay
you just can't like it doesn't matter
how much you want to you cannot
and
[Music]
I my understanding is that
this might be tested in court sometime
soon but basically you can't argue you
don't have it and so the other part of
motivation is that it's also potentially
liability
so like if I didn't argue that you still
have that data then you still have it
and you might be requested to produce it
there's some but there's some fun legal
cases like trading off the data that
must produce on the cost of producing it
there were some cases a few years ago
but
ultimately
this is going to be resolved eventually
I think it's just still involving
involving field because gdpr is new and
CCPA is even newer and like the new
policies that are being passed like
right now I think it was like a few
months ago that they announced that
they're going to start they're going to
enforce the CCP in California I think it
was like within a year or so that they
passed it before but that was some grace
period
thank you January 1st there you go so
like now they're actually enforcing it
and
it's yeah they're uh we're actually
we're working on trying to build the
defined database there are several
websites and tools that mostly for gdpr
that are keeping track of either like
the biggest or like an overview of fines
that are imposed and the reasons for it
to finally
maybe you guys can tell me if this is a
real world problem but I'm kind of
fascinated with the idea of that partial
purse where I would say after three
years the social security number isn't
retrievable anymore effectively turns
into the null
what does that do to the application the
problem is expecting a value to come
back and it was a written handled or not
that's an excellent question so like
what happens to what happens to the data
that's that's partially destroyed yeah
the application might not like it the
thought has occurred like one the
simplest example if your table does not
allow you to have a null then like that
would be a problem in the store
um one thing that someone had suggested
when I gave this talk before and I think
we can do it through um encryption
decryption mechanism is to essentially
anonymize it meaning that like what
sudanonymizes it where you decrypt it
not until now but into some other
unrelated value that's consistent right
so especially for things like salaries
or whatever but the idea would be that
maybe the same value you know Maps into
something else but it's some random
amount and then this way the value of
the meaningless but it's definitely not
going to break the application
so you could either have like a
placeholder or it's on our to-do list to
have something that decrypts into
anonymized uh value rather than the null
because yeah you're right for some cases
application my Traders don't enjoyed
that
within the application
you could also do it default but I bet
you it's not gonna fly because for
example social security number would
typically have a unique tag on it
uh
people do that it wouldn't necessarily
be well wouldn't necessarily be a key
but it would definitely be unique so
having like repeating Social Security
numbers is also not great
um so I think the solution is to
essentially do the anonymization
meaning that if you replace it by some
valid value that follows the same rule
so that none of the applications or
queries you have freak out
but of course like you'd still have to
be aware of the fact that there was no
valid numbers
to random Social Security
or you know alternatively you flag
someone like a bunch of people that's
having a fake Social Security number
because they all have the same value
and there's still a lot of work to be
done which is why I'm like I'm out here
trying to learn a little bit more about
video World applications because I'm
really trying to make it work in
practice because I'm aware of how often
academic Solutions are just that
you know in classroom and complete
prototype
all right well thank you very much
right here
[Music]