RE: Sql 2005 - how to allow users to decrypt table data using a database certificate ??

Hello,

Thank you for using Microsoft MSDN newsgroup. Great to meet you again. :-)

From your description, I understand that you'd like to know what permission
you need to grant to user group so that they could use EncryptByCert and
DecryptByCert to read/write tables when necessary. If I'm off-base, please
let me know.

If the users have permission of the table, they could read the data
properly by using the decryptByCert function by refererence the certificate
ID. For example:

SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardType)) AS CardType,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardNumber)) AS CardNumber,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes
FROM Customer


Also, users need to have the proper permission on the certificate if they
want to use the certificate in a SQL statement. We shall grant the
permission to the user or group by using sth like:

grant control on certificate::certficatename to testuser

Please check the following link for details:

http://msdn2.microsoft.com/en-us/ms186278.aspx

Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.asp
x#S5


Also, you could refer to "Key access control" topic in the following
article to use a SP to use a broker to do this job:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx


More relate inforamtion

http://blogs.msdn.com/yukondoit/articles/480854.aspx

http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx

918346 The user is not assigned to a default schema when you use Windows
authentication to connect the user to SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918346

If anything is unclear, please feel free to let us know. Have a great day.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.


.

Relevant Pages

  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: user defined Role - HELP
    ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • RE: HttpClientCertificate IsValid method
    ... Grant, ... | Content-Class: urn:content-classes:message ... | What are the precise capabilities of the IsValid method ... | sure that the certificate is only from that person (given ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Newbie to security
    ... Use sp_grantdbaccess to grant access to the database. ... databases when you are new to security. ... Microsoft SQL Server 2000 SP3 Security Features and Best ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server Express
    ... GRANT them permissions to do so with the GRANT command. ... GRANT CREATE DATABASE on DATABASE::xxx to SAM ... Yes, I am very familiar with configuring specific SQL Server accounts for access, and I am having no real problems in this area. ... really a good way to block administrator access without making your database pretty hard to administer. ...
    (microsoft.public.sqlserver.msde)