Re: Encrypting SQL objects

(HeatherLotito@xxxxxxxxx) writes:
I have a question regarding putting secuirty on sql server 2005
objects, i.e. Stored procs, Views, triggers.

Basically we are installing our system onsite at a client's facility.
Since the classic ASP code can't be compiled, we wanted to encrypt the
stored procs, views, triggers on an as needed basis, because we will
be giving our client's IT staff access to some stored procs, views and
triggers, but not all. The problem is, we've seen that Microsoft's
encryption can be easily decrypted, thus we can't use that.

Look at http://www.activecrypt.com. They claim to have a product that
performs what you are looking for. I have not tested it, and I am
skeptic to that it holds what it promises. The situation is that the
engine must be able to read the source code at run to be able to compile
it. Thus, it must be able to decrypt the code. And to do that, it must
have access to the decryption key. And if SQL Server has access to it,
then any admin user can access it to.

We have also been unable to find a way to give limited permission to a
user in SQL Server. It seems the user can either have all or none type
access. But again, we want to chose which sql objects their IT
department can access and which ones we can lock them out of.

For a non-admin users, you can set permissions on a very find-grained
level. But obviously there will be people in the admin department
that will have admin access, at least in Windows. And if they have
that, they can stop the service, copy the database files to another
server and attach them there and then access as much as they like.

Or some other solution to protect our web application
and database from being broken into?

License terms. You can set up whichever technical solutions you want,
but if you don't have license terms, then those solutions are just
roadblocks waiting to be removed.

I would also advice you to put yourself in the client's situation.
What if in three years, your company goes bust, are acquired by
another company, or just grow tired of the product? Where do they
turn to get help if your application starts creeping like a snail?
In that situation, they probably appreciate if they can access the
code.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Whats wrong with triggers?
    ... On the Microsoft SQL Server platform triggers offer an extremely powerful ... > (via a PC app) and writes it to a database. ... > processed using triggers and stored procs and replicated to a central ...
    (comp.databases)
  • Re: Help with SQL 2005 and Sourcesafe 6
    ... scripted stored procs in VSS, do you then have to, say, run an alter script ... would that update the stored proc in the database, ... If you check in a piece of VB codes does that also compile the VB ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Encrypting SQL objects
    ... I have a question regarding putting secuirty on sql server 2005 ... objects, i.e. Stored procs, Views, triggers. ...
    (microsoft.public.sqlserver.security)
  • Re: New to SQL server
    ... it is called triggers. ... ANYTHING THAT YOU CAN DO IN JET IS BABY TALK COMPARED TO WHAT WE CAN DO ... anything that you can do in JET that I can't do in SQL Server ... self-referencing FKs and inline constraints. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Dynamically selected columns with column switch option
    ... the middle tier passes to the stored procs. ... So we are facing a design problem here. ... they might have to add new requests with new column sets. ... broad SELECT statements will force the sql server to produce huge ...
    (microsoft.public.sqlserver.programming)