Tables and Views
CDP for MSSQL includes the following tables and views.
Tables
ING_AUTHORIZED_USER
This table is used to define all users authorized to perform encrypt and decrypt operations.
| Column | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | INT | Primary Key, NOT NULL |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR(256) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) | |
| ENCRYPTED_USER_NAME | VARCHAR(128) | |
| ENCRYPTED_ACCESS_CRED | VARCHAR(128) | |
| ENCRYPTED_DGTL_SGNTR | VARCHAR(256) |
Note
In case of fresh installation, the ENCRPT_USER_NM, ENCRPT_ACCESS_CD, and DGTL_SGNTR_NM columns will always remain empty. In future releases, these columns will be removed from the ING_AUTHORIZED_USER table.
ING_COLUMN_DEFAULT
This table is used to specify details about columns that have default values defined. This table is populated when Views and Triggers are created for an encrypted table.
| Column | Data Type | Description |
|---|---|---|
| DFLT_CLMN_ID | INT | Primary Key, NOT NULL |
| ENCRPT_TBL_ID | INT | Foreign Key, NOT NULL |
| CLMN_NM | VARCHAR(128) | NOT NULL |
| CLMN_NULL_DFLT_FL | CHAR(1) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_ENCRYPTED_COLUMN
This table is used to specify details about each column that is encrypted.
| Column | Data Type | Description |
|---|---|---|
| ENCRPT_CLMN_ID | INT | Primary Key, NOT NULL |
| ENCRPT_TBL_ID | INT | Foreign Key, NOT NULL |
| ORGNL_CLMN_NM | VARCHAR(128) | NOT NULL |
| NEW_CLMN_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_KEY_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ALGRTHM_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_MODE_NM | VARCHAR(30) | |
| ENCRPT_PAD_NM | VARCHAR(30) | |
| ENCRPT_IV_TYPE_CD | CHAR(1) | |
| ENCRPT_IV_NM | VARBINARY(128) | |
| ENCRPT_IV_CLMN_NM | VARCHAR(128) | |
| ENCRPT_NULL_RPLCMNT_NM | VARCHAR(128) | |
| ERR_RPLCMNT_ID | INT | |
| CLMN_ORGNL_DATA_TYPE_NM | VARCHAR(30) | NOT NULL |
| CLMN_ORGNL_LEN_QTY | INT | NOT NULL |
| CLMN_ORGNL_PRCSN_QTY | INT | |
| CLMN_ORGNL_SCALE_QTY | INT | |
| CLMN_NULL_FL | CHAR(1) | NOT NULL |
| CLMN_NEW_DATA_TYPE_NM | VARCHAR(30) | |
| CLMN_NEW_LEN_QTY | INT | |
| CLMN_DATA_MGRT_FL | CHAR(1) | NOT NULL |
| ENCRYTPT_FL | CHAR(1) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) | |
| TWEAKDATA_VAL | VARCHAR(256) | |
| TWEAKALGO_NM | VARCHAR(128) | |
| FPE_FL | CHAR(1) | |
| ROTATE_CLMN_NM | VARCHAR(30) | |
| ROTATE_CLMN_CREATE_FL | CHAR(1) | |
| ROTATE_KEY_NM | VARCHAR(128) | |
| ROTATE_IV_NM | VARBINARY(128) | |
| REROTATE_FL | CHAR(1) |
ING_ENCRYPTED_TABLE
This table is used to specify which tables contain encrypted columns.
| Column | Data Type | Description |
|---|---|---|
| ENCRPT_TBL_ID | INT | Primary Key, NOT NULL |
| DB_NM | VARCHAR(128) | NOT NULL |
| TBL_OWNR_NM | VARCHAR(128) | NOT NULL |
| ORGNL_TBL_NM | VARCHAR(128) | NOT NULL |
| NEW_TBL_NM | VARCHAR(128) | NOT NULL |
| ADD_ID_CLMN_FL | CHAR(1) | NOT NULL |
| ID_CLMN_NM | VARCHAR(128) | NOT NULL |
| ID_CLMN_DATA_TYPE_NM | VARCHAR(128) | NOT NULL |
| ID_CLMN_LEN_QTY | INT | NOT NULL |
| ID_CLMN_PRCSN_QTY | INT | |
| ID_CLMN_SCALE_QTY | INT | |
| TMPRY_TBL_NM | VARCHAR(128) | |
| TMPRY_TBL_TBLSPC_NM | VARCHAR(128) | |
| INTRM_VW_NM | VARCHAR(128) | |
| INSRT_TRG_NM | VARCHAR(128) | |
| UPDT_TRG_NM | VARCHAR(128) | |
| PRIMARY_KEY_COL_NMS | VARCHAR(2000) | |
| PRIMARY_KEY_NM | VARCHAR(128) | |
| TMPRY_TBL_CREATE_FL | CHAR(1) | NOT NULL |
| VW_CREATE_FL | CHAR(1) | NOT NULL |
| SERVER_VERSION | VARCHAR(20) | |
| SERVER_MIGRATION_VERSION | VARCHAR(20) | |
| UDF_VERSION | VARCHAR(20) | |
| SEQ_NM | VARCHAR(30) | |
| OLD_DATA_EXIST_FL | CHAR(1) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) | |
| META_CNTR | INT | |
| OP_ONLINE | CHAR(1) |
ING_ERROR_LOG
This table is used to log errors during column specific encrypt and decrypt operations.
| Column | Data Type | Description |
|---|---|---|
| ERR_LOG_ID | INT | Primary Key, NOT NULL |
| ENCRPT_CLMN_ID | INT | Foreign Key |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| RQST_TYPE_CD | CHAR(1) | NOT NULL |
| ERR_DT | DATETIME | NOT NULL |
| ERR_DESC | VARCHAR(1024) | |
| ERR_MSG_TXT | VARCHAR(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR(2000) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB
This table is used to describe the details of a data migration operation.
| Column | Data Type | Description |
|---|---|---|
| JOB_ID | INT | Primary Key, NOT NULL |
| ENCRPT_TBL_ID | INT | Foreign Key, NOT NULL |
| JOB_TYPE_CD | VARCHAR(12) | Foreign Key, NOT NULL |
| JOB_STATUS_CD | VARCHAR(12) | Foreign Key, NOT NULL |
| JOB_START_DT | DATETIME | |
| JOB_END_DT | DATETIME | |
| TOTAL_ROWS_QTY | INT | |
| ROWS_MDFY_QTY | INT | |
| BATCH_SIZE_QTY | INT | |
| JOB_CNCL_FL | CHAR(1) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_BATCH
This table is used to record the status of a migration job.
| Column | Data Type | Description |
|---|---|---|
| JOB_ID | INT | Primary Key, Foreign Key, NOT NULL |
| BATCH_SEQ | INT | Primary Key, NOT NULL |
| JOB_STATUS_CD | VARCHAR(12) | Foreign Key, NOT NULL |
| BATCH_START_DT | DATETIME | |
| BATCH_END_DT | DATETIME | |
| BATCH_SIZE_QTY | INT | |
| SQL_ERR_ID | INT | |
| SQL_ERR_STATE_TXT | VARCHAR(200) | |
| ERR_CLASS_NM | VARCHAR(200) | |
| ERR_MSG_TXT | VARCHAR(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR(2000) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_COLUMN
This table is used to describe which columns are encrypted with which key. Primarily used during key rotation.
| Column | Data Type | Description |
|---|---|---|
| JOB_ID | INT | Primary Key, Foreign Key, NOT NULL |
| ENCRPT_CLMN_ID | INT | Primary Key, Foreign Key, NOT NULL |
| ENCRPT_KEY_NM | VARCHAR(128) | FOR BIT DATA |
| ENCRPT_IV_NM | VARBINARY(128) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_STATUS
This table is a reference table for valid values of Job Type.
| Column | Data Type | Description |
|---|---|---|
| JOB_STATUS_CD | VARCHAR(12) | Primary Key, NOT NULL |
| JOB_STATUS_NM | VARCHAR(128) | |
| JOB_STATUS_DESC | VARCHAR(1024) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_TYPE
This table is a reference table for valid values of Job Type.
| Column | Data Type | Description |
|---|---|---|
| JOB_TYPE_CD | VARCHAR(12) | Primary Key, NOT NULL |
| JOB_TYPE_NM | VARCHAR(128) | |
| JOB_TYPE_DESC | VARCHAR(1024) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_PROPERTY
| Column | Data Type | Description |
|---|---|---|
| PRPTY_KEY_NM | VARCHAR(100) | Primary Key, NOT NULL |
| PRPTY_VAL_NM | VARCHAR(100) | |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
Views
ING_AUTH_USER
This view allows a user to select only own data from the ING_AUTHORIZED_USER table. This view contains the following columns.
| Column | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | INT | NOT NULL |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR(256) | NOT NULL |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) | |
| ENCRYPTED_USER_NAME | VARCHAR(128) | |
| ENCRYPTED_ACCESS_CRED | VARCHAR(128) | |
| ENCRYPTED_DGTL_SGNTR | VARCHAR(256) |
ING_AUTH_USER_DATABASE
This view allows a user to select only authorized users’ data from the ING_AUTHORIZED_USER table. This view contains the following columns.
| Column | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | INT | NOT NULL |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR(256) | NOT NULL |
| CREATE_DT | DATETIME | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | DATETIME | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
Note
Access management of metadata database should be managed by database admin. The above mentioned views and tables should not be tempered.
