method encryption

 to make encrypt and decryption for table data----

1. Master Key -  a key which is used to protect the keys of certificates and symmetric keys in the database

2. Certificates - used to encrypt the data in the database

3. Symmetric Key - can be encrypted by using many options, like certificate, password, symmetric key. 

   There are different algorithms available for encrypting a key. 

   The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

   

   

Step 1

 

Create a Master Key first with the help of the below script>>>>>>

use TestingDB; //This is the Test Database created.   

Create master key encryption by password ='abc123'

Step 2

 

Once the Master Key is created, now it's time to create a Certificate.>>>>>

Create certificate C1 with subject = 'Credit Card Data'   

Step 3

 

Now with the help of certificate and master key create SYMMETRIC KEY.

 

Create symmetric key SK1 with algorithm = AES_256 encryption by certificate C1.

 

Once all these KEYs are created in the database, we can use those for encrypting and decrypting data.

 

Below is the script to encrypt the data in the column. Here I have created one TABLE named TestEncryption having 3 columns with its datatype as below. Note that the column in which we want to insert or update encrypted data should have VARBINARY as the datatype.

Id - INT

EncryptedCCNumber - varbinary (256)

CCNumber - Numeric(18,0)

Let's insert data in the column of the table,

 

Open symmetric key SK1

 

Decryption by certificate C1

insert into TestEncryption(Id, EncryptedCCNumber, CCNumber) values (1, ENCRYPTBYKEY(key_guid('SK1'),'5000'), '5000') 

Close symmetric key SK1

 

Now it's time to check if the data is encrypted or not so when you execute a simple query you will get the data from the Table as it is.

select * from TestEncryption  

IF you want to DECRYPT the data you can use the below script

 

Open symmetric key SK1

 

Decryption by certificate C1

select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as 'Decrypted CC Number' from TestEncryption 

Close symmetric key SK1

 

You will get an extra column named "Decrypted CC Number". This way we can insert/update and select the encrypted data from the table in SQL Server   

----------------------------------------------------------------------------------------------------

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15

Example: Encrypt with symmetric encryption and authenticator

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE MASTER KEY ENCRYPTION BY   

PASSWORD = '<complex password>';

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

In Object Explorer, connect to an instance of Database Engine.

On the Standard bar, click New Query.

Copy and paste the following example into the query window that is connected to the AdventureWorks sample database.

Click Execute

CREATE CERTIFICATE Sales09  

   WITH SUBJECT = 'Customer Credit Card Numbers';  

GO  

CREATE SYMMETRIC KEY CreditCards_Key11  

    WITH ALGORITHM = AES_256  

    ENCRYPTION BY CERTIFICATE Sales09;  

GO  

-- Create a column in which to store the encrypted data.  

ALTER TABLE Sales.CreditCard   

    ADD CardNumber_Encrypted varbinary(160);   

GO  

-- Open the symmetric key with which to encrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  

   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  

-- symmetric key CreditCards_Key11.  

-- Save the result in column CardNumber_Encrypted.    

UPDATE Sales.CreditCard  

SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  

    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  

    , CreditCardID)));  

GO  

-- Verify the encryption.  

-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  

   DECRYPTION BY CERTIFICATE Sales09;  

GO  

-- Now list the original card number, the encrypted card number,  

-- and the decrypted ciphertext. If the decryption worked,  

-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   

    AS 'Encrypted card number', CONVERT(nvarchar,  

    DecryptByKey(CardNumber_Encrypted, 1 ,   

    HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  

    AS 'Decrypted card number' FROM Sales.CreditCard;  

GO

 

 --------------------------------------------------------------------------------------------------

windows>>>>>local machine,key

sql server>>>smk(service master key)

database level>>>book(dmk-database master key)>>>--symmetric,asymmetric,self -sighned certs

                 adventure(dmk-database master key)

story(dmk-database master key)

study(dmk-database master key)

 

 

Creating and Assigning a Role –

###https://www.geeksforgeeks.org/sql-creating-roles/

First, the (Database Administrator)DBA must create the role. Then the DBA can assign privileges to the role and users to the role.

        Syntax –CREATE ROLE manager;

                Role created.

Grant privileges to a role –

        GRANT create table, create view

        TO manager;

        Grant succeeded.

Grant a role to users-

        GRANT manager TO SAM, STARK;

        Grant succeeded.

Revoke privilege from a Role :

        REVOKE create table FROM manager;

Drop a Role :

        DROP ROLE manager;