The decrypt_default parameter for create table and alter table allows an encrypted column to return a user-defined value when a user without decrypt permission attempts to select information from the encrypted column, avoiding 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
Returns the value “****************” instead
of returning the credit card data to users who lack decrypt permission.
Specify 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 Adaptive Server 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
Tthis 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 funcion (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
