Users, roles, and data access

User-specified passwords on encryption keys ensure that data privacy is protected from the system administrator. Table 5-1 explains how:

Table 5-1: Permissions for users and roles on encrypted columns

Role

Can create encryption key?

Can use key in a schema definition?

Can decrypt encrypted data?

sso_role

Yes

No, requires create table permission

No. User with role may have knowledge of password, but requires select permission on table (SSO has implicit decrypt permission).

sa_role

No, requires create encryption key permission

Yes, but must be granted select permission on the key

No, requires knowledge of password

keycustodian_role

Yes

No, requires create table permission

No. User with role may have knowledge of password, but requires decrypt and select permission on table or column.

DBO or schema owner

No, requires create encryption key permission

Yes, but must be granted select permission on the key

No, requires knowledge of password.

User

No

No

Yes, but must be granted decrypt or select permission and have knowledge of key’s password.