Granting Decrypt Permission
Go Up to Using isql to Enable and Implement Encryption
After encrypting a column, the database owner or the individual table owner, grants decrypt permission to users who need to access the values in an encrypted column. Generally speaking, these are end users who already have, or who need to have, select, insert, update, and/or delete privileges on the same data. You can grant decrypt permission to individual users and to groups of users by role, view, trigger, and stored procedure.
To grant decrypt privileges to an individual user, use the following syntax:
grant decrypt[(column-name, …)] on table-name to user-name
If the database owner or the individual table owner has explicitly granted execute and select privileges to users on stored procedures and views, respectively, a chain of ownership implicitly grants decrypt privilege on any referenced encrypted columns in those schema elements owned by that schema owner.
Permissions for Roles and Views
When a number of users need to access the same encrypted columns, you can save time and effort by assigning the users to the same role, and granting decrypt permission to the role rather than to each individual user.
For example, suppose you have a table called “Employee” which contains columns that are used by people in the same department. You could create a role called “HR_Role,” assign individual HR employees to the role, and then grant decrypt privileges to the role. The code sample that follows shows you how to create users, assign them to a role, then provide decrypt privileges to the role:
CREATE USER J_Smith PASSWORD 'Smith'
CREATE USER J_Doe PASSWORD 'Doe'
CREATE USER B_Jones PASSWORD 'Jones'
CREATE ROLE HR_Role
GRANT HR_Role to J_Smith, J_Doe, B_Jones
GRANT DECRYPT (A) on Employee to HR_Role
GRANT DECRYPT (B) on Employee to HR_Role
After issuing these commands, all the members in the HR_role can use their role affiliation to decrypt columns A and B.
Similarly, you can give users access to a view that has decrypt access to encrypted columns. First you create the view:
CREATE VIEW Payroll_View as SELECT
C, D, E, I FROM Employee
Payroll_View now contains data from columns C, D, E, and I. Next, you can grant decrypt access to encrypted columns on Employee to view Payroll_View:
GRANT DECRYPT (C, D, E, I) ON Employee TO VIEW Payroll_View
Next, you can grant access to Payroll_View to individual users:
GRANT SELECT ON Payroll_View TO D_Gibson
or to all the users assigned to a role (after creating the role), as shown below:
GRANT SELECT ON Payroll_View TO Payroll_Role