Preventing Identity Theft Using SQL Server

Question

Post reply

Preventing Identity Theft Using SQL Server

Yaroslav Pentsarskyy-353753

Old Hand

Points: 333
More actions
September 11, 2006 at 3:16 pm

#367327

Comments posted to this topic are about the content posted at temp
Regards,
Yaroslav

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

I understand the approach, but I don't understand the reasons for it.

Wouldn't it be a lot easier to just:

1. Create stored procedures for all data modifications;

2. Have checks for validity of any modification in those stored procedures, not in the web application (or at least not ONLY in the web application);

3. Make sure that these stored procedures have the same owner as the tables;

4. Deny all users all rights to all tables;

5. Grant execute rights on the stored procedures to the userids that need those rights.

This way, even if a hacker found his way into the database server, he'd still have no way to alter data. The only thing he could do is execute the stored procedures - and those will still perform all their checks before carrying out any modification.

Am I missing something?

Best, Hugo

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 2

I suppose the worry here is if the attacker were to gain sys admin rights on the database. Then the whole permission system fails to work. This solution could then allow one to trap that an unauthorized modification has been made (as opposed to preventing one from happening).

drnetwork SSCommitted Points: 1815 More actions · Answer 3

SQL CLR makes such things as generating a hash really easy.

Here is a quick function (as well as a RandomNumber generator) built from the framework encryption classes in C#:

Here is the C# code:

using System;

using System.Diagnostics;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Security.Cryptography;

using System.Text;

namespace JenzabarRSAEncryption

{

public sealed class EncryptionHelpers

{

// Global event log variables

const string gstrEventSource = "Jenzabar EX Database";

const string gstrEventLog = "Application";

public static byte[] SHA1Hash(string strInput)

{

byte[] byteInput = new UnicodeEncoding().GetBytes(strInput);

byte[] byteResult;

SHA1 sha = new SHA1CryptoServiceProvider();

// This is one implementation of the abstract class SHA1.

byteResult = sha.ComputeHash(byteInput);

return byteResult;

}

public static Int32 RandomNumberGen()

{

// Create a byte array to hold the random value.

byte[] randomNumber = new byte[4];

// Create a new instance of the RNGCryptoServiceProvider.

RNGCryptoServiceProvider Gen = new RNGCryptoServiceProvider();

// Fill the array with random values.

Gen.GetBytes(randomNumber);

// Convert the byte to an integer value to make the modulus operation easier.

Int32 rand = Convert.ToInt32(randomNumber[0]) +

256 * Convert.ToInt32(randomNumber[1]) +

256 * 256 * Convert.ToInt32(randomNumber[2]) +

256 * 256 * 256 * Convert.ToInt32(randomNumber[3]);

// Return the random number

return rand;

}

};

}

Here is the compilation command prompt lines used after you install the Framework 2.0 SDK (which is free):

call "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sdkvars.bat"

csc.exe /T:library /out:EncrytionHelper.DLL /recurse:EncryptClass.cs

Here is the T-SQL DDL code to setup the objects:

CREATE ASSEMBLY EncrytionHelper

FROM 'C:\temp\EncryptionHelper.dll'

GO

CREATE FUNCTION dbo.fn_sha1_hash

(@input nvarchar(4000))

RETURNS varbinary(8000)

AS EXTERNAL NAME EncrytionHelper.[JenzabarRSAEncryption.EncryptionHelpers].SHA1Hash

GO

CREATE PROCEDURE RandomNumber$Gen

AS

EXTERNAL NAME EncrytionHelper.[JenzabarRSAEncryption.EncryptionHelpers].RandomNumberGen

GO

Have fun.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

I'm not saying that using a hash to verify data isn't a bad thing, however why would you have your database that close to the internet? Why even allow for that attack route to happen?

This is a simplistic example, but why not have something closer to the following? Now they have to take over your webserver and get thorough another firewall, which of course would be redirecting to non standard ports, using SPI, probably some IP Sec policies, etc, etc... to really crank down on the surface area and limit their attack vectors.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Steve-367196 SSC Enthusiast Points: 177 More actions · Answer 5

The principle behind this approach seems sound: I would make one small adjustment though. All the info included in the string that you are hashing is public and the method used to hash is public too meaning that the hash generation process can be duplicated by anyone. You need to salt the hashing process to prevent having the whole scheme compromised though data or process duplication.

http://en.wikipedia.org/wiki/Salt_(cryptography)

Confucius247 SSC-Addicted Points: 424 More actions · Answer 6

Re: The surely you should have it behind x firewall and using y IP, and a million other things that never happen in the real world, it doesn't happen in the real world! As an app developer you often don't have any control over this stuff due to legacy issues, time constraints, budget, etc.

And the secret/salt to the hash generation process will be the order that you put the data together.

Yaroslav Pentsarskyy-353753 Old Hand Points: 333 More actions · Answer 7

OK, if an attacker compromises the system using buffer overrun for example then she will get a highest privilage of access and delete all the stored procedures or any blockers enforced by any policy as they directly own the DBMS now - so creating stored procedures in the SQL machine itself won't help a lot.

As for double firewall, if the packet is designated for sql server and it looks like every other packet (ie totaly legal) the firewall will let it thru - even if there is more then 2 firewalls. As nobody suspected this packed to crash the buffer - it will flow without any warning.

As to the reality of the attack . I wish everyhting was so unreal as it seems like. Code Red virus is my favorite example. Looking at the design of this virus - you might think that virus writer had an access to sql server code. There is so many similar attacks that penetrate even encrypted or SSL communication - that leads me to believe that we are not that secure anymore

Regards,

Yaroslav

Steve-367196 SSC Enthusiast Points: 177 More actions · Answer 8

@conficius247, you said:

"And the secret/salt to the hash generation process will be the order that you put the data together."

If you think about the issue of salt from an abstract perspective, using the order in which data is put together as salt can essentially be thought of as implementing table level security - once you crack the algorithm (which is the order in which the data is arranged), you have access to all the hashes that use it.

On the other hand, the abstract equivalent of using random values as salt is row level security - even if you figure out the salt for a single hash, it cannot be used on any other hash since the salt varies from hash to hash.

This abstract argument is similar to the rationale for having the scheme generate a hash for each user based on the combination of what they enter and the page where they entered it as opposed to just generating a single hash based on just the source page used to enter the data that is then used for all users.

- Steve