I created a master key in my database...

   create master key encryption by password = 'pw';

Then I created a certificate...

    create certificate my_cert
    with subject = 'encrypt cc numbers';

Then I created symmetric key...

     create symmetric key my_sk
    with algorithm = aes_192 encryption
    by certificate my_cert

Then I open the key...

     open symmetric key my_sk decryption
    by certificate my_cert;


Now I can encrypt data....

     SELECT encryptbykey(key_guid('my_sk'), creditcardno) FROM CreditCards


Later I incorporate the encrypt and decrypt functionality into a stored
proc. 

I want all users to be able to execute the stored proc but based on the user
I want to either 1) return the encrypted text in the clear  or 2) only the
first 4 characters in the clear and the rest of the number masked with 'X's. 

I can easily do this in the proc but I don't understand how to apply
permissions. 

I have to open the key to access the encryption functionality but do I do
this with an open symmetric key statement at the beginning of the proc?  And
how do I grant rights to the certificate to all users so they can all open
the key?

Does anyone have any experience with these issues?  If so, can you help me
out with some ideas?

Thanks
Dave

Look at the examples for the EXECUTE AS statement in Books Online.


ML

---
http://milambda.blogspot.com/

Thanks ML

Yes, EXECUTE AS owner appears to work.

Here is abbreviated code from my proc.  Can anyone tell me if this approach
would be considered "standard practice" for an encrypt/decrypt stored proc? 

Or is there a more widely accepted approach?

Thanks
Dave


CREATE PROCEDURE sp_EncryptDecrypt
        @deText varbinary(6000) = NULL
        ,@enText varchar(128) = NULL
WITH EXECUTE AS owner  ---allow caller to use cerificate
AS

    IF @enText IS NULL AND @deText IS NULL
    BEGIN
        RETURN -1
    END

    IF not exists (select 1 from sys.openkeys WHERE key_name='sk_creditcards')
    BEGIN
        open symmetric key sk_creditcards
    END

    --decrypt
    IF @deText IS NOT NULL
    BEGIN
        --these user get output in clear
        IF @user IN ('user1', 'user2')
        BEGIN
            SELECT convert(varchar(60), convert(varchar, decryptbykey(@deText)))
        END
        ELSE --everyone else gets mask except first 2 and last 4
        BEGIN
            SELECT @OutString=convert(varchar(60), convert(varchar,
decryptbykey(@deText)))
            SELECT substring(@OutString,1,2) + REPLICATE ('X' , len(@OutString)-6 ) +
substring(@OutString,len(@OutString)-3,4)
        END

        close symmetric key sk_creditcards
        RETURN 0
    END

    --encrypt
    IF @enText IS NOT NULL
    BEGIN
        SELECT encryptbykey(key_guid('sk_creditcards'), @enText)

        close symmetric key sk_creditcards
        RETURN 0
    END


--Test
--    EXEC sp_EncryptDecrypt     ---errors w/no params specified

column dataType
Joining 2 select statement with like fields
Views and CTEs performance problem
Applying a function to a subquery
Query from a dynamic table