You must grant decrypt permission on encrypted columns before users or roles can select or search on encrypted data in those columns. 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 plain text data is not displayed and is not used for searching.
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.
create table emp (name char(50), ssn (char(11) encrypt decrypt_default '000-00-000', ...) 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.
