You must grant decrypt permission on encrypted columns before users or roles can select or search on encrypted data they contain. If an encrypted column has a decrypt default attribute, users without decrypt permission can run queries that select or search on these columns, but the cleartext data does not display. (Adaptive Server does not issue the 10330 error message and does not decrypt the data).
In this example, the owner of table emp allows users with the hr_role to view emp.ssn. Because the ssn column has a decrypt default, users who have only select permission on emp and who do not have the hr_role see the decrypt_default value only and not the actual decrypted data.
grant select permission on table emp to public grant decrypt on emp(ssn) to hr_role
If you have the hr_role and select from this table, you see the values for ssn:
select name, ssn from emp
name ssn ------------------------------ ------------ Joe Cool 123-45-6789 Tinna Salt 321-54-9879
If you do not have the hr_role and select from the table, you see the decrypt default:
select name, ssn from emp
name ssn ------------------------------ ----------- Joe Cool 000-00-0000 Tinna Salt 000-00-0000
order by clauses have no effect on the result set if you do not have the hr_role for this table.
