sp_encryption

Description

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]
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

helpkey

lists encryption key properties, including:

  • Whether the database contains encryption keys.

  • The following, when run by a user with sso_role, key custodian, or DBO: keyname, keyowner, key length, key algorithm, key type, pad, initialization vector, type of password used to encrypt the key, whether key recovery has been enabled and count of key copies. The output is sorted on owner.keyname. When run by a non-privileged user, this command will list keyname, keyowner and keytype.

help

included for backward compatibility. Includes the same output as helpkey

key_name

name of the key you are investigating. Lists the properties defined for key_name. If key_name is omitted, lists properties for all keys.

wildcard

lists the properties for keys matching the wildcard pattern in the current database. See the Reference Manual: Building Blocks for information about using wildcards.

all_dbs

lists information on encryption keys in all available databases. Only the SSO can run all_dbs.

key_copy

lists all user copies for the specified key in the current database. The output is sorted by key_owner.key_name. Includes information about:

  • The base key owner.

  • If the key copy is a recovery key copy.

  • The user to whom a copy belongs.

  • If the copy is encrypted with a user-encryption password, a login password, or the system encryption password for login association (indicated by Login Access).

display_keys

used with system_encr_passwd to display the keys and key copies that are encrypted using the system encryption password. Used with master or dual master to display keys and key copies encrypted using the master key or the dual master key.

You must be the system security officer, key custodian, or the database owner can run sp_encryption helpkey, master | 'dual master', display_keys to display encryption keys protected by the master or dual master key.

display_cols

displays the key name, all keys (or matching wildcard keys) in the current database and the columns the key encrypts. When SSO includes display_cols, it displays columns encrypted by the keys across all available databases. When a user without the sso_role runs display_cols, only those columns encrypted by the key in the current database are displayed. Data is sorted by key_name, key_owner, database_name, table_owner, table_name, and column_name.

master

reports information about the master key.

dual master

reports information about the dual master key.

servicekeyname

set to syb_extpasswdkey or syb_syscommkey%. Use with display_objs to display objects encrypted by the service key.

display_objs

displays object owners.

You must be the system security officer, key custodian, or the database to run sp_encryption helpkey, keyname, display_objs to display objects in current database protected by the syb_extpasswdkey or syb_syscommkey service keys.

helpextpasswd

displays the encryption status of external passwords in the status column. The encryption status is one of:

  • FIPS Encryption – the password is protected by the syb_extpasswdkey service key using a FIPS compliant cryptography algorithm

  • Needs Reset – indicates the system removed the password, and you must manually reset it.

  • Legacy Encryption – the password is protected with an algorithm from a version of Adaptive Server earlier than 15.7.

You must be the system security officer to run sp_encryption helpextpasswd to check the status of external passwords.

helpcol column_name

displays the column name and the key used to encrypt the column. If the SSO includes helpcol, it prints the key name even if the key is not present in the current database. If a non-SSO user includes helpcol, Adaptive Server prints the keyid of the key if it is not present in the current database, omitting the key_name. The output includes: owner.table.column, database.owner.keyname. The information is sorted by owner.table.column.

helpuser

displays the keys owned by or assigned to a user in the current database.

mkey_startup_file

displays or sets the master key startup file name and path. sp_encryption sets the master key startup file to new_path or the default location. If you specify null, or no location, sp_encryption displays the current master key startup file name and path.

sync_with_mem

(Available only on Cluster Edition) writes the master key encryption key that exists in server memory to the master key startup file. Replaces the current master key encryption key, if it exists. If automatic master key access is set to off, sync_with_mem is also disabled.

You must be the system security officer display, set, or sync the master key startup file.

sync_with_qrm

updates the local master key startup file with the version in the quorum device.

You must be the system security officer display, set, or sync the master key startup file.

downgrade_kek_size

displays or sets the downgrade kek size configuration. true indicates that Adaptive Server is in downgrade kek size mode, false disables this mode.

If you specify no argument, sp_encryption displays the current value for downgrade_kek_size.

You must be the system security officer or the key custodian to run this command.

system_encr_passwd

displays the keys and key copies encrypted using the system encryption password in the current database.

system_encr_passwd, all_dbs

