Re: Can web site data be protected from access by the webmasters?
From: Morris Lewis (Morris@Holistech.com)
Date: 07/01/02
- Next message: Morris Lewis: "Re: Change username of "SA""
- Previous message: Brad M.: "Re: My own security subsystem"
- In reply to: Skillman Hunter: "Re: Can web site data be protected from access by the webmasters?"
- Next in thread: SQL Guy: "Re: Can web site data be protected from access by the webmasters?"
- Reply: SQL Guy: "Re: Can web site data be protected from access by the webmasters?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Morris Lewis" <Morris@Holistech.com> Date: Mon, 1 Jul 2002 00:11:54 -0500
inline
"Skillman Hunter" <ski@acrobyte.com> wrote in message
news:Oc1fOLBICHA.1060@tkmsftngp11...
> The client already has a contract for this project and knows
> little about web site design or internet security.
> Canceling a contract can be an expensive hassle.
> The client contacted me after the fact of contract signing.
> That the client did a stupid thing is a given.
> Happens every day! That is why they hire folk like us.
> (Consultant: Reads the books on clients shelf and tells
> client what they say.)
> I am trying to evaluate options to give the client the best
> possible advice as to how to proceed from here.
> The obvious answer is to fire the contractor.
>
> But I don't want to overlook an obvious way the issue can be handled.
> I am trying to anticipate what the other side may say,
> "Oh sure, you can just have someone encrypt the SQL Server tables
> and we will never be able to see the data."
> But they could build an HTTPS web page that retrieves all the records
> and lists them.
> So my question seems to boil down to
> "How does HTTPS/SSL interact with encrypted SQL Server tables?"
> Can we have three passwords:
> 1) Users enter to submit new data or edit old data.
> 2) Web contractor enters to edit web pages, but they see encrypted data.
> 3) Full access to see actual data.
> Seems like this is a good security plan even without the contract hassles,
> given all the hack attempts I get on my system everyday.
You're missing the point somewhere. SSL does nothing but encrypt the stream
between the server and client. It has nothing to do with SS logins or
database permissions. It's strictly for securing the data stream on the
network.
Encrypted whole tables make no sense at all. Besides the question of how you
would do such a thing, the real issue is that SS's job is to answer queries.
Having the data encrypted on the server won't help because the resultset
would have to be readable by the client. If someone has SELECT permission,
he has permission to see the data. Unless you want to write your own ODS or
OLEDB drivers, there no way to encrypt the resultset so that the user who
sent the query can't see the contents. You can do a lot of stuff to protect
the data when it travels between the client and server, but you can't
prevent the user from seeing the data when it gets to his computer.
If you wanted to encrypt every field in a table, you could use the Crypto
API to encrypt it before you send it to the database. If you want a user to
be able to read the data later, it needs to be encrypted with a key the user
can reproduce. You can't store a encryption key in the application because
the developer would see it; therefore, it has to be something based on the
user's password. The problem with that method is that a trusted admin will
not be able to see the data without the password. The question then becomes
how to store the user's password so that only the trusted admin can see it.
You could create a table to store user passwords and grant access only to
the admin, but db_owner can change the permissions. I'm assuming the web
developer will be a member of the db_owner role, so that won't work. The
next choice, then, is to store the user passwords in a table and encrypt
them with a key the admin knows before they're stored in the table. The
problem with this method is that the web developer can just take all the
encrypted data off the production server and hack to his heart's content on
one of his own computers.
Full access is the only easy part of the whole situation. You've got so many
options for that (db_owner, sysadmin, db_datareaders, custom permissions,
etc), you can pick whichever one works best.
What I think is happening is you're confusing encryption with
authentication/passwords and permissions. If you want to restrict the
developer, don't make his account a member of sysadmins or db_owner. (Don't
let him create objects either. That gives him full control over the object.)
Then create views on the tables he needs to use. Grant him permissions on
the views and no permissions on the underlying tables. If you want to
restrict the rows he sees in the table, use a WHERE clause in the select
statement that defines the view. If you need to control what he puts into
the table, create a trigger to check the contents before it goes into the
table. In short - I know it's too late for that - use the mechanisms built
into SS instead of trying to encrypt and password protect everything.
This is getting too hard to discuss online, so if you want a better
explanation of what I'm talking about, email me and we'll set up a time for
a phone call. I'm in the midst of writing a book for Wrox that covers just
SS security, so I'm completely focussed on all aspects of security right
now. Maybe I can come up with a plan that let's you use the developer and
still protects the data.
Morris Lewis
MCDBA, MCSD, MCSE+I, MCT, CTT+
President, Holistech Inc.
>
> Apparently Microsoft is pushing their "Palladium" system as a solution
> to this kind of problem by tossing out TCP/IP and encrypting the
> entire path from keyboard through microprocessor, NIC cards
> and the internet:
> See: http://www.pbs.org/cringely/pulpit/pulpit20020627.html
Doesn't help you now, so we'll ignore it.
- Next message: Morris Lewis: "Re: Change username of "SA""
- Previous message: Brad M.: "Re: My own security subsystem"
- In reply to: Skillman Hunter: "Re: Can web site data be protected from access by the webmasters?"
- Next in thread: SQL Guy: "Re: Can web site data be protected from access by the webmasters?"
- Reply: SQL Guy: "Re: Can web site data be protected from access by the webmasters?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
