Adaptive Server version 15.0.2 enhances data privacy from the administrator even if you continue using the system encryption password for key protection. If you prefer to avoid password management and continue to use the system encryption password to protect encryption keys, you can restrict access to private data from the DBO by setting the restricted decrypt permission configuration parameter. This parameter grants the system security officer (SSO) control over which users have decrypt permissions. Once restricted decrypt permission is enabled, the SSO is the only user who receives implicit decrypt permission and who has implicit privilege to grant that permission to others. The SSO determines which users receive decrypt permission, or delegates this job to another user by granting decrypt permission with the with grant option. Table owners do not automatically have decrypt permission on their tables.
Users with execute permission on stored procedures or user-defined functions do not have implicit permission to decrypt data selected by the procedure or function. Users with decrypt permission on a view column do not have implicit permission to decrypt data selected by the view.
Summary information |
|
|---|---|
Default value |
0 (off) |
Range of values |
0 (off), 1 (on) |
Status |
Dynamic |
Display level |
Basic |
Required role |
System Security Officer |
When run by the SSO, the following command enables or disables restricted decrypt permission in all databases:
sp_configure "restricted decrypt permission", [1 | 0]
When restricted decrypt permission is set to 0 (off), decrypt permission on encrypted columns acts the same as in earlier versions:
The table owner or the SSO explicitly grants decrypt permission. However, with grant option on decrypt permission is supported.
Decrypt permission is granted implicitly to table owners and the SSO.
Decrypt permission is granted implicitly to any user through a chain of ownership. For example, if user Fred owns the proc1 stored procedure, which selects data from the encrypted column fred.table1.col1, and if Fred grants exec permission on proc1 to Harry, then Harry has implicit decrypt permission on fred.table1.col1.
Decrypt permission is not needed for alter table decrypt. because the table owner has implicit decrypt permission on encrypted columns.
When restricted decrypt permission is set to 1 (on):
Decrypt permission is granted implicitly to the SSO only.
The SSO can grant decrypt permission using the with grant option parameter. This allows the SSO to decide who should grant decrypt permission in the system. For example, if the SSO wants user1 to be able to grant decrypt permission on user3.user3_tab, he or she issues:
grant decrypt on user3.user3_tab to user1 with grant option
If you use a system encryption password, Sybase recommends that you do not grant decrypt permission to the DBO to protect data’s privacy from the administrator. In a system that protects keys through user encryption passwords, you do not threaten the privacy of data by granting decrypt permission to the DBO with the with grant option. Access to keys through user passwords prevents the DBO and other parties from accessing the data unless they have a key’s password; however, you may find it convenient for the DBO to decide which users should see the decrypted data. If you are not protecting keys and data with user-specified passwords, the System Security Officer should retain the sole responsibility to grant decrypt permission.
Table ownership does not give a user implicit decrypt permission. That is, if you create a table with encrypted columns, you do not have decrypt permission on them unless this is explicitly granted to you.
No user is implicitly granted decrypt permission through an ownership chain. For example, if user Fred owns the proc1 stored procedure, which selects data from the encrypted column fred.table1.col1, and if Fred grants exec permission on proc1 to Harry, then Harry must also have explicit decrypt permission on fred.table1.col1 to see the data.
Aliased users assume the permissions of the user to whom they are aliased. Similarly, a user with sa_role, who is implicitly aliased to the DBO in any database, inherits any decrypt permissions explicitly granted to the DBO.
Decrypt permission is required for alter table decrypt statement because the table owner does not have implicit decrypt permission on the table.
If you change restricted decrypt permission from 0 to 1, currently executing statements that use implicit decrypt permission complete. Any subsequent statements that use implicit decrypt permission fail with this error message until the SSO grants the user decrypt permission on the necessary columns:
Msg 10330 "DECRYPT permission denied on object object_name, database database_name, owner owner_name."
If you change restricted decrypt permission from 1 to 0, the rows that reflect explicit grants remain in sysprotects. However, these rows have no effect on implicitly granted decrypt permissions because Adaptive Server does not check sysprotects to make sure decrypt permission can be implicitly granted. sp_helprotect displays misleading information for only those users who were granted or revoked explicit decrypt permission before the system was re-configured and who now have implicit decrypt permission.
Sybase recommends that you revoke any explicit decrypt permissions granted to users before you switch between enabling or disabling restricted decrypt permission to keep the system consistent.
