Defining Decrypt Defaults

Doing so avoids error message 10330:

Decrypt permission denied on object <table_name>,
     database <database name>, owner <owner name>

Using decrypt defaults on encrypted columns allows existing reports to run to completion without error, and allows users to continue seeing the information that is not encrypted. For example, if the customer table contains the encrypted column creditcard, you can design the table schema so that:

select * from customer

Add a decrypt default on a new column with create table. The partial syntax for create table is:

create table table_name (column_name datatype
		[[encrypt [with keyname]] [decrypt_default value]], ....)

• decrypt_default – specifies that this column returns a default value on a select statement for users who do not have decrypt permissions.

• value – is the value SAP ASE returns on select statements instead of the decrypted value. A constant-valued expression cannot reference a database column but it can include a user-defined function which itself references tables and columns. The value can be NULL on nullable columns only, and the value must be convertible into the column’s datatype.

For example, the ssnum column for table t2 returns “?????????” when a user without decrypt permissions selects it:

create table t2 (ssnum char(11)
     encrypt decrypt_default '???????????', ...)

To add encryption and a decrypt default value to an existing column not previously encrypted, use:

alter table table_name modify column_name [type] 
     [[encrypt [with keyname]] [decrypt_default value]], …

This example modifies the emp table to encrypt the ssn column and specifies decrypt default:

alter table emp modify ssn encrypt 
     with key1 decrypt_default '000-00-0000'

To add a decrypt default to an existing encrypted column or change the decrypt default value on a column that already has a decrypt default, use:

alter table table_name replace column_name decrypt_default value

This example adds a decrypt default to the salary column, which is already encrypted:

alter table employee replace salary 
     decrypt_default $0.00

This example replaces the previous decrypt_default value with a new value and uses a user-defined function (UDF) to generate the default value:

alter table employee replace salary 
     decrypt_default dbo.mask_salary()

To remove a decrypt default from an encrypted column without removing the encryption property, use:

alter table table_name replace column_name drop decrypt_default

This example removes the decrypt default for salary without removing the encryption property:

alter table employee replace salary 
     drop decrypt_default