Decrypt permission

Users must have decrypt permission to select plain text data from an encrypted column, or to search or join on an encrypted column.

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 or user-defined function that selects from an encrypted column where the owner of the procedure or function 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
[with grant option]

Granting decrypt permission at the table or view 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