Security issue need help asap

  • Hi friends,

    I want to know a couple of things. Can i encrypt fields in the database using encryption even if i don't have any source code with me or i don't have any ability to modify his/her program? since the program was developed by another programmer.

    In order to decrypt an encrypt filed do we have to modify SQL statements to use decryptbypassphrase, decryptbykey or decryptbycertificate statements?

    The next question related to extrusion detection. With out using triggers is it possible to determine a user who ran an SQL statement against a particular table, for audit purposes?

    Thanks guys for your suggestion.

    Sam.

  • You can absolutely use SQL Server to ENCRYPT a field without modifying the app's source code. DECRYPTING is a different story.

    You could create INSTEAD OF INSERT/UPDATE triggers to re-issue the insert/update statements after encrypting the appropriate field. To have the app decrypt the field, you could create a stored procedure that would be called for every SELECT statement the app issued. It would test to see if the encrypted field was requested by the query, decrypt it, and return it with the result set. However, this solution would require modifying the app's source code to use the stored proc.

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Let's take these in order:

    1. Yes, using triggers you can enforce encryption on the fields without modifying the application. However, as Seth has pointed out, you get stuck trying to get the data back out, because there's no equivalent process like an INSTEAD OF trigger which can fire for a SELECT. Now, if all access is through stored procedures (hopefully that's the way it was built for both performance and security reasons), then you can make changes to the stored procedures that are seamless to the application to handle the encrypt/decrypt. And in that case you can and should avoid triggers altogether for the purpose of handling encrypt/decrypt.

    2. Yes, statements will have to be modified to include the appropriate Decrypt functions. BTW, best practice is to encrypt the data using a symmetric key, encrypt that symmetric key using an asymmetric key or certificate, and encrypt the asymmetric key or certificate using the database master key. This represents the fastest way to encrypt/decrypt the data but helps protect the symmetric key. In this case you'd use DecryptByKeyAutoAsymKey or DecryptByKeyAutoCert functions.

    3. Yes, through server side traces (use SQL Profiler to create them). In SQL Server 2005 this is actually the only way to determine if someone executed a SELECT statement against a table. Triggers only are on INSERT, UPDATE, and DELETE. Now in SQL Server 2008 Enterprise Edition or Datacenter Edition you get the Audit object, which can do the auditing without resorting to traces or triggers.

    K. Brian Kelley
    @kbriankelley

Viewing 3 posts - 1 through 2 (of 2 total)

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