Permissions for Roles and Views

Go Up to Granting Decrypt Permission

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