System Tables

New and changed system tables in Adaptive Server 15.0.2.

New system tables
System table Description
sysoptions sysoptions is a fake table queried by sp_options. The columns names are case-sensitive.

Changed system tables
System table Description of change
sysquerymetrics

Adaptive Server version 15.0.2 increases the number of metrics shared among user IDs. The change reduces the number of entries in sysquerymetrics (a view of sysqueryplans), and automatically aggregates the metrics for identical queries across different user IDs.

syscolumns

Adds a status bit to the status2 column that indicates an encrypted column has a decrypt_default attached to it:

0x00001000 (4096) – column has a decrypt default

sysobjects

The type column of sysobjects includes an entry of “DD” for each object that has a decrypt default.

sysaudits
Changes include:
  • The Alter Encryption Key audit event name is changed to AEK As/Not Default

  • Adaptive Server release 15.0.2 supports these audit events and numbers:
    • 118 – AEK Modify Encryption

    • 119 – AEK Add Encryption

    • 120 – AEK Drop Encryption

    • 121 – AEK Modify Owner

    • 122 – AEK Key Recovery

sysattributes
sysattributes includes these changes:
  • Adds two classes:
    • Class 31 allow password downgrade – when set to 1, allow password downgrade enables special handling of login passwords for compatibility with Adaptive Server release 15.0 and earlier.

    • Class 32 enable last login updates – when set to 1, enable last login updates enables system tables to store the date of the last login.

  • sysattributes includes information about default decrypt. These are the changes to the columns:
    • attribute – specifies a default decrypt on an encrypted column with a value of 1 (DECRYPT-DEFAULT_ID) for objects with a type of EC and a class of 25.

    • object – includes the decrypt default ID.

    • object_info_1 – includes the table ID for a table whose encrypted column defines the decrypt default.

    • object_info2 – specifies the colid of the encrypted column that includes the decrypt default.

sysencryptkeys
Changes to sysencryptkeys includes
  • New types:
    • EK_KEYCOPY – 0x0010,

    • EK_KEYBASE – 0x0020

    • EK_RECOVERY – 0x0040

  • New status bits:
    • EK_KEYRECOVERY(0x00000004) – keys encrypted for lost password protection.

    • EK_LOGINACCESS(0x00000008) – key encrypted for login access

    • EK_LOGINPASS (0x00000010) – key encrypted with login password

    • EK_USERPWD(0x00000100) – keys encrypted with user-encryption passwords

  • Changes to the description for the uid column – user access or key recovery row. uid contains the user ID (uid) identifying the database user associated with current row.

    Previous versions of Adaptive Server did not use the uid column.

syslogins

In previous releases the maximum length of the password column was 30 bytes. In Adaptive Server release 15.0.2, the maximum length of the password column is 128 bytes.

New columns are: lastlogindate, crdate, locksuid, lockreason, and lockdate.

syssrvroles In previous releases the maximum length of the password column was 30 bytes. In Adaptive Server release 15.0.2, the maximum length of the password column is 128 bytes.

See the Reference Manual: Tables.