Use alter table to:
Encrypt or decrypt existing data
Add an encrypted column to a table
Add, drop, or replace a decrypt default
The following partial syntax for alter table includes only clauses specific to encryption. See the Reference Manual for the complete syntax.
Encrypt a column:
alter table tablename add column_name encrypt [with [database.[owner].]keyname] [decrypt_default constant expression]
Decrypt an existing column:
alter table tablename modify column_name [decrypt [with [database.[owner].]keyname]]
keyname – identifies a key created using create encryption key. The table owner must have select permission on keyname. If keyname is not supplied, Adaptive Server looks for a default key created using create encryption key or alter encryption key as default.
Example 1 To create an encryption key and encrypt ssn column in existing employee table, enter.
set encryption passwd '4evermore' for key ssn_key alter table employee modify ssn encrypt with ssn_key
If ssn in this encrypted by key1, alter
table would cause Adaptive Server to decrypt ssn using key1 and
reencrypt ssn using ‘ssn_key’.
Example 2 This adds an encrypted column to an existing table. Because keyname is omitted, Adaptive Server uses the database default encryption key:
alter table sales_mgr add bonus money null encrypt
Example 3 To decrypt credit card data that is no longer sensitive, enter:
alter table stolen_ccards decrypt ccard
If ccard was encrypted by a key protected by a user-defined password, precede this command with the set encryption key command.
Example 4 To add a decrypt default to an existing encrypted column, enter:
alter table employee replace salary decrypt_default $0.0
Example 5 A user-defined password that protects a keyname must be set using set encryption passwd before you can execute alter table. To remove a decrypt default from the encrypted salary column without decrypting the column, enter:
alter table employee replace salary drop decrypt_default
Use alter table to change an encrypted column. This operation may take a significant amount of time if the table contains a large number of rows.
Modifying a column for encryption can cause the row size of the table to increase.
You cannot use alter table to encrypt or decrypt a column belonging to a clustered or placement index. To encrypt or decrypt such a column:
Drop the index.
Alter the column.
Re-create the index.
You cannot use alter table to encrypt or decrypt a column if the table has a trigger defined. To modify the column:
Drop the trigger.
Alter the column.
Re-create the trigger.
If the type of the encrypted column which belongs to a clustered or placement index is modified, it results in the index being out of order. alter table displays an error. To modify the type:
Drop the index
Modify the type
Re-create the index
alter table reports an error if you:
Change a computed column to an encrypted column or change an encrypted column to a computed column
Enable a column for encryption where the column is referenced in an expression used by a computed column
Change a computed column to reference an encrypted column.
Encrypt a column that is a member of a functional index
Specify an encrypted column as a partition key
Encryption-enable a column that is already used as a partition key
