sp_encryption

Reports encryption information.

Syntax

sp_encryption help | helpkey
sp_encryption help | helpkey [, key_name | wildcard]
	[, all_dbs | key_copy | display_cols]
sp_encryption help | 'helpkey', 
	{'master'|'dual master'} [, 'display_keys' | 'all_dbs'
sp_encryption 'help'[, 'servicekeyname '[, 'display_objs']]
sp_encryption 'helpextpasswd'
sp_encryption helpcol [, table_name | column_name ]
sp_encryption helpuser [, user_name | wildcard ][, key_copy | login_passwd_check ]
sp_encryption 'mkey_startup_file'[, {'new_path' | 'default_location' | 'null'} 
	[, {sync_with_mem | sync_with_qrm}]]
sp_encryption 'downgrade_kek_size' [, 'true'|'false']
sp_encryption system_encr_passwd, 'newpasswd' [,'oldpasswd']

Parameters

Examples

Usage

  • When a database is fully encrypted, sp_encryption reports a key type called "database encryption key".
  • The privileges granted to the user who runs sp_encryption determines the output. See for more information.

  • If you run sp_encryption helpkey and no keys are present in the database, you see an informational message.

  • You must specify the key_copy parameter to get information about key copies. If you do not specify the key_copy parameter, sp_encryption returns information only about base keys.

  • If keyname is NULL in sp_encryption helpkey, keyname, key_copy, lists all the key copies in the current database for a SSO, key custodian, or DBO. If it is run by a user without privileges, it lists all the key copies assigned to the user in the current database and all key copies of the keys owned by the user in the current database.

  • For sp_encryption helpcol, column_name uses the form name.name.name, where:
    • name – if sp_encryption finds no tables of this name, it looks for all columns of that name.

    • name.name – is owner.table. If sp_encryption finds no tables of this name, it looks for a single column named table.column.

    • name.name.name – is owner.table.name.

    For all columns identified by these rules in the current database, sp_encryption displays column name along with the key used to encrypt the column.

    The output for sp_encryption helpcol, column_name is owner.table.column and db.owner.keyname. The keyname is expressed as database.keyid when run by non-SSO users, and the key is present in a different database from the encrypted column. The result set is sorted by owner.table.column.

The restrictions for sp_encryption are:
  • Only an SSO can run sp_encryption helpkey [, keyname | wildcard], all_dbs to get the properties of keys in all databases. If a user without the sso_role runs this command, they receive an “unauthorized user” error message. If no keys qualify the keyname or wildcard, the SAP ASE server returns a message stating 'There are no encryption keys (key copies) like keyname in all databases'.

  • When the SSO runs sp_encryption helpkey, keyname, display_cols, it lists all columns across all available databases encrypted by keyname. If it is run by a user without privileges, it lists the columns in the current database encrypted by keyname.

    If the SSO runs sp_encryption helpkey, keyname, display_cols and the keyname value is NULL, it displays all encrypted columns across all available databases. When run by a user without privileges, it displays all encrypted columns in the current database.

  • If an SSO, key custodian, or DBO runs sp_encryption helpuser, user_name, key_copy without specifying a user_name and key_copy for the helpuser parameter, it lists all the base keys owned by all users in the current database. If sp_encryption is run by a user without privileges without specifying a user_name or key_copy, it displays the base keys owned by the current user.

    If any user runs sp_encryption helpuser, user_name, it lists all the base keys owned by owner.keyname. If a user without privileges runs the command and owns no base keys, the SAP ASE server displays an informational message stating this.

    If an SSO, key custodian, or DBO runs sp_encryption helpuser, user_name, key_copy, it lists the key copies assigned to user_name. If a user without privileges issues this command, its lists the key copies assigned to this user and all the key copies of the keys owned by the user in the current database, with these columns in the result set: Owner.Keyname, Assignee, Type of Password, and Key Recovery. The output is sorted by Assignee.

    If user_name is NULL for sp_encryption helpuser user_name, key_copy, it lists all the key copies in the current database for a SSO, key custodian, or DBO. For users without privileges, it lists all the key copies assigned to the user in the current database and the key copies for the keys owned by this user.

  • When a SSO, key custodian, or DBO runs sp_encryption helpkey, keyname, key_copy, it lists the key copies in the current database for keyname. If this is run by a user without privileges, it lists the key copies assigned to the user for that keyname and the key copies for that keyname if the user is the key owner.

  • The SSO, key custodian, and DBO can run sp_encryption helpkey, system_encr_passwd, display_keys to receive information on all keys and key copies in the current database encrypted by system encryption password. Users without privileges receive information about the base encryption keys or key copies they own or are assigned in the current database. Key copies are encrypted with the system encryption password only when they are created for login association. The output is sorted by owner.keyname.

Permissions

The permission checks for sp_encryption differ based on your granular permissions settings.

SettingDescription
Enabled
With granular permissions enabled:
  • downgrade_kek_size – You must be a user with manage security configuration privilege.

  • help/help_key system_encr_passwd, display_keys – You must be a user with manage column encryption key privilege. Any user can see their own key.

  • help/help_key system_encr_passwd – You must be a user with manage column encryption key privilege.

  • help/help_key master key/ dual master key, display_keys – You must be a user manage master key privilege.

  • help/help_key keyname/wild card, display_cols – You must be a user with use any database privilege for cross database check. Any user for the current database.

  • help/help_key service keyname, display_objs –You must be a user with manage service key privilege.

  • help/help_key keyname/wild card, all_dbs – You must be a user with the following privilege depending the key type:

    • column encryption key – manage column encryption key

    • master key – manage master key

    • service key – manage service key

  • For cross-database checks, one of the above three, and use any database permission.

  • help/help_key keyname wildcard – You must be a user with the following privilege depending the key type:

    • column encryption key – manage column encryption key

    • master key – manage master key

    • service key – manage service key

    For non-privileged users, limited encyption key information is displayed.
  • help/help_key keyname wildcard, key_copy – You must a user with the following privilege depending on the key type:
    • column encryption key – manage column encryption key

    • master key – manage master key

  • helpcol – You must be a user with use any database privilege for cross database checks.

  • helpextpassword – You must be a user with manage service key privilege.

  • helpuser username/wildcard, [key_copy/login_passwd_check] – You must be a user with manage any encryption key privilege. Non-privilege users can see their own key.

  • mkey_startup_file – You must be a user with with manage security configuration privilege.

  • system_encr_passwd – You must be a user with manage column encryption key privilege.

  • verify_downgrade – You must be a user with manage security configuration privilege.

Disabled
With granular permissions disabled:
  • downgrade_kek_size – You must be a user with sso_role or keycustdian_role.

  • help/help_key system_encr_passwd, display_keys – You must be the database owner, a user with sso_role, or a user with keycustdian_role. Any user can see their own key.

  • help/help_key system_encr_passwd – You must be the database owner, a user with sso_role, or a user with keycustdian_role.

  • help/help_key master key/ dual master key, display_keys – You must be the database owner, a user with sso_role, or a user with keycustdian_role.

  • help/help_key keyname/wild card, display_cols – You must be a user with sso_role for cross database check. Any user for the current database.

  • help/help_key service keyname, display_objs – You must be a user with sso_role or a user with keycustodian_role.

  • help/help_key keyname/wild card, all_dbs – You must be a user with sso_role.

  • help/help_key keyname wildcard – You must be the database owner, a user with sso_role, or a user with keycustdian_role.

  • help/help_key keyname wildcard, key_copy – You must be the database owner, a user with sso_role, or a user with keycustdian_role.

  • For:
    • Non-privileged users – displays only key_copy information

    • For privileged users – displays the encryption key and key_copy information for all users in the database

  • helpcol – You must be a user with sso_role.

  • helpextpassword – You must be a user with sso_role.

  • helpuser username/wildcard, [key_copy/login_passwd_check] – You must be the database owner, a user with sso_role, or a user with keycustdian_role. Non-privilege users can see their own keys.

  • mkey_startup_file – You must be a user with sso_role.

  • system_encr_passwd – You must be a user with sso_role or keycustdian_role.

  • verify_downgrade – You must be a user with sso_role or keycustdian_role.