displays the properties of the system encryption password in every database where it has been set. The output is sorted by database name. Only the system security officer an run this command. If the system encryption password has not been set for all databases, Adaptive Server generates Message 19782:

The system encryption password has not been set for all
available databases

Examples

Example 1

Use the helpkey parameter to display key information in the current database. You can get information on all keys or specific keys. The second parameter to sp_encryption supplies the key name and may include SQL pattern-matching characters. If you are not the database owner and do not have sso_role or keycustodian_role, sp_encryption displays fewer columns.

This displays properties of all base encryption keys in the current database when run by the SSO, key custodian, or the DBO:

sp_encryption helpkey
Key Name    Key Owner  Key Length  Key Algorithm  Key Type               Pad
     Init Vector  Protected By    Key Recovery  # of Key Copies
----------  ---------  ----------  --------------  --------              ----
     -----------  --------------------------    ------------  ---------------
tinnap_key  tinnap     128         AES            symmetric key          0
     1            system encryption password               0                0
tinnap_key1 tinnap     128         AES            symmetric default key  0
     1            user Passwd                              1                3
sample_key1 dbo        192         AES            symmetric key          1
     1            login Passwd                             1                2

When run by user “tinnap,” this displays the following properties of all base encryption keys in the current database:

sp_encryption helpkey
Key Name          Key Owner   Key Type
--------------     ---------   ------------
tinnap_key         tinnap      symmetric key
tinnap_key1        tinnap      symmetric default key
sample_key1        dbo         symmetric key

If you are not the system security officer, or do have keycustodian_role, the query displays all base keys you own in the current database. If you do not specify a user_name as the second parameter, the query displays the base keys you own.

Example 2

Displays properties of all base encryption keys with names similar to “tinnap%” in the current database when run by SSO, key custodian, or DBO:

sp_encryption helpkey, "tinnap%"
Key Name  Key Owner  Key Length   Key Algorithm            Key Type
   Pad  Init Vector   Protected By         Key Recovery     # of Key Copies
---------  --------    ---------  --------------  -----------------------
   ---  -----------   -------------------  ------------    ---------------
tinnap_key   tinnap          128             AES   symmetric key
     0            1   system encr passwd              0                  0
tinnap_key1  tinnap           128             AES   symmetric default key	     0            1          user passwd              1                  3

When run by user “tinnap,” displays the following properties for the base encryption keys in the current database with names similar to “tinnap%”:

sp_encryption helpkey, "tinnap%"
Key Name           Key Owner  Key Type
------------------  ---------  ------------
tinnap_key          tinnap     symmetric key
tinnap_key1         tinnap     symmetric default key

Example 3

Displays the properties of base encryption key sample_key1 when run by the SSO, key custodian, or DBO in the current database:

sp_encryption helpkey, sample_key1
Key Name   Key Owner  Key Length  Key Algorithm          Key Type
   Pad  Init Vector   Protected By      Key Recovery     # of Key Copies
---------  --------    ---------  --------------         --------------
   ---  -----------   ------------      -------------    ---------------
sample_key1     dbo          192             AES            symmetric Key
     1            1           Login                  1                   2

When non-privileged user “tinnap” runs this command, it displays the following properties for the base encryption key sample_key1 in the current database:

sp_encryption helpkey, sample_key1
Key Name       Key Owner    Key Type
-------------  -----------  ------------
sample_key1            dbo  ymmetric key

Example 4

Displays the properties of all base encryption keys in all available databases (only the SSO can run this command):

sp_encryption helpkey, NULL, all_dbs
Db.Owner.Keyname         Key Length   Key Algorithm  Key Type
Pad	   Init Vector   Protected By       Key Recovery    #of Key Copies
------------------------   ----------   -----------  --------------------
-----  ----------   -----------------    --------------  ----------------
keydb.dbo.cc_key           256          AES         symmetric default key
    1           1   system encr passwd                0              0
keydb.dbo.sample_key1      128          AES         symmetric key
    0           0   system encr passwd                1              4
keydb1.tinnap.tinnap_key   128          AES         symmetric key
    0           1   system encr passwd                0              0
keydb1.tinnap.tinnap_key1  128          AES         symmetric default key
    0           1        user password                1              3
keydb1.dbo.sample_key1     192          AES         symmetric key
    1           1         login passwd                1              2

Example 5

all_dbs indicates that information on keys across all databases is required. You must have sso_role to use the all_dbs parameter.

Displays the properties of all base encryption keys similar to %key in all available databases:

sp_encryption helpkey, '%key', all_dbs
Db.Owner.Keyname         Key Length   Key Algorithm  Key Type
Pad   Init Vector   Protected By       Key Recovery    #of Key Copies
------------------------   ----------   -----------  --------------------
-----  ----------   ---------------    --------------  ----------------
keydb.dbo.cc_key                  256      AES       symmetric default key
    1           1            system encr passwd     0               0
keydb1.tinnap.tinnap_key          128      AES              symmetric key
    0           1             system encr passwd    0               0

Example 6

Displays information on key copies using key_copy as the third parameter. Enter null instead of value for keyname for the second parameter to see information on all key copies. You can use pattern-matching characters in keyname (see example 2):

sp_encryption helpkey, tinnap_key1, key_copy
Owner.Keyname         Assignee       Protected by       Key Recovery
------------------     ---------       ----------------       ----------
tinnap.tinnap_key1     joesmp          user passwd            0
tinnap.tinnap_key1     samcool         user passwd            1
tinnap.tinnap_key1     billyg          user passwd            0

When run by user “joesmp,” this displays all encryption key copies assigned to user “joesmp” and also all the key copies for that keyname if the user is the owner of the key in the current database:

sp_encryption helpkey, tinnap_key1, key_copy
Owner.Keyname         Assignee       Protected by          Key Recovery
------------------     ---------       ----------------      ------------
tinnap.tinnap_key1        joesmp             user passwd                 0

Example 7

Use the display_cols parameter to show all encrypted columns in all available databases encrypted by keys from the current database. If you do not have the sso_role, the query displays only the encrypted columns in the current database encrypted by keys from the current database. You can use pattern matching characters or key_name for the second parameter. If you use pattern matching characters for key_name as sso_role, the query displays all encrypted columns in all available databases encrypted by the pattern matching key_name. If you use key_name for the second parameter and have the sso_role, displays all encrypted columns in all available databases encrypted by the specified key_name:

sp_encryption helpkey, null, display_cols
Key Name     Key Owner  Database Name  Table Owner  Table Name  Column Name
----------   ---------  -------------  ----------   ----------  ----------
tinnap_key      tinnap        testdb1      tinnap           t3         c3
tinnap_key1     tinnap        testdb1      tinnap           t4         c4
sample_key1        dbo          coldb         dbo           t1         c1
sample_key1        dbo          coldb      billyg            t2        c2

Example 8

Displays all keys and key copies encrypted with the system encryption password in the current database. If you do not have these privileges, the query displays the keys owned by or assigned to the user which are encrypted with the system encryption password:

sp_encryption helpkey, system_encr_passwd, display_keys
Owner.Keyname                   Assignee
---------------                  -------------
dbo.cc_key                                NULL
dbo.sample_key1                           NULL
dbo.sample_key1                         tinnap

Example 9

When run by the database owner or a user with keycustodian_role or sso_role, the helpuser parameter displays all base keys owned by users in the current database.

sp_encryption helpuser
Owner.Keyname             Protected by
---------------              -------------------tinnap.tinnap_key           system encr passwd
tinnap.tinnap_key1                 user passwd
dbo.sample_key1                    login passwd

If user “tinnap” runs this command, lists all base keys owned by this user in the current database:

sp_encryption helpuser
Owner.Keyname             Protected by
---------------              -------------------tinnap.tinnap_key           system encr passwd
tinnap.tinnap_key1           user passwd

Example 10

The database owner or a user with keycustodian_role or sso_role can use the key_copy parameter with the helpuser parameter to display key copies assigned to one or more users in the current database. You can use pattern-matching characters for the user parameter. This shows the key copies of all users in the current database:

sp_encryption helpuser, NULL, key_copy
Owner.Keyname            Assignee          Protected by      Key Recovery
---------------------      -----------      ----------------  -----------
dbo.sample_key1                 tinnap          login passwd           0
tinnap.tinnap_key1              joesmp           user passwd           0
dbo.sample_key1                 joesmp          login passwd           1
tinnap.tinnap_key1             samcool           user passwd           1
tinnap.tinnap_key1              billyg            user passwd          0

