Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Preventing Identity Theft Using SQL Server Expand / Collapse
Author
Message
Posted Monday, September 11, 2006 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 15, 2009 7:51 PM
Points: 5, Visits: 3
Comments posted to this topic are about the content posted at temp


Regards,

Yaroslav

Post #307779
Posted Wednesday, September 20, 2006 2:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 6,133, Visits: 8,398

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
Post #309842
Posted Wednesday, September 20, 2006 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:27 AM
Points: 281, Visits: 190

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).

Post #309889
Posted Wednesday, September 20, 2006 6:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, 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.



Post #309895
Posted Wednesday, September 20, 2006 6:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:29 AM
Points: 2,655, Visits: 6,016

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
Post #309901
Posted Wednesday, September 20, 2006 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 26, 2007 11:55 AM
Points: 13, 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)

Post #309922
Posted Wednesday, September 20, 2006 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 27, 2009 4:01 AM
Points: 14, 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.

Post #309974
Posted Wednesday, September 20, 2006 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 15, 2009 7:51 PM
Points: 5, 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

Post #310015
Posted Thursday, September 21, 2006 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 26, 2007 11:55 AM
Points: 13, 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

Post #310409
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse