Decrypt default columns and query qualifications

If you use a column with the decrypt default property in a where clause, the qualification evaluates to false if you do not have decrypt permission. These examples use the emp table described above. Only users with the hr_role have decrypt permission on ssn.

1. If you have the hr_role and issue the following query, Adaptive Server returns one row.

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

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

2. If you do not have the hr_role, Adaptive Server returns no rows:

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

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

3. If you have the hr_role and include an or statement on a nonencrypted column, Adaptive Server returns the appropriate rows:

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

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

4. If you do not have the hr_role and issue the same command, Adaptive Server 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, Adaptive Server groups by the decrypt default constant value.