alter table

Use alter table to encrypt or decrypt existing data or to add an encrypted column to a table.

Syntax

Encrypt a column:

alter table tablename add column_name 
            encrypt [with [database.[owner].]keyname

Decrypt an existing column:

[decrypt [with [database.[owner].]keyname]]

keyname – identifies a key created using create encryption key. The creator of the table 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

Example 5

Create an encryption key and encrypt ssn column in existing “employee” table.

alter table employee modify ssn
         encrypt with ssn_key
grant decrypt on employee(ssn) to hr_manager_role,
         hr_director_role

Usage

Use alter table to change an encryption key. When the encrypt qualifier is used on a column that is already encrypted, Adaptive Server decrypts the column and reencrypts it with the new key. This operation may take a significant amount of time if the table contains a large number of rows.
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:
1. Drop the index.
2. Alter the column.
3. 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:
1. Drop the trigger.
2. Alter the column.
3. Re-create the trigger.
alter table displays an error if you alter an encrypted column’s datatype to be bigint, unsigned bigint, date, money, text, datetime, or time.
alter table displays an error if you alter a bigint, unsigned bigint, date, money, text, datetime, or time column to be an encrypted column.
alter table reports an error if you:
- Modify a computed column to be an encrypted column.
- Modify a column for encryption where the column is referenced in an expression used by a computed column.
- Modify an encrypted column to be a computed column.
- Modify a column to a computed column in which the expression references an encrypted column.
- Encrypt a column that is a member of a functional index.
- Specify an encrypted column as a partition key.
- Modify for encryption a column that is used as a partition key.