Application transparency using login passwords on key copies

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

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 administration 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 data privacy improvements in Adaptive Server version 15.0.2.

Use this syntax to encrypt a key copy with a user’s login password:

alter encryption key [database_name.[owner_name].]key_name 
with password '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 two-steps.

Using the alter encryption key syntax above, 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 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 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 was supplied to the session.

When adding a key copy with alter encryption key, Adaptive Server initially encrypts the key copy with the system encryption password. The system encryption password must 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'

If there is not already a system encryption password, Razi sets one:
```
sp_encryption system_encr_passwd, 'keepitsecret'
```
Razi creates a copy of key1 for user Bill, initially encrypted with 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 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 system encryption password to decrypt the key value data saved in step 3. 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.

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 with a login password with alter encryption key. However, the disadvantages of using this method over the login association are:

The key custodian must communicate the key copies 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. He uses the command:
```
alter encryption key k1 with passwd "just4bill" modify encryption with passwd login_passwd
```
Nw, when “bill” processes encrypted columns, Adaptive Server accesses “bill’s” key copy through his login password.