Enable encryption for user with the DMK not encrypted by SMK
- From: wh.mens.4s5s6s@xxxxxxxxxxxxxx
- Date: 27 Jun 2006 07:48:13 -0700
As dbo, I am attempting to allow encrypt and decrypt permissions for a
DAUser. Is it possible with the DbMK encrypted only by password (and
not by SMK - want to allow dbo control only)? The DAUser runs under a
schema with minimal permissions (update, insert, delete, select,
execute), and certainly no encryption permissions.
As DAUser, I understand how the DecryptByKeyAutoCert() function can be
used without the symmetric key or DbMK having to be first opened.
However, when I remove encryption by SMK I am warned that the DbMK has
to be first opened. Is this necessarily a permissions thing?
For example:
create master key encryption by password = 'Pufd&s@))%';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pufd&s@))%';
create certificate cert_sk_DAUser
authorization DAUser
start_date = '20060626';
create symmetric key sk_Person_InternalEmailAddress with algorithm =
triple_des encryption
by certificate cert_sk_DAUser;
grant view definition on symmetric key::sk_Person_EmailAddress to
DAUser;
close all symmetric keys;
Executing as DAUser, I would like to now run something along these
lines:
select convert( varchar, DecryptByKeyAutoCert(
cert_id('cert_sk_DAUser'), NULL, Person.EmailAddress ))
Where the EmailAddress is encrypted by the new key.
.
- Prev by Date: Re: sql2k5 security
- Next by Date: Re: SQL 2005 - Searching Encrypted SSN
- Previous by thread: DTS Package - Destination server is now off domain.
- Next by thread: SQLAGENTSERVER startup!
- Index(es):
Relevant Pages
|
