Lets dive into one of the amazing features of SQL Server regarding data protection and security. So far the data protection is mostly handled by the application layer, developers create login page/window for users to login and manipulate their own data and records. At the other hand, all the data stored in the back end database which DBAs can view and manipulate all the data or other users can hijack each others credentials (Username/Password) to do some illegal data manipulation on behalf.
How to find out illegal data manipulation was not done by the data owner? Or how to find out illegal data manipulation straight away?
Scenario: There is a real estate company which has its own sales system, this sales system has a function called ‘Contract Store’ which users can enter contract’s key information such as the real estate price, commission percentage, and also the sales person name. In this company there is a sales person which is trying to own the new contracts sealed by other sales persons by hacking the database system and update the records manually!
In SQL Server we can protect data by digital signing, which bounds the data to the real owner. Signing data can be accomplished by private key of either certificate or asymmetric key. The private key must be only in hands of the signer and the signature can be verified by anybody that has public key. Below figure illustrates the differences between encryption and signature.
SQL Server Database Administrators can sign the data by following the below steps.
- The certificate and the owner must be bound together.
- Revoke control permission of the certificate from public role.
- Sign the data by SignByCert() function.
- Verify the signature by VerifySignedByCert() function.
Below codes show the steps of the signing data.
Author: Hamid Jabarpour Fard