Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / November 2006

Tip: Looking for answers? Try searching our database.

Securing my Stored Procedures?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jay - 27 Nov 2006 06:18 GMT
Hi,

I have a database that I have created. Currently, I am the only user of
this database. I connect as 'sa'.

Now, I need to move it to a new Company Server.
The logic in the Stored Procedures in my proprietary and I don't want
any body else to be able to view/edit/delete these existing stored
procedures but be able to execute them. I will now have another
programmer adding new ancillary procedures, create new tables write
front-end programs to call my proprietary procedures etc.

Basically, I don't want any body to be able to view my existing
procedures except me. Is this possible? If so, how do I do this? Once
complete, the database will not have many users - 1 or two, hence, one
'sa' account and one, two other accounts will be more than enough.

Thanks.
Chris O'C - 27 Nov 2006 08:09 GMT
> Basically, I don't want any body to be able to view my existing
> procedures except me. Is this possible? If so, how do I do this?

You can encrypt them, but then no one can easily read them, including you.
First make a backup copy of the procedures and store them somewhere for your
use only (source code control is perfect for this), then use the WITH
ENCRYPTION key words to encrypt the procedures.  For example:

CREATE PROCEDURE uspTest
AS
SELECT *
FROM Authors
GO

sp_helptext uspTest
GO

ALTER PROCEDURE uspTest
WITH ENCRYPTION
AS
SELECT *
FROM Authors
GO

sp_helptext uspTest
GO

The first execution of sp_helptext will show what's been stored in the
Syscomments system table, but the second execution of sp_helptext will show
this message:  "The object's comments have been encrypted."  Everyone who has
permissions to run the SP will be able to, but no one will be able to read
the source after the encryption unless he uses a hacker's tool.

If encryption is news to you, realize that it's probably not news to the
company's DBA and programmer.  If they're experienced, they already know how
to decrypt the SP's.  But a lot of companies don't employ experienced SQL
Server people, so your encrypted SP's may be safe from prying eyes for the
time being.

Alternatives are extended stored procedures and, if you have SQL Server 2005,
writing procedures with a CLR language.  These require skills beyond T-SQL,
but the code is separate from SQL Server and requires other, more
sophisticated hacker tools to read the source code.
Jay - 27 Nov 2006 09:15 GMT
Ok, sounds perfect for me.Someone can delete/overwrite it but that is
ok with me as no one can "easily" read my logic.

Thanks.

> > Basically, I don't want any body to be able to view my existing
> > procedures except me. Is this possible? If so, how do I do this?
[quoted text clipped - 43 lines]
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-security/200611/1
Arnie Rowland - 27 Nov 2006 17:57 GMT
Jay,

As Chris indicated, 'easily' is extremely relative. Anyone that is a little
bit SQL Server 'savvy' and knows how to use Google will readily 'break' the
encryption.

It's like the lock on the front door of your home, it only keep the honest
people out.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

> Ok, sounds perfect for me.Someone can delete/overwrite it but that is
> ok with me as no one can "easily" read my logic.
[quoted text clipped - 57 lines]
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-security/200611/1
Chris O'C - 28 Nov 2006 05:03 GMT
> Ok, sounds perfect for me.Someone can delete/overwrite it but that is
> ok with me as no one can "easily" read my logic.
>
> Thanks.

You're welcome, but before you think it's "perfect," do an Internet search to
find out how easy it might be to find out how to decrypt the stored
procedures.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.