I have a requirement for which I need to encrypt and decrypt column data for a specific table or view. The application is on Universal GUI.
- The user needs to click
- a task button
- provide the password
- save it in the session cookie.
- When the user will want to see the decrypted information of the specific encrypted related View.
- A function will need to have access to the session cookie to retrieve the entered password of the user.
- This view will utilize the function to decrypt the column values upon refreshing the document.
- If No Password OR Wrong password was entered the view will show the encrypted information.
Any other idea how to implement something like this differently would be useful.
*Note: The database is already encrypted and that protects the data from outside theft, however it does not protect the data from Administrators within the company or people that have dattabase access. This solution is for covering the later part.
Below functionality of Azure SQL will be used:
--Create fist a certificate -- Self signed
CREATE CERTIFICATE <certificate name>
ENCRYPTION BY PASSWORD = <my password>
WITH SUBJECT = <subject>,
EXPIRY_DATE = <expiry date>;
--Verify certificate creation
select * from sys.certificates
--Create a symmetric Key --Use self signed certificate
CREATE SYMMETRIC KEY <testing key >
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE <certificate name>
--Verify the symmetric key creation
select * from sys.symmetric_keys
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY <testing key >
DECRYPTION BY CERTIFICATE <certificate name> WITH PASSWORD = <my password>;
,EncryptByKey(Key_GUID( <testing_key>), convert(nvarchar(100),non_encrypted_column_in_table)) as EncryptedCompName
,convert(nvarchar(100),DecryptByKey(EncryptByKey(Key_GUID(<testing_key>), convert(nvarchar(100),non_encrypted_column_in_table)))) as DecryptedCompName
Best answer by mperrottView original