Re: How does username without login work with certificate

Thank you for your response.

In our case, it seems like the username without login is inappropriate do
the job. What do you recommend if we just want to let some users to decrypt
the data?

Brian


"Bob Beauchemin" wrote:

Hi Brian,

To answer your first question, anyone with SSMS must have impersonate
permission on user::username for the "execute as" to work.

Whether or not you should use a password to protect a certificate rather
than the database master key depends on what you want to accomplish. With
the setup you have, you cannot keep the data away from someone who knows the
user name, has impersonate privilege on the user, and has the password for
the certificate.

Cheers,
Bob Beauchemin
SQLskills

"Brian Ho" <BrianHo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9BBE8C2-C220-4906-8C68-1EAC1E88D3D9@xxxxxxxxxxxxxxxx
We used a username without login feature to give CONTROL access to
certificate so that application can use this user account to decrypt
sensistive column. The set up is fine and we were able to decrypt the
column
with this username. This bring up an issue if someone who knows this
username and certificate password, they can see the decrypted information
too. Anyone with SQL analyzer can enter EXECUTE AS USER = 'username' to
grant access to the sensistive column. How can we prevent this or the way
we
set up this username is inappropriated?

Below is a sample code that we used to allow this username to decrypt
column
for your reference.

USE DEMO
GO
CREATE USER username without login
grant CONTROL on certificate :: CertKey to username
grant VIEW DEFINITION on symmetric key::SSN_Sym_Key to username
GRANT SELECT ON CUSTOMER TO username
GO

-- To decrypt SSN column
EXECUTE AS USER = 'USERNAME'
GO
Open symmetric key SSN_Sym_Key decryption by certificate CertKey with
password = 'certpwd'
go
Select CONVERT(varchar, DecryptByKey(SSN)) as 'Decrypted SSN'
from CUSTOMER
Where [LAST NAME] = 'Johonson'
GO

Is there a way to store the certificate password instead of hard code it
into the open symmetric key command?





.

Relevant Pages

  • Re: How does username without login work with certificate
    ... If you are OK with allowing the dbo the ability to decrypt (because dbo has ... users/role control on the cert and view definition on the symmetric key. ... it seems like the username without login is inappropriate do ... Whether or not you should use a password to protect a certificate rather ...
    (microsoft.public.sqlserver.security)
  • encryption
    ... my computer is a member of a domain. ... and recreated my username in the domain. ... even if i log on as administrator of either the ... i still can not decrypt my files. ...
    (microsoft.public.windowsxp.security_admin)
  • Re: How does username without login work with certificate
    ... anyone with SSMS must have impersonate ... Whether or not you should use a password to protect a certificate rather ... The set up is fine and we were able to decrypt the ... username and certificate password, they can see the decrypted information ...
    (microsoft.public.sqlserver.security)
  • WCF Message Security Problem
    ... username password authentication via a custom asp.net provider. ... password authentication mode requires transport or message security to ... I created the certificate with makecert following ...
    (microsoft.public.dotnet.languages.csharp)
  • The getRemoteUser() returns null
    ... Getting the username from a client certificate. ... Websphere Application Server v6.1 ... Websphere plugin for web server ...
    (comp.lang.java.programmer)
Quantcast