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.
set encryption passwd 'password_phrase' for {key | column} {keyname | column_name}
password_phrase – is the explicit password specified with the create encryption key or alter encryption key command to protect the key.
key – indicates that SAP ASE uses this password to decrypt the key when accessing any column encrypted by the named key
[[database.][owner].]keyname
column – specifies that SAP ASE 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. SAP ASE saves the password in encrypted form in the user session's internal context. SAP ASE removes the key from memory at the end of the session by overwriting the memory with zeros.
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.
set encryption passwd "Ynot387" for column payroll.ssn
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"
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'
set encryption passwd "Unot387" for column employee.snn
set encryption passwd "Ynot387" for column employee.ssn
set encryption passwd "Ynot387" for key key1
set encryption passwd "Ynot387" for column payroll.ssn
If a stored procedure or a trigger references data encrypted by a user specified password, you must set the encryption password before executing the procedure or the statement that fires the trigger.