This editorial was originally published on April 10, 2009. It is being re-run as Steve is on vacation.
I recently engaged in a discussion with someone that was building an application on SQL Server. This person had a bunch of SQL code that was being put in stored procedures and then being sent to client sites. The developers were worried about clients modifying their code and wanted to send "secure updates" to the client by encrypting the stored procedures and giving the clients the encrypted text.
Apart from the hassles of getting this to work, I asked by would they bother. There are decryption routines available and this isn't meant to be a secure way to hide your code. Heck, even application code can be decompiled, and if they're likely to mess with the code, they likely have the skills to get the source.
So for this Friday's poll, I'm wondering about how you feel about encrypting code in SQL Server. I want to know what you think.
Is there a point?
Is there a reason to encrypt stored procedure code? After all, there are many, many vendors that sell applications built on SQL Server, with stored procedures. Most of that code isn't encrypted and it's usually not a problem. Most customers don't mess with the code and there are usually prohibitions written into support agreements.
Personally I don't think there are many great ideas, and likely very, very few in the database space, that are worth securing. Someone doesn't buy a software package so the can learn how you wrote it. Most of them buy software because it solves a problem and saves them time. If you can deliver a well performing, and good looking application, no one cares about the code.
But I'm curious what the rest of you think, both end users and software developers. Is there really a good reason to worry about encrypting your code?
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
As a developer, if you need to go into the database and write queries, design tables, or determine the configuration of your SQL Server Systems, these tips should help make sure you're not unnecessarily sacrificing database performance. This eBook has 45 easy tips to improve the performance of your indexes and T-SQL queries, and hunt down problems within ORM tools and database design. More »
Creating and maintaining mutually beneficial relationships with external vendors is one of the pillars of good project management. Dwain Camps goes through what to expect and allow in your client-vendor relationship during the various stages of a given project to ensure its success and secure that all important win-win outcome. More »
Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.
Maximize your performance on the exam by learning how to:
Create database objects
Work with data
Troubleshoot and optimize queries
You also get an exam discount voucher—making this book an exceptional value and a great career investment.
Yesterday's Question of the Day
(by Chirag Patel):
Examine this code:
CREATE TABLE table1
, lastname VARCHAR(50)
CREATE TABLE table2
, address2 VARCHAR(50)
, zip VARCHAR(50)
, name VARCHAR(50)
INSERT INTO table1
VALUES ( 'f1', 'l1' ),
( 'f2', 'l2' ),
( 'f3', 'l3' ),
( 'f4', 'l4' ),
( 'f5', 'l5' ),
( 'f6', 'l6' ),
( 'f7', 'l7' );
INSERT INTO table2
VALUES ( 'add1', 'add2', '12300-12', 'f2' ),
( 'add3', 'add4', '12300-14', 'f4' ),
( 'add5', 'add6', '12300-20', 'f1' ),
( 'add7', 'add8', '12330', NULL );
If we run this query, which names will be returned from table1?
FROM table1 AS t1
WHERE t1.firstname NOT IN ( SELECT t2.name
FROM table2 AS t2 );
DROP TABLE table1;
DROP TABLE table2;
While executing the query, SQL uses three-valued logic, driven by the existence of NULL, which is not a value but a marker to indicate missing (or UNKNOWN) information. When the NOToperator is applied to the list of values from the subquery, in the IN predicate it looks somewhat like this.
t1.firstname NOT IN ('f2','f4', 'f1', NULL)
The expression "name = NULL" evaluates to UNKNOWN. According to the Three valued logic there could be any thing indicating (True, False, some indeterminate third value ).
In the present scenario we have "name = NULL" which leads to UNKNOWN as indeterminate value. Evaluating t1.firstname NOT IN ('f2','f4', 'f1', UNKNOWN) gives all rows filtered out and returns empty result set.
This script contains various queries I have written on the subject of security as it applies to a specific database. It's arranged so I can either (A) run one query I need alone or (B) run the whole thing as a batch to dump out everything at once.
Option B might not seem useful at first (information overload!), but consider that if your output in SSMS is set to "Results to Text", this will allow you to quickly copy off a single text file that contains everything you might want to know about that database's security at a point in time. Furthermore, the queries are carefully structured and ordered to be consistent. This makes two of these text files easy to compare with a standard compare/diff/merge application (e.g. WinMerge, Beyond Compare).
Database role membership
Database role and user permissions
Database-level SQL/Assembly module EXECUTE AS
Individual ownership of database objects if not owned by schema
Database users linked to server login
Database users that were linked to server login, but are not anymore
The last query is a re-write of the 'report' mode of sp_change_users_login.
Please also see related script "Security Queries: Server-level".
t-sql 2008 r2 concatenate
- In an sql server 2008 r2 database, I have one column that I need to separate the values. I then...
- I have written a stored procedure like
/****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Pivot, Unpivot and Cross Apply !!!! Nothing seems to work
- Consider the below T-SQL
CREATE TABLE Temp10(Source nvarchar(50),Target nvarchar(50),Property1 int,Property2 int,
Property3 int,Property4 decimal,Property5 nvarchar(100),Property6 nvarchar(50))
INSERT INTO Temp10 values('A','DEF',10,8,20,12.34,'Good','Bad')
INSERT INTO Temp10 values('A','GKL',2,14,0,20.4,'Bad','Good')
Undo the cross tab
- I'd like to put the data in table #Things into a normalized structure. Table #NewThings represents my desired output. I...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.