Accessing encrypted data with user password

You must supply the encryption key’s password to encrypt or decrypt data on an insert, update, delete, select, alter table, or select into statement. If the system encryption password protects the encryption key, you need not supply the system encryption password because Adaptive Server can already access it. Similarly, if your key copy is encrypted with your login password, Adaptive Server can access this password while you remain logged in to the server (see “Application transparency using login passwords on key copies”). For keys encrypted with an explicit password, you must set the password in your session before executing any command that encrypts or decrypts an encrypted column with this syntax:

set encryption passwd 'password_phrase'
	for {key | column} {keyname | column_name}

where:

password_phrase – is the explicit password specified with the create encryption key or alter encryption key command to protect the key.
key – indicates that Adaptive Server uses this password to decrypt the key when accessing any column encrypted by the named key
keyname – may be supplied as a fully qualified name. For example:
```
[[database.][owner].]keyname
```
column – specifies that Adaptive Server use this password only in the context of encrypting or decrypting the named column. End users do not necessarily know the name of the key that encrypts a given column.
column_name – name of the column on which you are setting an encryption password. Supply column_name as:
```
[[ database.][ owner ]. ]table_name.column_name
```

Each user who requires access to a key encrypted by an explicit password must supply the password. Adaptive Server saves the password in encrypted form in the user session's internal context. Adaptive Server removes the key from memory at the end of the session by overwriting the memory with zeros.

This example illustrates how Adaptive Server determines the password when it must encrypt or decrypt data. It assumes that the ssn column in the employee and payroll tables is encrypted with key1, as shown in these simplified schema creation statements:

create encryption key key1 with passwd "Ynot387"
create table employee (ssn char (11) encrypt with key1, ename char(50))
create table payroll (ssn char(11) encrypt with key1, base_salary float)

The key custodian shares the password required to access employee.ssn with “susan”. He does not need to disclose the name of the key to do this.

If “susan” has select and decrypt permission on employee, she can select employee data using the password given to her for employee.ssn:

set encryption passwd "Ynot387" for column employee.ssn
     select ename from employee where ssn = '111-22-3456'

ename
-----------------------
Priscilla Kramnik

If “susan” attempts to select data from payroll without specifying the password for payroll.ssn, the following select fails (even if “susan” has select and decrypt permission on payroll):
```
select base_salary from payroll where ssn = '111-22-3456'
```
```
You cannot execute 'SELECT' command because the user encryption password
has not been set.
```
To avoid this error, “susan” must first enter:
```
set encryption passwd "Ynot387" for column payroll.ssn
```

The key custodian may choose to share passwords on a column-name basis and not on a key-name basis to avoid users hard-coding key names in application code, which can make it difficult for the database owner to change the keys used to encrypt the data. However, if one key is used to encrypt several columns, it may be convenient to enter the password once. For example:

set encryption passwd "Ynot387" for key key1
select base_salary from payroll p, employee e
     where p.ssn = e.ssn
        and e.ename = "Priscilla Kramnik"

If one key is used to encrypt several columns and the user is setting a password for the column, the user needs to set password for all the columns they want to process. For example:

set encryption passwd 'Ynot387' for column payroll.ssn
set encryption passwd 'Ynot387' for column employee.ssn
select base_salary from payroll  p, employee e
     where p.ssn = e.ssn
     and e.ename = 'Priscilla Kramnik'

If a password is set for a column and then set at the key level for the key that encrypts the column, Adaptive Server discards the password associated with the column and retains the password at the key level. If two successive entries for the same key or column are entered, Adaptive Server retains only the latest. For example:

If a user mistypes the password for the column employee.ssn as “Unot387” instead of the correct “Ynot387”:
```
set encryption passwd "Unot387" 
     for column employee.snn
```
And then the user reenters the correct password, Adaptive Server retains only the second entry:
```
set encryption passwd "Ynot387" 
     for column employee.ssn
```
If the user now enters the same password at the key level, Adaptive Server retains only this last entry:
```
set encryption passwd "Ynot387" for key key1
```
If the user now enters the same password at the column level, Adaptive Server discards this entry because it already has this password at the key level:
```
set encryption passwd "Ynot387" 
     for column payroll.ssn
```

If a stored procedure or a trigger references a column encrypted by a user specified password, you must set the encryption password before executing the procedure or the statement that fires the trigger.

Sybase recommends that you do not place the set encryption passwd statement inside a trigger or procedure; this could lead to unintentional exposure of the password through sp_helptext. Additionally, hard-coded passwords require you to change the procedure or trigger when a password is changed.