You must have these two permissions to select plaintext data from an encrypted column or to search or join on an encrypted column:
select permission on the column
decrypt permission on the column used in the target list and in where, having, order by, update, and other such clauses
The table owner uses grant decrypt to grant explicit permission to decrypt one or more columns in a table to other users, groups, and roles. Decrypt permission may be implicitly granted when a procedure or view owner grants:
exec permission on a stored procedure that selects from an encrypted column where the owner of the procedure also owns the table containing the encrypted column
decrypt permission on a view column that selects from an encrypted column where the owner of the view also owns the table
In both cases, decrypt permission need not be granted on the encrypted column in the base table.
The syntax is:
grant decrypt on [ owner.] table[( column[{,column}])] to user
| group | role
Granting decrypt permission at the table level grants decrypt permission on all encrypted columns in the table.
To grant decrypt permission on all encrypted columns in the customer table, enter:
grant decrypt on customer to accounts_role
The following example shows the implicit decrypt permission of user2 on the ssn column of the base table “employee”. user1 sets up the employee table and the employee_view as follows:
create table employee (ssn varchar(12)encrypt, dept_id int, start_date date, salary money) create view emp_salary as select ssn, salary from employee grant select, decrypt on emp_salary to user2
user2 has access to decrypted Social Security Numbers when selecting from the emp_salary view:
select * from emp_salary
grant all on a table or view does
not grant decrypt permission.
