The key custodian may need to make a copy of the key temporarily available to an administrator or an operator who must load data into encrypted columns. Because this operator does not otherwise have permission to access encrypted data, he should not have permanent access to a key.
You can make key copies 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.
This syntax shows how to add a key encrypted using an explicit password for a designated user:
alter encryption key [database.[ owner ].]key with passwd 'base_key_password' add encryption with passwd 'key_copy_password' for user_name ''
where:
base_key_password – is the password used to encrypt the base key, and may be known only by the key custodian. The password can be upto 255 bytes in length. 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 base key, encrypts it with a key encryption key derived from the key_copy_password, and saves the encrypted base key 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 (uid).
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 database owner for schema creation:
grant select on key key1 to dbo
database owner 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.
