SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Preventing Identity Theft Using SQL Server


Preventing Identity Theft Using SQL Server

Author
Message
Yaroslav Pentsarskyy-353753
Yaroslav Pentsarskyy-353753
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 3
Comments posted to this topic are about the content posted at temp


Regards,

Yaroslav

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18417 Visits: 12426

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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Chris Chilvers
Chris Chilvers
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 215

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
drnetwork
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 82
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
Luke L
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 6139

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

For better help with performance problems please read this
Steve-367196
Steve-367196
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 1

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
Confucius247
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 40

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
Yaroslav Pentsarskyy-353753
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 3

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
Steve-367196
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 1

@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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search