
USE [DataEncryptDemo]
go

-- Create a view so users can view the decrypted data 
-- without need to decrtpt & convert themselves
CREATE VIEW [dbo].[CreditCardsView]
AS
 SELECT CardId as CardId, 
 convert( nvarchar(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), 
 null, CardNumber )) as CardNumber 
FROM [dbo].[CreditCards]
go

-- CREATED VIEW

-- In order for the user to use the view it requires
-- READ access to the view
-- CONTROL access to the certificate
-- VIEW DEFINITION access to the key

GRANT SELECT ON [dbo].[CreditCardsView] TO [user_low_priv]
go
GRANT CONTROL on certificate::[cert_SecretTable_SecretData_Key]
 TO [user_low_priv] 
go
GRANT VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
 TO [user_low_priv] 
go

