Creating key copies

Key copies are made available to individual users as follows:

The key custodian uses create encryption key to create a key with a user-defined password. This key is known as the “base” key.

The key custodian uses alter encryption key to assign a copy of the base key to an individual user with an individual password. See “Application transparency using login passwords on key copies” for a description of for key copies accessed through a login password.

This syntax shows how to add a key for a designated user encrypted using an explicit password:
```
alter encryption key [ database_name.[ owner_name ].] key_name
       with passwd 'base_key_password'
       add encryption with passwd 'key_copy_password'
       for user ''
```

base_key_password – is the password used to encrypt the base key, and may be known only by the key custodian. The password cannot be longer than 255 bytes. Adaptive Server uses the first password to decrypt the base column-encryption key.
key_copy_password – the password used to encrypt the key copy. The password cannot be longer than 255 bytes. Adaptive Server makes a copy of the decrypted CEK, encrypts it with a KEK derived from the second key_copy_password, and saves the encrypted CEK copy as a new row in sysencryptkeys.
user_name identifies the user for whom the key copy is made. For a given key, sysencryptkeys includes a row for each user who has a copy of the key, identified by their user_id.
The key custodian adds as many key copies as there are users who require access through a private password.
Users can alter their copy of the encryption key to encrypt it with a different password.

The following example illustrates how to set up and use key copies with an encrypted column:

Key custodian Razi creates the base encryption key with a user-specified password:
```
create encryption key key1 with passwd 'WorldsBiggestSecret'
```
Razi grants select permission on key1 to DBO for schema creation:
```
grant select on key key1 to dbo
```

DBO creates schema and grants table and column-level access to Bill:

create table employee (empname char(50), emp_salary money encrypt with razi.key1, emp_address varchar(200))
grant select on employee to bill
grant decrypt on employee(emp_salary) to bill

Key custodian creates a key copy for Bill and gives Bill the password to his key copy. Only the key custodian and Bill know this password.
```
alter encryption key key1 with passwd 'WorldsBiggestSecret' 
add encryption with passwd 'justforBill' 
for user 'bill'
```

When Bill accesses employee.emp_salary, he first supplies his password:

set encryption passwd 'justforBill' for key Razi.key1
	select empname, emp_salary from dbo.employee

When Adaptive Server accesses the key for the user, it looks up that user’s key copy. If no copy exists for a given user, Adaptive Server assumes the user intends to access the base key.