Decrypt Default Columns and Query Qualifications

If you have the hr_role and issue the following query, SAP ASE returns one row.

select name from emp where ssn = '123-456-7890'

name
------------------------------ 
Joe Cool

If you do not have the hr_role, SAP ASE returns no rows:

select name from emp where ssn = '123-456-7890'

name
------------------------------ 
(0 rows affected)

If you have the hr_role and include an or statement on a nonencrypted column, SAP ASE returns the appropriate rows:

select name from emp where ssn = '123-456-7890' or
name like 'Tinna%'

name
------------------------------ 
Joe Cool
Tinna Salt

If you do not have the hr_role and issue the same command, SAP ASE returns only one row:

select name from emp where ssn = '123-456-7890' or name like 'Tinna%'

name
------------------------------ 
Tinna Salt

In this case, the qualification against the encrypted column with the decrypt default property evaluates to false, but the qualification against the nonencrypted column succeeds.

If you do not have decrypt permission on an encrypted column, and you issue a group by statement on this column with a decrypt default, SAP ASE groups by the decrypt default constant value.