indexed views / performance / encryption

I think this is an interesting question for anyone who
encrypts data in their db, but wants the performance of
an unencrypted field. This question was posted yesterday
in microsoft.public.sqlserver.security with no response.
I have a table that contains an encrypted field, I will
call the table encryptTable.
The only way that a user can view the data is by using a
view. For example
select col001 from encryptTable --returns
#&$@.!?`#*&$#
select col001 from encryptTableView --returns
mysqql_sucks
Because the field is encrypted, this query takes a lot of
time.
select * from encryptTableView where col001
= 'mysqql_sucks'
This takes a lot of time because every row in the million
row table has to be decrypted, to be compared to the
where clause.
If I were to create an indexed view then col001 would be
stored in a clear text format
and allow queries like:
select * from encryptTableView where col001
= 'mysqql_sucks'
to run in record time.
The question is...
Is is possible to access (read) the data in the indexed
view? Since the data is clear text, that would not be
good.
Thanks for you help
Bob
www.cake&eat2.comThanks for you reply, perhaps I was not clear enough. At
this point, I am not concerned if the query optimizer
chooses to use the index or not. I am concerned that the
clear text data in the index is readable. For example,
if select * from myindex returned the data stored in the
index, this would be bad. Is there any way to view the
data inside an index?
>--Original Message--
>I think yes, by using a query hint and specifying the
index.
>Yovan
>"Bob" <bob@.nospam.com> wrote in message
>news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
>> I think this is an interesting question for anyone who
>> encrypts data in their db, but wants the performance of
>> an unencrypted field. This question was posted
yesterday
>> in microsoft.public.sqlserver.security with no
response.
>> I have a table that contains an encrypted field, I will
>> call the table encryptTable.
>> The only way that a user can view the data is by using
a
>> view. For example
>> select col001 from encryptTable --returns
>> #&$@.!?`#*&$#
>> select col001 from encryptTableView --returns
>> mysqql_sucks
>> Because the field is encrypted, this query takes a lot
of
>> time.
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> This takes a lot of time because every row in the
million
>> row table has to be decrypted, to be compared to the
>> where clause.
>> If I were to create an indexed view then col001 would
be
>> stored in a clear text format
>> and allow queries like:
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> to run in record time.
>> The question is...
>> Is is possible to access (read) the data in the indexed
>> view? Since the data is clear text, that would not be
>> good.
>>
>> Thanks for you help
>> Bob
>> www.cake&eat2.com
>
>.
>|||To my knowlegde there is not, but i am sure there is way at the data level.
Yovan
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||I think your confusing what an Indexed view is. You don't read from an
Index you read from the table but sql server may choose to use an index if
available to determine what rows to retrieve. You don't have the option of
selecting from an Index itself. In the case of an Indexed view you are
essentially creating a new table with the contents of the clustered index
expression of the view. So the answer is essentially Yes. If the user has
read permissions on that indexed view then they can indeed read the values
of that column. Do you really need to encrypt the columns you use to search
on?
In any case you might want to read up in BOL under "Indexes - Creating an
Indexed view" for more details.
--
Andrew J. Kelly
SQL Server MVP
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||Bob,
I'm not sure what tool you are using for the encryption, but here is another
option. If you are using ECB cipher mode your cipher text will always be the
same. So when you want to search for a value just encypt the value and use the
cipher text for that value as the search criteria.
To the best of my knowledge this only works with ECB. The downside is that ECB
is not terribly safe compared to other cipher modes. You may want to first
encrypt using CBC, OFB, CFB and then encrypt the data again using ECB.
Just a suggestion
Tom O
"Bob" :
> I think this is an interesting question for anyone who
> encrypts data in their db, but wants the performance of
> an unencrypted field. This question was posted yesterday
> in microsoft.public.sqlserver.security with no response.
> I have a table that contains an encrypted field, I will
> call the table encryptTable.
> The only way that a user can view the data is by using a
> view. For example
> select col001 from encryptTable --returns
> #&$@.!?`#*&$#
> select col001 from encryptTableView --returns
> mysqql_sucks
> Because the field is encrypted, this query takes a lot of
> time.
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> This takes a lot of time because every row in the million
> row table has to be decrypted, to be compared to the
> where clause.
> If I were to create an indexed view then col001 would be
> stored in a clear text format
> and allow queries like:
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> to run in record time.
> The question is...
> Is is possible to access (read) the data in the indexed
> view? Since the data is clear text, that would not be
> good.
>
> Thanks for you help
> Bob
> www.cake&eat2.com
>