RE: security of indexes (indexed views)

From: Bob (bob_at_bob.com)
Date: 09/20/03 

Date: Sat, 20 Sep 2003 08:57:42 -0700

Bill,
You are right, using the view the data is in clear text.
That is ok because I can control who has permission to the
view.
Think about it this way. If the string "%$$^$%#" exists
in mytable, that does not mean that the clear text version
actually resides in the database mdf file at all. So if "%
$$^$%#" decrypts to "secretString" using the view, you
could search high and low for the word "secretString" or
73 65 63 72 54 74 72 69 6E 67 (hex version) and it will
not be found anywhere in the db. I did a test using a new
empty db. By creating one table with one column and one
row containing the value "$$^$%#", I could use a hex
editor to find the string once in the mdf file. The
string "secretString" was not found. No surprise. When I
created an indexed view on the table, the hex editor
revealed one version "$$^$%#" and 2 new
versions "secretString". One for mytable, one for the
virtual table created for myIndexedView, and one for the
index of the virtual table.

Now, from a security perspective, I am vulnerable from 3
sides. Not just one. The view, the virtual table created
by the indexed view, and the index of the virtual table.
I know if someone gets ahold of a backup file, ldf file or
mdf file I am in trouble. But can the
string "secretString" be found using Query Analyzer?

 Thanks again for you help
 Bob

 
>-----Original Message-----
>Hi Bob,
>
>>From your post, you are able to access clear text data in
encryptTableView
>view. Making encryptTableView view indexed view may
increase select
>performance.
>
>However, I do not understand your questions. Since the
data from
>encryptTableView view is already clear text, what do you
mean by "Is is
>possible to access the data in the indexed view"? Could
you describe your
>scenario in more details?
>
>
>Bill Cheng
>Microsoft Online Partner Support
>
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>--------------------
>| Content-Class: urn:content-classes:message
>| From: "Bob" <bob@bob.com>
>| Sender: "Bob" <bob@bob.com>
>| Subject: security of indexes (indexed views)
>| Date: Thu, 18 Sep 2003 12:35:57 -0700
>| Lines: 38
>| Message-ID: <04a201c37e1c$155c4b20$a301280a@phx.gbl>
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>| charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Thread-Index: AcN+HBVaWhtESsTjReOdWfHvZPI5pg==
>| Newsgroups: microsoft.public.sqlserver.security
>| Path: cpmsftngxa06.phx.gbl
>| Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.security:15889
>| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
>| X-Tomcat-NG: microsoft.public.sqlserver.security
>|
>| 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 where 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 questions are
>| 1) Is is possible to access the data in the indexed
view?
>| Since the data is clear text, that would not be good.
>|
>| 2)Are you sure there is no way to view data inside an
>| index?
>|
>| Thanks for you help
>| Bob
>| www.cake&eat2.com
>|
>|
>
>.
>

Quantcast