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. This 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
Returns the value “****************” instead
of returning the credit card data to users who lack decrypt permission.
These examples show how to add and remove decrypt defaults from tables.
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 key_name]] [decrypt_default value]], ....)
Where:
decrypt_default – specifies that this column returns a default value for users who do not have decrypt permissions.
value – is the constant value Adaptive Server returns on select statements instead of the encrypted value. The value can be NULL on nullable columns only. If the decrypt_value cannot be converted to the column's data type, Adaptive Server catches the conversion error only when it executes the query.
For example, the ssnum column for table t2 returns “?????????” when
a user without decrypt permissions performs a query on it:
create table t2 (ssnum char(11) encrypt decrypt_default '???????????', ...)
The command to add a new encrypted column with a decrypt default is:
alter table table_name add column_name type [[encrypt [with key_name] [decrypt_default value]], ....
The syntax to add encryption and a decrypt default value to an existing column not previously encrypted, is:
alter table table_name modify column_name [type] [[encrypt [with key_name]] [decrypt_default value]], …
For example, this 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 using alter table.. replace. The syntax is:
alter table table_name replace column_name decrypt_default value
For example, this adds a decrypt default to the salary column, which is already encrypted:
alter table employee replace salary decrypt_default $0.00
To remove a decrypt default from an encrypted column without removing the encryption property, the syntax is:
alter table table_name replace column_name drop decrypt_default
For example, this removes the decrypt default for salary without removing the encryption property:
alter table employee replace salary drop decrypt_default
