
USE [DataEncryptDemo]
go

-- Note here the use of EXECUTE AS for a temporary, controled elevation of privileges in order to access the certificate
-- An alternative would be to digitally sign the function and grant CONTROL on the certificate used to protect the data 
-- to a user mapped from the signing certificate
CREATE FUNCTION [dbo].[Cards_decrypted_Helper] ( @SecretData VARBINARY(256))
RETURNS NVARCHAR(50)
WITH EXECUTE AS 'DBO'
AS
BEGIN
RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData ))
END
go

-- CREATED FUNCTION

CREATE VIEW [dbo].[CreditCardsView2]
AS
 SELECT CardID as CardID, [dbo].[Cards_decrypted_Helper](CardNumber) as CardNumber FROM [dbo].[CreditCards]
go

-- CREATED VIEW

-- NOTE: We can revoke the additional privileges
REVOKE CONTROL on certificate::[cert_SecretTable_SecretData_Key]
 TO [user_low_priv] 
go
REVOKE VIEW DEFINITION on symmetric key::[SecretTable_SecretData_Key]
 TO [user_low_priv] 
go

-- REVOKED UNNEEDED PERMISSIONS

-- NOTE: We are using ownership chaining to allow any caller to the view to use the helper function
GRANT SELECT ON [dbo].[CreditCardsView2] TO [user_low_priv]

-- GRANTED SELECT ON NEW VIEW