If you use a column with the decrypt default property in a where clause, the qualification fails 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.
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
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)
If you have the hr_role and include an or statement on a non-encrypted 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
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 fails but the qualification against the non-encrypted 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.
