Application transparency using login passwords on key copies

The key custodian can set up key copies for encryption with a user’s login password, and thereby provide:

Ease of use – users whose login password is associated with a key can access encrypted data without supplying a password.
Better security – users have fewer passwords to track, and are less likely to write them down.
Lower administrative overhead for key custodian – the key custodian need not manually distribute temporary passwords to each user who requires key access through a private password.
Application transparency – applications need not prompt for a password to process encrypted data. Existing applications can take advantage of the measures to protect data privacy from the power of the administrator.

To encrypt a key copy with a user’s login password, use:

alter encryption key [[database.][owner].]keyname 
	with passwd 'base_key_password' 
	add encryption for user 'user_name' for login_association

where login_association tells Adaptive Server to create a key copy for the named user, which it later encrypts with the user's login password. Encrypting a key copy with a login password requires:

Using alter encryption key, the key custodian creates a key copy for each user who requires key access via a login password. Adaptive Server attaches information to the key copy to securely associate the key copy with a given user. The identifying information and key are temporarily encrypted using a key derived from the master key or—if no master key exists—the system encryption password. The key copy is saved in sysencryptkeys.
When a user processes a column requiring a key lookup, Adaptive Server notes that a copy of the encryption key identified for this user is ready for login password association. Using the master key or the system encryption password to decrypt the information in the key copy, Adaptive Server validates the user information associated with the key copy against the user’s login credentials, and encrypts the key copy with a KEK derived from the user’s login password, which has been supplied to the session.

When adding a key copy with alter encryption key key for login_association, the master key or the system encryption password must be available for encryption of the key copy. The system encryption password must still be available for Adaptive Server to decrypt the key copy when the user logs in. After Adaptive Server has reencrypted the key copy with the user’s login password, the system encryption password is no longer required.

The following example encrypts a user’s copy of the encryption key, key1, with the user’s login password:

Key custodian “razi” creates an encryption key:

create encryption key key1 for AES 
     with passwd 'MotherofSecrets'

“razi” creates a copy of key1 for user “bill”, initially encrypted with the master key or the system encryption password, but eventually to be encrypted by “bill”’s login password:
```
alter encryption key key1 with
     passwd 'MotherofSecrets'
     add encryption
     for user 'bill'
     for login_association
```
Adaptive Server uses the master key or the system encryption password to encrypt a combination of the key and information identifying the key copy for “bill”, and stores the result in sysencryptkeys.
“bill” logs in to Adaptive Server and processes data, requiring the use of key1. For example, if emp.ssn is encrypted by key1:
```
select * from emp
```
Adaptive Server recognizes that it must encrypt “bill”’s copy of key1 with his login password. Adaptive Server uses the master key or the system encryption password to decrypt the key value data saved in step 4. It validates the information against the current login credentials, then encrypts key1's key value with a KEK generated from “bill”’s login password.
During future logins when “bill” processes columns encrypted by key1, Adaptive Server accesses key1 directly by decrypting it with “bill”’s login password, which is available to Adaptive Server through “bill”’s internal session context.

Users who are aliased to “bill” cannot access the data encrypted by key1 because their own login passwords cannot decrypt key1.
When “bill” loses authority to process confidential data, the key custodian drops “bill”’s access to the key:
```
alter encryption key key1
     drop encryption
     for user 'bill'
```

A user can encrypt a key copy directly with a login password with alter encryption key using the with passwd login_passwd clause. However, the disadvantages of using this method over the login association are:

The key custodian must communicate the key copy’s first assigned password to the user.
The user must issue alter encryption key to reencrypt the key copy with a login password.

For example:

“razi” adds a key copy for user “bill” encrypted by an explicit password:

alter encryption key key1 
     with passwd 'MotherofSecrets' 
add encryption with passwd 'just4bill' 
     for user bill

“razi” shares the key copy's password with “bill”.

“bill” decides to encrypt his key copy with his login password for his own convenience:

alter encryption key key1 with passwd "just4bill" modify encryption with passwd login_passwd

Now, when “bill” processes encrypted columns, Adaptive Server accesses “bill”’s key copy through his login password.