Encryption Master Key

Hi,
I wrote a Encryption following on line tutorial, I didn't understand how Master Key working.

I thought after create master key password , when user going to decrypt ,it will ask Master key password.
I need to write Encryption method only Certain users can see  ,DBA can't see the data
for that i need when userlogin asking key or password.
Please any one direct me how do any idea appriciate.

See my code
Create Master KEY ENCRYPTION BY PASSWORD ='Ryan1234'

Create Asymmetric Key User1Asymmetric
AUTHORIZATION user1
WITH ALGORITHM =RSA_2048



/* Create Symmetric Key */

CREATE  SYMMETRIC KEY User1SymmetricKey
WITH ALGORITHM =AES_256    --RC4  --RC2
ENCRYPTION BY ASYMMETRIC KEY User1Asymmetric




CLOSE SYMMETRIC KEY User1SymmetricKey ;
DROP SYMMETRIC KEY User1SymmetricKey;


select * from sys.symmetric_keys
select * from sys.asymmetric_keys

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] LIKE '%User1Asymmetric%')
DROP MASTER KEY


IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] LIKE '%User1SymmetricKey%')
DROP MASTER KEY



OPEN SYMMETRIC KEY User1SymmetricKey
DECRYPTION BY ASYMMETRIC KEY User1Asymmetric
INSERT INTO Customer Values(
1,
'Ryan',
ENCRYPTBYKEY(Key_GUID('User1SymmetricKey'),'Visa'),
ENCRYPTBYKEY(Key_GUID('User1SymmetricKey'),'123456')

)

CLOSE SYMMETRIC KEY User1SymmetricKey

Select * from Customer


OPEN SYMMETRIC KEY User1SymmetricKey
DECRYPTION BY ASYMMETRIC KEY User1Asymmetric

Select CustId,Name,CONVERT(VARCHAR,DecryptByKey(CreadictCardNumber)) as CarNumber
From Customer

Open in new window

LVL 10
ukerandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The Encryption is for protect you code not your data.
The master key is saved in master database so if the code is copied to another instance won't work unless you have create there the same key.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rich RumbleSecurity SamuraiCommented:
Have a look at my article here, to understand some of the limitations of encryption, both asymmetric and symmetric. http://www.experts-exchange.com/Security/Encryption/A_12134-Choosing-the-right-encryption-for-your-needs.html
You are after asymmetric, but I think you'll want something more like S/MIME uses, where you can have one key unlock a file, but several people can unlock that key. It's a mix of asymmetric and symmetric...

http://superuser.com/questions/554513/pgp-encrypt-single-message-for-multiple-recipients
-rich
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Encryption

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.