If you are not the database owner and do not have keycustodian_role or sso_role, this query displays the copies of any keys you own and the key copies that other key owners have assigned to you. For example, when user “tinnap” runs this query:

sp_encryption helpuser, NULL, “key_copy”
Owner.Keyname            Assignee         Protected by       Key Recovery
---------------------     -----------      ----------------   ---------
dbo.sample_key1                tinnap          login passwd           0
tinnap.tinnap_key1             joesmp           user passwd           0
tinnap.tinnap_key1            samcool           user passwd           1
tinnap.tinnap_key1              billyg            user passwd         0

Example 11

If you are the database owner or a user with keycustodian_role or sso_role, helpcol displays all encrypted columns in the current database and the keys used to encrypt the columns. If you do not have these privileges, helpcol displays keyid instead of the key_name if the encryption key is in a different database:

sp_encryption helpcol
Owner.Table.Column           Db.Owner.Keyname
-----------------------       ---------------------
dbo.t1.c1                     keydb1.dbo.sample_key1
billyg.t2.c2                  keydb.dbo.sample_key1
tinnap.t3.c3                  coldb.dbo.sample_key2

Example 12

Include the helpcol parameter with the table_name and column_name parameters to display all encrypted columns or a specific encrypted column in a given table. When run by a user with sso_role, the query below displays all encrypted columns in table t3 in the current database and the keys used to encrypt the columns across all available databases. When run by a user without sso_role, this query displays the key’s ID instead of its name if the key is not in the current database. The second parameter can have a combination of [database_name.][table_name.][column_name]:

sp_encryption helpcol, t3
Owner.Table.Column           Db.Owner.Keyname
-----------------------      ---------------------
tinnap.t3.c3                  coldb.dbo.sample_key2

Example 13

Displays the system encryption password properties each database (you must have sso_role to run this query):

sp_encryption helpkey, system_encr_passwd, all_dbs
Database  Type of system_encr_passwd   Last modified by            Date
--------  --------------------------    ---------------   ----------------
master                    persistent                sa  Aug 26 2008 10:05AM

Example 14

Displays all encryption keys encrypted with the master key in the current database (you must have sso_role, keycustodian_role, or be the database owner to run this query):

sp_encryption helpkey,'master',display_keys
Owner.Keyname      Assignee   
---------------- ---------- 
user1.key_dual         NULL
user1.key_mst          NULL
user4.key_dC_pwd       NULL
user4.key_dC_pwd      user5
user4.key_dC_pwd      user6
user4.key_dC_pwd    KC_tdb1 

Example 15

Displays the name and location of the current master key startup file configured for the current server:

sp_encryption mkey_startup_file
Msg 19956, Level 16, State 1: Procedure 'sp_encryption', Line 298: The current master key startup file is:'/sybase/release/ASE-150/init/ase_encrcols_mk_l157.dat'. 

Example 16

Displays three stored procedures are encrypted with key syb_syscommkey_123456, and are owned by user1 and user2:

sp_encryption  helpkey, "syb_syscommkey%", display_objs
Key Name                            Key Owner     Database Name
        Object owner           Object Name 
------------                        -------       -----------
        ------------            -----------       syb_syscommkey_1234567890ab             dbo            testdb
              user1            sp_mysproc1
syb_syscommkey_abcdefghijkl123456       dbo            testdb
              user1            sp_mysproc2_  
syb_syscommkey_ABCDEF123456             dbo            testdb
              user2            sp_mysproc3

Usage


Usage restrictions

Permissions

Only SSO, key custodian, and DBO can run sp_encryption helpkey, master | 'dual master', display_keys to display encryption keys protected by the master or dual master key.

Only SSO, key custodian, and DBO can run sp_encryption helpkey, keyname, display_objs to display objects in current database protected by service key either syb_extpasswdkey or syb_syscommkey.

Only SSO and key custodian can run sp_encryption downgrade_kek_size to set or reset the 'downgrade_kek_size' option.

Only SSO can run sp_encryption mkey_startup_file {'newpath' | 'default_location' | 'null'} [, {sync_with_mem | sync_with_qrm}]] to display, set, sync the master key startup file.

Only SSO can run sp_encryption helpextpasswd to check the status of external passwords.