Key-copy support

ddlgen generates DDL for key copies along with the DDL for the base key. For example, the following syntax generates DDL for “ssn_key” and its key copies:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key

The output from ddlgen looks like:

-----------------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
-----------------------------------------------------------------------------
print '<<<<< CREATING EncryptedKey - "k1" >>>>>'
use SampleKeysDB
go
IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE  o.uid=u.uid
	AND o.name = 'ssn_key' AND u.name = dbo AND o.type = 'EK)
		drop encryption key SampleKeysDB.dbo.ssn_key
IF (@@error != 0)
BEGIN
	PRINT "Error CREATING EncryptedKey 'ssn_key'"
	SELECT syb_quit()
END
go

-- The DDL is generated with a default password – 'password' as 
-- a password was specified when this key was created.

create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
passwd 'password'
init_vector random 
go 

alter encryption key SampleKeysDB.dbo.ssn_key
with passwd 'password'
add encryption with passwd 'passwd'
for user 'user1'
go

If you include the -XOD flag, the DDL for key copy looks similar to this:

alter encryption key SampleKeysDB.dbo.ssn_key add encryption 
with keyvalue
0x84A7360AA0B28801D6D4CBF2F8219F634EE641E1082F221A2C58C9BBEC9F49B501 
passwd 0x000062DF4B8DA5709E5E01 
keystatus 257 
for user 'user1' 
go

Encryption key copy (EKC) filter

ddlgen supports the EKC (encryption key copy) extended type on the -F filter argument, which suppresses the generation of key copies for encryption keys.

This example uses -FEKC to avoid creating DDL for key copies when generating DDL for the “ssn_key” encryption key:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key  -FEKC

This is the output from ddlgen:

-----------------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
-----------------------------------------------------------------------------
print '<<<<< CREATING EncryptedKey - "k1" >>>>>'
go
use SampleKeysDB
go
IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid
	AND o.name = 'ssn_key' AND u.name = 'dbo' AND o.type = 'EK')
		drop encryption key SampleKeysDB.dbo.ssn_key
IF (@@error != 0)BEGIN
	PRINT "Error CREATING EncryptedKey 'ssn_key'"
END
go

-- The DDL is generated with a default password – 'password' as 
-- a password was specified when this key was created.

create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
passwd 'password'
init_vector random 
go

Create table DDL

ddlgen can generate decrypt_default statements for encrypted columns along with a table’s DDL.

This example issues a ddlgen command on a table called employee, which has an “ssn” column that is encrypted with encryption key “ssn_key,” and a decrypt default value that is set to 100:

ddlgen -Usa -P -Sserver -TU -N db1.dbo.employee

The DDL output from the command is:

create table employee (
  ssn            int       not null  encrypt with ssn_key decrypt_default 100 ,
  last_name      int       not null ,
  first_name     int       not null
)
lock allpages
 on 'default'
go