?
Solved

Encryption Master Key

Posted on 2014-08-11
2
Medium Priority
?
423 Views
Last Modified: 2014-08-18
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

0
Comment
Question by:ukerandi
2 Comments
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 40255030
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
 
LVL 38

Assisted Solution

by:Rich Rumble
Rich Rumble earned 1000 total points
ID: 40256978
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you thought ransomware was bad, think again! Doxware has the potential to be even more damaging.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question