Cómo construir un cluster de WSO2 Identity Server con Kubernetes y Raspberry Pi

docker pull jsurf/rpi-mariadb

8. Creamos el script mysql.sql basado en el recurso oficial de WSO2 para kubernetes (https://github.com/wso2/kubernetes-is/blob/master/simple/deployment-scripts/wso2is-ga.sh ).

DROP DATABASE IF EXISTS WSO2IS_SHARED_DB;
DROP DATABASE IF EXISTS WSO2IS_IDENTITY_DB;
CREATE DATABASE WSO2IS_SHARED_DB;
CREATE DATABASE WSO2IS_IDENTITY_DB;
GRANT ALL ON WSO2IS_SHARED_DB.* TO 'wso2carbon'@'%' IDENTIFIED BY 'wso2carbon';
GRANT ALL ON WSO2IS_IDENTITY_DB.* TO 'wso2carbon'@'%' IDENTIFIED BY 'wso2carbon';
USE WSO2IS_SHARED_DB;
CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
REG_LOCK_NAME VARCHAR (20),
REG_LOCK_STATUS VARCHAR (20),
REG_LOCKED_TIME TIMESTAMP,
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_LOCK_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_LOG (
REG_LOG_ID INTEGER AUTO_INCREMENT,
REG_PATH VARCHAR (750),
REG_USER_ID VARCHAR (31) NOT NULL,
REG_LOGGED_TIME TIMESTAMP NOT NULL,
REG_ACTION INTEGER NOT NULL,
REG_ACTION_DATA VARCHAR (500),
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
-- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
-- See CARBON-5917.
CREATE TABLE IF NOT EXISTS REG_PATH(
REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
REG_PATH_VALUE VARCHAR(750) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
REG_PATH_PARENT_ID INTEGER,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_CONTENT (
REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
REG_CONTENT_DATA LONGBLOB,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
REG_CONTENT_ID INTEGER NOT NULL,
REG_CONTENT_DATA LONGBLOB,
REG_DELETED   SMALLINT,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE (
REG_PATH_ID         INTEGER NOT NULL,
REG_NAME            VARCHAR(256),
REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
REG_MEDIA_TYPE      VARCHAR(500),
REG_CREATOR         VARCHAR(31) NOT NULL,
REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_LAST_UPDATOR    VARCHAR(31),
REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_DESCRIPTION     VARCHAR(1000),
REG_CONTENT_ID      INTEGER,
REG_TENANT_ID INTEGER DEFAULT 0,
REG_UUID VARCHAR(100) NOT NULL,
CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
REG_PATH_ID         INTEGER NOT NULL,
REG_NAME            VARCHAR(256),
REG_VERSION         INTEGER NOT NULL,
REG_MEDIA_TYPE      VARCHAR(500),
REG_CREATOR         VARCHAR(31) NOT NULL,
REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_LAST_UPDATOR    VARCHAR(31),
REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REG_DESCRIPTION     VARCHAR(1000),
REG_CONTENT_ID      INTEGER,
REG_DELETED         SMALLINT,
REG_TENANT_ID INTEGER DEFAULT 0,
REG_UUID VARCHAR(100) NOT NULL,
CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_COMMENT (
REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
REG_USER_ID           VARCHAR(31) NOT NULL,
REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
REG_COMMENT_ID          INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_RATING (
REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
REG_RATING        INTEGER NOT NULL,
REG_USER_ID       VARCHAR(31) NOT NULL,
REG_RATED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
REG_RATING_ID           INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_TAG (
REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
REG_TAG_NAME       VARCHAR(500) NOT NULL,
REG_USER_ID        VARCHAR(31) NOT NULL,
REG_TAGGED_TIME    TIMESTAMP NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
REG_TAG_ID              INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
CREATE TABLE IF NOT EXISTS REG_PROPERTY (
REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
REG_NAME       VARCHAR(100) NOT NULL,
REG_VALUE        VARCHAR(1000),
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
REG_PROPERTY_ID         INTEGER NOT NULL,
REG_VERSION             INTEGER,
REG_PATH_ID             INTEGER,
REG_RESOURCE_NAME       VARCHAR(256),
REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE INNODB;
ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
-- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
-- SRC_PATH_ID     INTEGER,
-- SRC_RESOURCE_NAME    VARCHAR(256),
-- SRC_VERSION     INTEGER,
-- TGT_PATH_ID     INTEGER,
-- TGT_RESOURCE_NAME    VARCHAR(256),
-- TGT_VERSION     INTEGER
-- )ENGINE INNODB;
--
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
REG_SOURCEPATH VARCHAR (750) NOT NULL,
REG_TARGETPATH VARCHAR (750) NOT NULL,
REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
REG_PATH_ID            INTEGER NOT NULL,
REG_RESOURCE_NAME      VARCHAR(255),
REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
REG_TENANT_ID INTEGER DEFAULT 0,
CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
-- ################################
-- USER MANAGER TABLES
-- ################################
CREATE TABLE UM_TENANT (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_TENANT_UUID VARCHAR(36) NOT NULL,
UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
UM_EMAIL VARCHAR(255),
UM_ACTIVE BOOLEAN DEFAULT FALSE,
UM_CREATED_DATE TIMESTAMP NOT NULL,
UM_USER_CONFIG LONGBLOB,
PRIMARY KEY (UM_ID),
UNIQUE(UM_DOMAIN_NAME),
UNIQUE(UM_TENANT_UUID)
)ENGINE INNODB;
CREATE TABLE UM_DOMAIN(
UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
ON UM_TENANT (UM_DOMAIN_NAME);
CREATE TABLE UM_USER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_ID VARCHAR(255) NOT NULL,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_USER_PASSWORD VARCHAR(255) NOT NULL,
UM_SALT_VALUE VARCHAR(31),
UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
UM_CHANGED_TIME TIMESTAMP NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_USER_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_USER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_USER_PASSWORD VARCHAR(255) NOT NULL,
UM_SALT_VALUE VARCHAR(31),
UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
UM_CHANGED_TIME TIMESTAMP NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_USER_NAME, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_MODULE(
UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
UM_MODULE_NAME VARCHAR(100),
UNIQUE(UM_MODULE_NAME),
PRIMARY KEY(UM_ID)
)ENGINE INNODB;
CREATE TABLE UM_MODULE_ACTIONS(
UM_ACTION VARCHAR(255) NOT NULL,
UM_MODULE_ID INTEGER NOT NULL,
PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE UM_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_RESOURCE_ID VARCHAR(255) NOT NULL,
UM_ACTION VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_MODULE_ID INTEGER DEFAULT 0,
UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
CREATE TABLE UM_ROLE_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PERMISSION_ID INTEGER NOT NULL,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_IS_ALLOWED SMALLINT NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
CREATE TABLE UM_USER_PERMISSION (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PERMISSION_ID INTEGER NOT NULL,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_IS_ALLOWED SMALLINT NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
CREATE TABLE UM_USER_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_ID INTEGER NOT NULL,
UM_USER_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SHARED_USER_ROLE(
UM_ROLE_ID INTEGER NOT NULL,
UM_USER_ID INTEGER NOT NULL,
UM_USER_TENANT_ID INTEGER NOT NULL,
UM_ROLE_TENANT_ID INTEGER NOT NULL,
UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE UM_ACCOUNT_MAPPING(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER NOT NULL,
UM_USER_STORE_DOMAIN VARCHAR(100),
UM_ACC_LINK_ID INTEGER NOT NULL,
UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID)
)ENGINE INNODB;
CREATE TABLE UM_USER_ATTRIBUTE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ATTR_NAME VARCHAR(255) NOT NULL,
UM_ATTR_VALUE VARCHAR(1024),
UM_PROFILE_ID VARCHAR(255),
UM_USER_ID INTEGER,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
--CREATE INDEX UM_ATTR_NAME_VALUE_INDEX ON UM_USER_ATTRIBUTE(UM_ATTR_NAME, UM_ATTR_VALUE);
CREATE TABLE UM_DIALECT(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_URI VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_CLAIM(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_ID INTEGER NOT NULL,
UM_CLAIM_URI VARCHAR(255) NOT NULL,
UM_DISPLAY_TAG VARCHAR(255),
UM_DESCRIPTION VARCHAR(255),
UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
UM_MAPPED_ATTRIBUTE VARCHAR(255),
UM_REG_EX VARCHAR(255),
UM_SUPPORTED SMALLINT,
UM_REQUIRED SMALLINT,
UM_DISPLAY_ORDER INTEGER,
UM_CHECKED_ATTRIBUTE SMALLINT,
UM_READ_ONLY SMALLINT,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_PROFILE_CONFIG(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_DIALECT_ID INTEGER NOT NULL,
UM_PROFILE_NAME VARCHAR(255),
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_PROFILE_ID INTEGER,
UM_CLAIM_ID INTEGER,
UM_BEHAVIOUR SMALLINT,
UM_TENANT_ID INTEGER DEFAULT 0,
FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
PRIMARY KEY(UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
CREATE TABLE UM_HYBRID_USER_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_GROUP_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_GROUP_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UM_DOMAIN_ID INTEGER,
UNIQUE (UM_GROUP_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID),
UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_SYSTEM_USER_ROLE(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255),
UM_ROLE_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE UM_HYBRID_REMEMBER_ME(
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_NAME VARCHAR(255) NOT NULL,
UM_COOKIE_VALUE VARCHAR(1024),
UM_CREATED_TIME TIMESTAMP,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_USER_ID VARCHAR(255) NOT NULL,
UM_DOMAIN_ID INTEGER NOT NULL,
UM_TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY (UM_ID),
UNIQUE (UM_USER_ID),
FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
USE WSO2IS_IDENTITY_DB;
CREATE TABLE IF NOT EXISTS IDN_BASE_TABLE (
PRODUCT_NAME VARCHAR(20),
PRIMARY KEY (PRODUCT_NAME)
)ENGINE INNODB;
INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
CREATE TABLE IF NOT EXISTS IDN_OAUTH_CONSUMER_APPS (
ID INTEGER NOT NULL AUTO_INCREMENT,
CONSUMER_KEY VARCHAR(255),
CONSUMER_SECRET VARCHAR(2048),
USERNAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT 0,
USER_DOMAIN VARCHAR(50),
APP_NAME VARCHAR(255),
OAUTH_VERSION VARCHAR(128),
CALLBACK_URL VARCHAR(2048),
GRANT_TYPES VARCHAR (1024),
PKCE_MANDATORY CHAR(1) DEFAULT '0',
PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_VALIDATORS (
APP_ID INTEGER NOT NULL,
SCOPE_VALIDATOR VARCHAR (128) NOT NULL,
PRIMARY KEY (APP_ID,SCOPE_VALIDATOR),
FOREIGN KEY (APP_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_REQUEST_TOKEN (
REQUEST_TOKEN VARCHAR(255),
REQUEST_TOKEN_SECRET VARCHAR(512),
CONSUMER_KEY_ID INTEGER,
CALLBACK_URL VARCHAR(2048),
SCOPE VARCHAR(2048),
AUTHORIZED VARCHAR(128),
OAUTH_VERIFIER VARCHAR(512),
AUTHZ_USER VARCHAR(512),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (REQUEST_TOKEN),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_ACCESS_TOKEN (
ACCESS_TOKEN VARCHAR(255),
ACCESS_TOKEN_SECRET VARCHAR(512),
CONSUMER_KEY_ID INTEGER,
SCOPE VARCHAR(2048),
AUTHZ_USER VARCHAR(512),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ACCESS_TOKEN),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN (
TOKEN_ID VARCHAR (255),
ACCESS_TOKEN VARCHAR(2048),
REFRESH_TOKEN VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
USER_TYPE VARCHAR (25),
GRANT_TYPE VARCHAR (50),
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REFRESH_TOKEN_TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
VALIDITY_PERIOD BIGINT,
REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
TOKEN_SCOPE_HASH VARCHAR(32),
TOKEN_STATE VARCHAR(25) DEFAULT 'ACTIVE',
TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE',
SUBJECT_IDENTIFIER VARCHAR(255),
ACCESS_TOKEN_HASH VARCHAR(512),
REFRESH_TOKEN_HASH VARCHAR(512),
IDP_ID INTEGER DEFAULT -1 NOT NULL,
TOKEN_BINDING_REF VARCHAR (32) DEFAULT 'NONE',
PRIMARY KEY (TOKEN_ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_TOKEN_BINDING (
TOKEN_ID VARCHAR (255),
TOKEN_BINDING_TYPE VARCHAR (32),
TOKEN_BINDING_REF VARCHAR (32),
TOKEN_BINDING_VALUE VARCHAR (1024),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (TOKEN_ID),
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
TOKEN_ID VARCHAR (255),
ACCESS_TOKEN VARCHAR(2048),
REFRESH_TOKEN VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
USER_TYPE VARCHAR (25),
GRANT_TYPE VARCHAR (50),
TIME_CREATED TIMESTAMP NULL,
REFRESH_TOKEN_TIME_CREATED TIMESTAMP NULL,
VALIDITY_PERIOD BIGINT,
REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
TOKEN_SCOPE_HASH VARCHAR(32),
TOKEN_STATE VARCHAR(25),
TOKEN_STATE_ID VARCHAR (128) ,
SUBJECT_IDENTIFIER VARCHAR(255),
ACCESS_TOKEN_HASH VARCHAR(512),
REFRESH_TOKEN_HASH VARCHAR(512),
INVALIDATED_TIME TIMESTAMP NULL,
IDP_ID INTEGER DEFAULT -1 NOT NULL
);
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHORIZATION_CODE (
CODE_ID VARCHAR (255),
AUTHORIZATION_CODE VARCHAR(2048),
CONSUMER_KEY_ID INTEGER,
CALLBACK_URL VARCHAR(2048),
SCOPE VARCHAR(2048),
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
VALIDITY_PERIOD BIGINT,
STATE VARCHAR (25) DEFAULT 'ACTIVE',
TOKEN_ID VARCHAR(255),
SUBJECT_IDENTIFIER VARCHAR(255),
PKCE_CODE_CHALLENGE VARCHAR(255),
PKCE_CODE_CHALLENGE_METHOD VARCHAR(128),
AUTHORIZATION_CODE_HASH VARCHAR(512),
IDP_ID INTEGER DEFAULT -1 NOT NULL,
PRIMARY KEY (CODE_ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHZ_CODE_SCOPE(
CODE_ID   VARCHAR(255),
SCOPE     VARCHAR(60),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (CODE_ID, SCOPE),
FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW (
CODE_ID VARCHAR(255),
DEVICE_CODE VARCHAR(255),
USER_CODE VARCHAR(25),
CONSUMER_KEY_ID INTEGER,
LAST_POLL_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXPIRY_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
POLL_TIME BIGINT,
STATUS VARCHAR (25) DEFAULT 'PENDING',
AUTHZ_USER VARCHAR (100),
TENANT_ID INTEGER,
USER_DOMAIN VARCHAR(50),
IDP_ID INTEGER,
PRIMARY KEY (DEVICE_CODE),
UNIQUE (CODE_ID),
UNIQUE (USER_CODE),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW_SCOPES (
ID INTEGER NOT NULL AUTO_INCREMENT,
SCOPE_ID VARCHAR(255),
SCOPE VARCHAR(255),
PRIMARY KEY (ID),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
TOKEN_ID VARCHAR (255),
TOKEN_SCOPE VARCHAR (60),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE),
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE (
SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(512),
TENANT_ID INTEGER NOT NULL DEFAULT -1,
SCOPE_TYPE VARCHAR(255) NOT NULL,
PRIMARY KEY (SCOPE_ID),
UNIQUE (NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_BINDING (
SCOPE_ID INTEGER NOT NULL,
SCOPE_BINDING VARCHAR(255) NOT NULL,
BINDING_TYPE VARCHAR(255) NOT NULL,
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE,
UNIQUE (SCOPE_ID, SCOPE_BINDING, BINDING_TYPE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_RESOURCE_SCOPE (
RESOURCE_PATH VARCHAR(255) NOT NULL,
SCOPE_ID INTEGER NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (RESOURCE_PATH),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(255) NOT NULL,
ATTR_NAME VARCHAR(1024) NOT NULL,
ATTR_VALUE VARCHAR(1024),
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OPENID_REMEMBER_ME (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT 0,
COOKIE_VALUE VARCHAR(1024),
CREATED_TIME TIMESTAMP,
PRIMARY KEY (USER_NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OPENID_USER_RPS (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT 0,
RP_URL VARCHAR(255) NOT NULL,
TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
LAST_VISIT DATE NOT NULL,
VISIT_COUNT INTEGER DEFAULT 0,
DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OPENID_ASSOCIATIONS (
HANDLE VARCHAR(255) NOT NULL,
ASSOC_TYPE VARCHAR(255) NOT NULL,
EXPIRE_IN TIMESTAMP NOT NULL,
MAC_KEY VARCHAR(255) NOT NULL,
ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (HANDLE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_STS_STORE (
ID INTEGER AUTO_INCREMENT,
TOKEN_ID VARCHAR(255) NOT NULL,
TOKEN_CONTENT BLOB(1024) NOT NULL,
CREATE_DATE TIMESTAMP NOT NULL,
EXPIRE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
STATE INTEGER DEFAULT 0,
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_IDENTITY_USER_DATA (
TENANT_ID INTEGER DEFAULT -1234,
USER_NAME VARCHAR(255) NOT NULL,
DATA_KEY VARCHAR(255) NOT NULL,
DATA_VALUE VARCHAR(2048),
PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_IDENTITY_META_DATA (
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1234,
METADATA_TYPE VARCHAR(255) NOT NULL,
METADATA VARCHAR(255) NOT NULL,
VALID VARCHAR(255) NOT NULL,
PRIMARY KEY (TENANT_ID, USER_NAME, METADATA_TYPE,METADATA)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_THRIFT_SESSION (
SESSION_ID VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
CREATED_TIME VARCHAR(255) NOT NULL,
LAST_MODIFIED_TIME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (SESSION_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_STORE (
SESSION_ID VARCHAR (100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BLOB,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_TEMP_SESSION_STORE (
SESSION_ID VARCHAR (100) NOT NULL,
SESSION_TYPE VARCHAR(100) NOT NULL,
OPERATION VARCHAR(10) NOT NULL,
SESSION_OBJECT BLOB,
TIME_CREATED BIGINT,
TENANT_ID INTEGER DEFAULT -1,
EXPIRY_TIME BIGINT,
PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_USER (
USER_ID VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
DOMAIN_NAME VARCHAR(255) NOT NULL,
IDP_ID INTEGER NOT NULL,
PRIMARY KEY (USER_ID),
CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID));
CREATE TABLE IF NOT EXISTS IDN_AUTH_USER_SESSION_MAPPING (
USER_ID VARCHAR(255) NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID));
CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_APP_INFO (
SESSION_ID VARCHAR (100) NOT NULL,
SUBJECT VARCHAR (100) NOT NULL,
APP_ID INTEGER NOT NULL,
INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
PRIMARY KEY (SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_META_DATA (
SESSION_ID VARCHAR (100) NOT NULL,
PROPERTY_TYPE VARCHAR (100) NOT NULL,
VALUE VARCHAR (255) NOT NULL,
PRIMARY KEY (SESSION_ID, PROPERTY_TYPE, VALUE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS SP_APP (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
APP_NAME VARCHAR (255) NOT NULL ,
USER_STORE VARCHAR (255) NOT NULL,
USERNAME VARCHAR (255) NOT NULL ,
DESCRIPTION VARCHAR (1024),
ROLE_CLAIM VARCHAR (512),
AUTH_TYPE VARCHAR (255) NOT NULL,
PROVISIONING_USERSTORE_DOMAIN VARCHAR (512),
IS_LOCAL_CLAIM_DIALECT CHAR(1) DEFAULT '1',
IS_SEND_LOCAL_SUBJECT_ID CHAR(1) DEFAULT '0',
IS_SEND_AUTH_LIST_OF_IDPS CHAR(1) DEFAULT '0',
IS_USE_TENANT_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
IS_USE_USER_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0',
SUBJECT_CLAIM_URI VARCHAR (512),
IS_SAAS_APP CHAR(1) DEFAULT '0',
IS_DUMB_MODE CHAR(1) DEFAULT '0',
UUID CHAR(36),
IMAGE_URL VARCHAR(1024),
ACCESS_URL VARCHAR(1024),
IS_DISCOVERABLE CHAR(1) DEFAULT '0',
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID);
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_UUID_CONSTRAINT UNIQUE(UUID);
CREATE TABLE IF NOT EXISTS SP_METADATA (
ID INTEGER AUTO_INCREMENT,
SP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
VALUE VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID),
CONSTRAINT SP_METADATA_CONSTRAINT UNIQUE (SP_ID, NAME),
FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS SP_INBOUND_AUTH (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
INBOUND_AUTH_KEY VARCHAR (255),
INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
INBOUND_CONFIG_TYPE VARCHAR (255) NOT NULL,
PROP_NAME VARCHAR (255),
PROP_VALUE VARCHAR (1024) ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_INBOUND_AUTH ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_AUTH_STEP (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
STEP_ORDER INTEGER DEFAULT 1,
APP_ID INTEGER NOT NULL ,
IS_SUBJECT_STEP CHAR(1) DEFAULT '0',
IS_ATTRIBUTE_STEP CHAR(1) DEFAULT '0',
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_AUTH_STEP ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_FEDERATED_IDP (
ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_ID INTEGER NOT NULL,
PRIMARY KEY (ID, AUTHENTICATOR_ID)
)ENGINE INNODB;
ALTER TABLE SP_FEDERATED_IDP ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_CLAIM_DIALECT (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
SP_DIALECT VARCHAR (512) NOT NULL,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID));
ALTER TABLE SP_CLAIM_DIALECT ADD CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
IDP_CLAIM VARCHAR (512) NOT NULL ,
SP_CLAIM VARCHAR (512) NOT NULL ,
APP_ID INTEGER NOT NULL,
IS_REQUESTED VARCHAR(128) DEFAULT '0',
IS_MANDATORY VARCHAR(128) DEFAULT '0',
DEFAULT_VALUE VARCHAR(255),
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_CLAIM_MAPPING ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_ROLE_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
IDP_ROLE VARCHAR (255) NOT NULL ,
SP_ROLE VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_ROLE_MAPPING ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_REQ_PATH_AUTHENTICATOR (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_REQ_PATH_AUTHENTICATOR ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS SP_PROVISIONING_CONNECTOR (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
IDP_NAME VARCHAR (255) NOT NULL ,
CONNECTOR_NAME VARCHAR (255) NOT NULL ,
APP_ID INTEGER NOT NULL,
IS_JIT_ENABLED CHAR(1) NOT NULL DEFAULT '0',
BLOCKING CHAR(1) NOT NULL DEFAULT '0',
RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE SP_PROVISIONING_CONNECTOR ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
CREATE TABLE SP_AUTH_SCRIPT (
ID         INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID  INTEGER                NOT NULL,
APP_ID     INTEGER                NOT NULL,
TYPE       VARCHAR(255)           NOT NULL,
CONTENT    BLOB    DEFAULT NULL,
IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID));
CREATE TABLE IF NOT EXISTS SP_TEMPLATE (
ID         INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID  INTEGER                NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023),
CONTENT BLOB DEFAULT NULL,
PRIMARY KEY (ID),
CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME));
CREATE TABLE IF NOT EXISTS IDN_AUTH_WAIT_STATUS (
ID              INTEGER AUTO_INCREMENT NOT NULL,
TENANT_ID       INTEGER                NOT NULL,
LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
TIME_CREATED    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXPIRE_TIME     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ID),
CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY));
CREATE TABLE IF NOT EXISTS IDP (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
NAME VARCHAR(254) NOT NULL,
IS_ENABLED CHAR(1) NOT NULL DEFAULT '1',
IS_PRIMARY CHAR(1) NOT NULL DEFAULT '0',
HOME_REALM_ID VARCHAR(254),
IMAGE MEDIUMBLOB,
CERTIFICATE BLOB,
ALIAS VARCHAR(254),
INBOUND_PROV_ENABLED CHAR (1) NOT NULL DEFAULT '0',
INBOUND_PROV_USER_STORE_ID VARCHAR(254),
USER_CLAIM_URI VARCHAR(254),
ROLE_CLAIM_URI VARCHAR(254),
DESCRIPTION VARCHAR (1024),
DEFAULT_AUTHENTICATOR_NAME VARCHAR(254),
DEFAULT_PRO_CONNECTOR_NAME VARCHAR(254),
PROVISIONING_ROLE VARCHAR(128),
IS_FEDERATION_HUB CHAR(1) NOT NULL DEFAULT '0',
IS_LOCAL_CLAIM_DIALECT CHAR(1) NOT NULL DEFAULT '0',
DISPLAY_NAME VARCHAR(255),
IMAGE_URL VARCHAR(1024),
UUID CHAR(36) NOT NULL,
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, NAME),
UNIQUE (UUID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_ROLE (
ID INTEGER AUTO_INCREMENT,
IDP_ID INTEGER,
TENANT_ID INTEGER,
ROLE VARCHAR(254),
PRIMARY KEY (ID),
UNIQUE (IDP_ID, ROLE),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_ROLE_MAPPING (
ID INTEGER AUTO_INCREMENT,
IDP_ROLE_ID INTEGER,
TENANT_ID INTEGER,
USER_STORE_ID VARCHAR (253),
LOCAL_ROLE VARCHAR(253),
PRIMARY KEY (ID),
UNIQUE (IDP_ROLE_ID, TENANT_ID, USER_STORE_ID, LOCAL_ROLE),
FOREIGN KEY (IDP_ROLE_ID) REFERENCES IDP_ROLE(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_CLAIM (
ID INTEGER AUTO_INCREMENT,
IDP_ID INTEGER,
TENANT_ID INTEGER,
CLAIM VARCHAR(254),
PRIMARY KEY (ID),
UNIQUE (IDP_ID, CLAIM),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_CLAIM_MAPPING (
ID INTEGER AUTO_INCREMENT,
IDP_CLAIM_ID INTEGER,
TENANT_ID INTEGER,
LOCAL_CLAIM VARCHAR(253),
DEFAULT_VALUE VARCHAR(255),
IS_REQUESTED VARCHAR(128) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (IDP_CLAIM_ID, TENANT_ID, LOCAL_CLAIM),
FOREIGN KEY (IDP_CLAIM_ID) REFERENCES IDP_CLAIM(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
IDP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
IS_ENABLED CHAR (1) DEFAULT '1',
DISPLAY_NAME VARCHAR(255),
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, NAME),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_METADATA (
ID INTEGER AUTO_INCREMENT,
IDP_ID INTEGER,
NAME VARCHAR(255) NOT NULL,
VALUE VARCHAR(255) NOT NULL,
DISPLAY_NAME VARCHAR(255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID),
CONSTRAINT IDP_METADATA_CONSTRAINT UNIQUE (IDP_ID, NAME),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR_PROPERTY (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
AUTHENTICATOR_ID INTEGER,
PROPERTY_KEY VARCHAR(255) NOT NULL,
PROPERTY_VALUE VARCHAR(2047),
IS_SECRET CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, AUTHENTICATOR_ID, PROPERTY_KEY),
FOREIGN KEY (AUTHENTICATOR_ID) REFERENCES IDP_AUTHENTICATOR(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_CONFIG (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
IDP_ID INTEGER,
PROVISIONING_CONNECTOR_TYPE VARCHAR(255) NOT NULL,
IS_ENABLED CHAR (1) DEFAULT '0',
IS_BLOCKING CHAR (1) DEFAULT '0',
IS_RULES_ENABLED CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROV_CONFIG_PROPERTY (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
PROVISIONING_CONFIG_ID INTEGER,
PROPERTY_KEY VARCHAR(255) NOT NULL,
PROPERTY_VALUE VARCHAR(2048),
PROPERTY_BLOB_VALUE BLOB,
PROPERTY_TYPE CHAR(32) NOT NULL,
IS_SECRET CHAR (1) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY),
FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_ENTITY (
ID INTEGER AUTO_INCREMENT,
PROVISIONING_CONFIG_ID INTEGER,
ENTITY_TYPE VARCHAR(255) NOT NULL,
ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
ENTITY_NAME VARCHAR(255) NOT NULL,
ENTITY_VALUE VARCHAR(255),
TENANT_ID INTEGER,
ENTITY_LOCAL_ID VARCHAR(255),
PRIMARY KEY (ID),
UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME, PROVISIONING_CONFIG_ID),
UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDP_LOCAL_CLAIM (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER,
IDP_ID INTEGER,
CLAIM_URI VARCHAR(255) NOT NULL,
DEFAULT_VALUE VARCHAR(255),
IS_REQUESTED VARCHAR(128) DEFAULT '0',
PRIMARY KEY (ID),
UNIQUE (TENANT_ID, IDP_ID, CLAIM_URI),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_ASSOCIATED_ID (
ID INTEGER AUTO_INCREMENT,
IDP_USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER DEFAULT -1234,
IDP_ID INTEGER NOT NULL,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
ASSOCIATION_ID CHAR(36) NOT NULL,
PRIMARY KEY (ID),
UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_USER_ACCOUNT_ASSOCIATION (
ASSOCIATION_KEY VARCHAR(255) NOT NULL,
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS FIDO_DEVICE_STORE (
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(45) NOT NULL,
TIME_REGISTERED TIMESTAMP,
KEY_HANDLE VARCHAR(200) NOT NULL,
DEVICE_DATA VARCHAR(2048) NOT NULL,
PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS FIDO2_DEVICE_STORE (
TENANT_ID INTEGER,
DOMAIN_NAME VARCHAR(255) NOT NULL,
USER_NAME VARCHAR(45) NOT NULL,
TIME_REGISTERED TIMESTAMP,
USER_HANDLE VARCHAR(64) NOT NULL,
CREDENTIAL_ID VARCHAR(200) NOT NULL,
PUBLIC_KEY_COSE VARCHAR(1024) NOT NULL,
SIGNATURE_COUNT BIGINT,
USER_IDENTITY VARCHAR(512) NOT NULL,
DISPLAY_NAME VARCHAR(255),
IS_USERNAMELESS_SUPPORTED CHAR(1) DEFAULT '0',
PRIMARY KEY (CREDENTIAL_ID, USER_HANDLE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_REQUEST (
UUID VARCHAR (45),
CREATED_BY VARCHAR (255),
TENANT_ID INTEGER DEFAULT -1,
OPERATION_TYPE VARCHAR (50),
CREATED_AT TIMESTAMP,
UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
STATUS VARCHAR (30),
REQUEST BLOB,
PRIMARY KEY (UUID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_BPS_PROFILE (
PROFILE_NAME VARCHAR(45),
HOST_URL_MANAGER VARCHAR(255),
HOST_URL_WORKER VARCHAR(255),
USERNAME VARCHAR(45),
PASSWORD VARCHAR(1023),
CALLBACK_HOST VARCHAR (45),
CALLBACK_USERNAME VARCHAR (45),
CALLBACK_PASSWORD VARCHAR (255),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (PROFILE_NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_WORKFLOW(
ID VARCHAR (45),
WF_NAME VARCHAR (45),
DESCRIPTION VARCHAR (255),
TEMPLATE_ID VARCHAR (45),
IMPL_ID VARCHAR (45),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_WORKFLOW_ASSOCIATION(
ID INTEGER NOT NULL AUTO_INCREMENT,
ASSOC_NAME VARCHAR (45),
EVENT_ID VARCHAR(45),
ASSOC_CONDITION VARCHAR (2000),
WORKFLOW_ID VARCHAR (45),
IS_ENABLED CHAR (1) DEFAULT '1',
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY(ID),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_WORKFLOW_CONFIG_PARAM(
WORKFLOW_ID VARCHAR (45),
PARAM_NAME VARCHAR (45),
PARAM_VALUE VARCHAR (1000),
PARAM_QNAME VARCHAR (45),
PARAM_HOLDER VARCHAR (45),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (WORKFLOW_ID, PARAM_NAME, PARAM_QNAME, PARAM_HOLDER),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_REQUEST_ENTITY_RELATIONSHIP(
REQUEST_ID VARCHAR (45),
ENTITY_NAME VARCHAR (255),
ENTITY_TYPE VARCHAR (50),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY(REQUEST_ID, ENTITY_NAME, ENTITY_TYPE, TENANT_ID),
FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS WF_WORKFLOW_REQUEST_RELATION(
RELATIONSHIP_ID VARCHAR (45),
WORKFLOW_ID VARCHAR (45),
REQUEST_ID VARCHAR (45),
UPDATED_AT TIMESTAMP,
STATUS VARCHAR (30),
TENANT_ID INTEGER DEFAULT -1,
PRIMARY KEY (RELATIONSHIP_ID),
FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE,
FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_RECOVERY_DATA (
USER_NAME VARCHAR(255) NOT NULL,
USER_DOMAIN VARCHAR(127) NOT NULL,
TENANT_ID INTEGER DEFAULT -1,
CODE VARCHAR(255) NOT NULL,
SCENARIO VARCHAR(255) NOT NULL,
STEP VARCHAR(127) NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
UNIQUE(CODE)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_PASSWORD_HISTORY_DATA (
ID INTEGER NOT NULL AUTO_INCREMENT,
USER_NAME   VARCHAR(255) NOT NULL,
USER_DOMAIN VARCHAR(127) NOT NULL,
TENANT_ID   INTEGER DEFAULT -1,
SALT_VALUE  VARCHAR(255),
HASH        VARCHAR(255) NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(ID),
UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_DIALECT (
ID INTEGER NOT NULL AUTO_INCREMENT,
DIALECT_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM (
ID INTEGER NOT NULL AUTO_INCREMENT,
DIALECT_ID INTEGER NOT NULL,
CLAIM_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE (
ID INTEGER NOT NULL AUTO_INCREMENT,
LOCAL_CLAIM_ID INTEGER,
USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_PROPERTY (
ID INTEGER NOT NULL AUTO_INCREMENT,
LOCAL_CLAIM_ID INTEGER,
PROPERTY_NAME VARCHAR (255) NOT NULL,
PROPERTY_VALUE VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
EXT_CLAIM_ID INTEGER NOT NULL,
MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS  IDN_SAML2_ASSERTION_STORE (
ID INTEGER NOT NULL AUTO_INCREMENT,
SAML2_ID  VARCHAR(255) ,
SAML2_ISSUER  VARCHAR(255) ,
SAML2_SUBJECT  VARCHAR(255) ,
SAML2_SESSION_INDEX  VARCHAR(255) ,
SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255) ,
SAML2_ASSERTION  VARCHAR(4096) ,
ASSERTION BLOB ,
PRIMARY KEY (ID)
)ENGINE INNODB;
CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
ID INT(11) NOT NULL AUTO_INCREMENT,
SOURCE_ID VARCHAR(255) NOT NULL,
MESSAGE_HANDLER VARCHAR(255) NOT NULL,
AUTHN_REQ_DTO BLOB NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
EXP_TIMESTAMP TIMESTAMP NOT NULL,
INIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ASSERTION_ID VARCHAR(255),
PRIMARY KEY (`ID`)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_JTI (
JWT_ID VARCHAR(255) NOT NULL,
EXP_TIME TIMESTAMP NOT NULL ,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (JWT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS  IDN_OIDC_PROPERTY (
ID INTEGER NOT NULL AUTO_INCREMENT,
TENANT_ID  INTEGER,
CONSUMER_KEY  VARCHAR(255) ,
PROPERTY_KEY  VARCHAR(255) NOT NULL,
PROPERTY_VALUE  VARCHAR(2047) ,
PRIMARY KEY (ID),
FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_REFERENCE (
ID INTEGER NOT NULL AUTO_INCREMENT,
CONSUMER_KEY_ID INTEGER ,
CODE_ID VARCHAR(255) ,
TOKEN_ID VARCHAR(255) ,
SESSION_DATA_KEY VARCHAR(255),
PRIMARY KEY (ID),
FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS (
ID INTEGER NOT NULL AUTO_INCREMENT,
REQ_OBJECT_ID INTEGER,
CLAIM_ATTRIBUTE VARCHAR(255) ,
ESSENTIAL CHAR(1) NOT NULL DEFAULT '0' ,
VALUE VARCHAR(255) ,
IS_USERINFO CHAR(1) NOT NULL DEFAULT '0',
PRIMARY KEY (ID),
FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE (ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
ID INTEGER NOT NULL AUTO_INCREMENT,
REQ_OBJECT_CLAIMS_ID INTEGER ,
CLAIM_VALUES VARCHAR(255) ,
PRIMARY KEY (ID),
FOREIGN KEY (REQ_OBJECT_CLAIMS_ID) REFERENCES  IDN_OIDC_REQ_OBJECT_CLAIMS(ID) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CERTIFICATE (
ID INTEGER NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100),
CERTIFICATE_IN_PEM BLOB,
TENANT_ID INTEGER DEFAULT 0,
PRIMARY KEY(ID),
CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE_CLAIM_MAPPING (
ID INTEGER NOT NULL AUTO_INCREMENT,
SCOPE_ID INTEGER NOT NULL,
EXTERNAL_CLAIM_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
UNIQUE (SCOPE_ID, EXTERNAL_CLAIM_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_FUNCTION_LIBRARY (
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023),
TYPE VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
DATA BLOB NOT NULL,
PRIMARY KEY (TENANT_ID,NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_AUTH_CODE (
AUTH_CODE_KEY CHAR (36),
AUTH_REQ_ID CHAR (36),
ISSUED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSUMER_KEY VARCHAR(255),
LAST_POLLED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
POLLING_INTERVAL INTEGER,
EXPIRES_IN  INTEGER,
AUTHENTICATED_USER_NAME VARCHAR(255),
USER_STORE_DOMAIN VARCHAR(100),
TENANT_ID INTEGER,
AUTH_REQ_STATUS VARCHAR (100) DEFAULT 'REQUESTED',
IDP_ID INTEGER,
UNIQUE(AUTH_REQ_ID),
PRIMARY KEY (AUTH_CODE_KEY),
FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_REQUEST_SCOPES (
AUTH_CODE_KEY CHAR (36),
SCOPE VARCHAR (255),
FOREIGN KEY (AUTH_CODE_KEY) REFERENCES IDN_OAUTH2_CIBA_AUTH_CODE(AUTH_CODE_KEY) ON DELETE CASCADE
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_FED_AUTH_SESSION_MAPPING (
IDP_SESSION_ID VARCHAR(255) NOT NULL,
SESSION_ID VARCHAR(255) NOT NULL,
IDP_NAME VARCHAR(255) NOT NULL,
AUTHENTICATOR_ID VARCHAR(255),
PROTOCOL_TYPE VARCHAR(255),
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (IDP_SESSION_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_TYPE (
ID VARCHAR(255) NOT NULL,
NAME VARCHAR(255) NOT NULL,
DESCRIPTION VARCHAR(1023) NULL,
PRIMARY KEY (ID),
CONSTRAINT TYPE_NAME_CONSTRAINT UNIQUE (NAME)
)ENGINE INNODB;
INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('9ab0ef95-13e9-4ed5-afaf-d29bed62f7bd', 'IDP_TEMPLATE', 'Template type to uniquely identify IDP templates'),
('3c4ac3d0-5903-4e3d-aaca-38df65b33bfd', 'APPLICATION_TEMPLATE', 'Template type to uniquely identify Application templates'),
('8ec6dbf1-218a-49bf-bc34-0d2db52d151c', 'CORS_CONFIGURATION', 'A resource type to keep the tenant CORS configurations');
CREATE TABLE IF NOT EXISTS IDN_CONFIG_RESOURCE (
ID VARCHAR(255) NOT NULL,
TENANT_ID INT NOT NULL,
NAME VARCHAR(255) NOT NULL,
CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
LAST_MODIFIED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
HAS_FILE tinyint(1) NOT NULL,
HAS_ATTRIBUTE tinyint(1) NOT NULL,
TYPE_ID VARCHAR(255) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT NAME_TENANT_TYPE_CONSTRAINT UNIQUE (NAME, TENANT_ID, TYPE_ID)
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_RESOURCE ADD CONSTRAINT TYPE_ID_FOREIGN_CONSTRAINT FOREIGN KEY (TYPE_ID) REFERENCES
IDN_CONFIG_TYPE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_ATTRIBUTE (
ID VARCHAR(255) NOT NULL,
RESOURCE_ID VARCHAR(255) NOT NULL,
ATTR_KEY VARCHAR(255) NOT NULL,
ATTR_VALUE VARCHAR(1023) NULL,
PRIMARY KEY (ID),
CONSTRAINT RESOURCE_KEY_VAL_CONSTRAINT UNIQUE (RESOURCE_ID(64), ATTR_KEY(255))
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_ATTRIBUTE ADD CONSTRAINT RESOURCE_ID_ATTRIBUTE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID)
REFERENCES IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS IDN_CONFIG_FILE (
ID VARCHAR(255) NOT NULL,
VALUE BLOB NULL,
RESOURCE_ID VARCHAR(255) NOT NULL,
NAME VARCHAR(255) NULL,
PRIMARY KEY (ID)
)ENGINE INNODB;
ALTER TABLE IDN_CONFIG_FILE ADD CONSTRAINT RESOURCE_ID_FILE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID) REFERENCES
IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IDN_REMOTE_FETCH_CONFIG (
ID VARCHAR(255) NOT NULL,
TENANT_ID INT NOT NULL,
IS_ENABLED CHAR(1) NOT NULL,
REPO_MANAGER_TYPE VARCHAR(255) NOT NULL,
ACTION_LISTENER_TYPE VARCHAR(255) NOT NULL,
CONFIG_DEPLOYER_TYPE VARCHAR(255) NOT NULL,
REMOTE_FETCH_NAME VARCHAR(255),
REMOTE_RESOURCE_URI VARCHAR(255) NOT NULL,
ATTRIBUTES_JSON MEDIUMTEXT NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT UC_REMOTE_RESOURCE_TYPE UNIQUE (TENANT_ID, CONFIG_DEPLOYER_TYPE)
)ENGINE INNODB;
CREATE TABLE IDN_REMOTE_FETCH_REVISIONS (
ID VARCHAR(255) NOT NULL,
CONFIG_ID VARCHAR(255) NOT NULL,
FILE_PATH VARCHAR(255) NOT NULL,
FILE_HASH VARCHAR(255),
DEPLOYED_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
LAST_SYNC_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
DEPLOYMENT_STATUS VARCHAR(255),
ITEM_NAME VARCHAR(255),
DEPLOY_ERR_LOG MEDIUMTEXT,
PRIMARY KEY (ID),
FOREIGN KEY (CONFIG_ID) REFERENCES IDN_REMOTE_FETCH_CONFIG(ID) ON DELETE CASCADE,
CONSTRAINT UC_REVISIONS UNIQUE (CONFIG_ID, ITEM_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_USER_FUNCTIONALITY_MAPPING (
ID VARCHAR(255) NOT NULL,
USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
IS_FUNCTIONALITY_LOCKED BOOLEAN NOT NULL,
FUNCTIONALITY_UNLOCK_TIME BIGINT NOT NULL,
FUNCTIONALITY_LOCK_REASON VARCHAR(1023),
FUNCTIONALITY_LOCK_REASON_CODE VARCHAR(255),
PRIMARY KEY (ID),
CONSTRAINT IDN_USER_FUNCTIONALITY_MAPPING_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_USER_FUNCTIONALITY_PROPERTY (
ID VARCHAR(255) NOT NULL,
USER_ID VARCHAR(255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
PROPERTY_NAME VARCHAR(255),
PROPERTY_VALUE VARCHAR(255),
PRIMARY KEY (ID),
CONSTRAINT IDN_USER_FUNCTIONALITY_PROPERTY_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID, PROPERTY_NAME)
)ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CORS_ORIGIN (
ID                INT           NOT NULL AUTO_INCREMENT,
TENANT_ID         INT           NOT NULL,
ORIGIN            VARCHAR(2048) NOT NULL,
UUID              CHAR(36)      NOT NULL,
PRIMARY KEY (ID),
UNIQUE (UUID)
) ENGINE INNODB;
CREATE TABLE IF NOT EXISTS IDN_CORS_ASSOCIATION (
IDN_CORS_ORIGIN_ID  INT NOT NULL,
SP_APP_ID           INT NOT NULL,
PRIMARY KEY (IDN_CORS_ORIGIN_ID, SP_APP_ID),
FOREIGN KEY (IDN_CORS_ORIGIN_ID) REFERENCES IDN_CORS_ORIGIN (ID) ON DELETE CASCADE,
FOREIGN KEY (SP_APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
) ENGINE INNODB;
-- --------------------------- INDEX CREATION -----------------------------
-- IDN_OAUTH2_ACCESS_TOKEN --
CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED);
CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH);
CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
--CREATE INDEX IDX_AT_AT ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN);
CREATE INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH);
--CREATE INDEX IDX_AT_RT ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN);
CREATE INDEX IDX_AT_CKID_AU_TID_UD_TSH_TS ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);
-- IDN_OAUTH2_AUTHORIZATION_CODE --
CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH, CONSUMER_KEY_ID);
CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER, TENANT_ID, USER_DOMAIN, STATE);
CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID);
CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID);
--CREATE INDEX IDX_AC_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(AUTHORIZATION_CODE, CONSUMER_KEY_ID);
-- IDN_SCIM_GROUP --
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME);
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME(500));
-- IDN_AUTH_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
-- IDN_AUTH_TEMP_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
-- IDN_OIDC_SCOPE_CLAIM_MAPPING --
CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);
-- IDN_OAUTH2_SCOPE --
CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID);
-- IDN_OAUTH2_SCOPE_BINDING --
CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID);
-- IDN_OIDC_REQ_OBJECT_REFERENCE --
CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID);
-- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID);
-- SP_TEMPLATE --
CREATE INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (TENANT_ID, NAME);
-- IDN_AUTH_USER --
CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME);
CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID);
-- IDN_AUTH_USER_SESSION_MAPPING --
CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID);
CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID);
-- IDN_OAUTH_CONSUMER_APPS --
CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME);
-- IDX_SPI_APP --
CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID);
-- IDN_OIDC_PROPERTY --
CREATE INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY(TENANT_ID,CONSUMER_KEY);
-- IDN_FIDO2_PROPERTY --
CREATE INDEX IDX_FIDO2_STR ON FIDO2_DEVICE_STORE(USER_NAME, TENANT_ID, DOMAIN_NAME, CREDENTIAL_ID, USER_HANDLE);
-- IDN_ASSOCIATED_ID --
CREATE INDEX IDX_AI_DN_UN_AI ON IDN_ASSOCIATED_ID(DOMAIN_NAME, USER_NAME, ASSOCIATION_ID);
-- IDN_OAUTH2_TOKEN_BINDING --
CREATE INDEX IDX_IDN_AUTH_BIND ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_REF);
-- IDN_FED_AUTH_SESSION_MAPPING --
CREATE INDEX IDX_FEDERATED_AUTH_SESSION_ID ON IDN_FED_AUTH_SESSION_MAPPING (SESSION_ID);
-- IDN_REMOTE_FETCH_REVISIONS --
CREATE INDEX IDX_REMOTE_FETCH_REVISION_CONFIG_ID ON IDN_REMOTE_FETCH_REVISIONS (CONFIG_ID);
-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_SP_APP_ID ON IDN_CORS_ASSOCIATION (SP_APP_ID);
-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_ORIGIN_ID ON IDN_CORS_ASSOCIATION (IDN_CORS_ORIGIN_ID);

Copiamos el script mysql.sql creado anteriormente y lo ejecutamos para crear los esquemas WSO2IS_SHARED_DB y WSO2IS_IDENTITY_DB

docker cp mysql.sql wso2is-mariadb:.
docker exec -ti wso2is-mariadb bash
mysql -u root -p123456 < /mysql.sql

9. Desplegar Identity Server

Nos conectamos nuevamente al nodo master nodeA y creamos el archivo deployment.yaml

nano deployment.yaml

y colocamos la siguiente configuración basada en el recurso oficial de wso2 para kubernetes (https://github.com/wso2/kubernetes-is/blob/master/simple/deployment-scripts/wso2is-ga.sh ), donde creamos el archivo de configuración deployment.toml que apuntará a la base de datos en el host 0x00 (192.168.1.101) y una sola replica:

apiVersion: v1
kind: Namespace
metadata:
name: wso2
spec:
finalizers:
- kubernetes
---
apiVersion: v1
kind: ServiceAccount
metadata:
name: wso2svc-account
namespace : wso2
---
apiVersion: v1
kind: ConfigMap
metadata:
name: identity-server-conf
namespace : wso2
data:
deployment.toml: |-
[server]
hostname = "$env{HOST_NAME}"
node_ip = "$env{NODE_IP}"
# base_path = "https://$ref{server.hostname}:${carbon.management.port}"
[super_admin]
username = "admin"
password = "admin"
create_admin_account = true
[user_store]
type = "read_write_ldap_unique_id"
connection_url = "ldap://localhost:${Ports.EmbeddedLDAP.LDAPServerPort}"
connection_name = "uid=admin,ou=system"
connection_password = "admin"
base_dn = "dc=wso2,dc=org"      #refers the base dn on which the user and group search bases will be generated
[database.identity_db]
type = "mysql"
url = "jdbc:mysql://192.168.1.101:3306/WSO2IS_IDENTITY_DB?autoReconnect=true&useSSL=false"
username = "root"
password = "123456"
driver = "com.mysql.cj.jdbc.Driver"
[database.identity_db.pool_options]
validationQuery = "SELECT 1"
[database.shared_db]
type = "mysql"
url = "jdbc:mysql://192.168.1.101:3306/WSO2IS_SHARED_DB?autoReconnect=true&useSSL=false"
username = "root"
password = "123456"
driver = "com.mysql.cj.jdbc.Driver"
[database.shared_db.pool_options]
validationQuery = "SELECT 1"
[keystore.primary]
file_name = "wso2carbon.jks"
password = "wso2carbon"
---
apiVersion: v1
kind: Service
metadata:
name: wso2is-service
namespace : wso2
labels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
spec:
selector:
deployment: wso2is
app: wso2is
type: NodePort
ports:
- name: servlet-http
port: 9763
targetPort: 9763
protocol: TCP
- name: servlet-https
port: 9443
targetPort: 9443
protocol: TCP
nodePort: 30443
---
apiVersion: apps/v1
kind: Deployment
metadata:
name: wso2is-deployment
namespace : wso2
spec:
replicas: 1
minReadySeconds: 600
strategy:
rollingUpdate:
maxSurge: 1
maxUnavailable: 0
type: RollingUpdate
selector:
matchLabels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
template:
metadata:
labels:
deployment: wso2is
app: wso2is
monitoring: jmx
pod: wso2is
spec:
hostAliases:
- ip: "127.0.0.1"
hostnames:
- "wso2is"
containers:
- name: wso2is
image: chakray/wso2is-rpi:5.10.0
livenessProbe:
exec:
command:
- /bin/sh
- -c
- nc -z localhost 9443
initialDelaySeconds: 250
periodSeconds: 10
readinessProbe:
exec:
command:
- /bin/sh
- -c
- nc -z localhost 9443
initialDelaySeconds: 250
periodSeconds: 10
imagePullPolicy: Always
resources:
requests:
memory: "1Gi"
cpu: "2000m"
limits:
memory: "2Gi"
cpu: "4000m"
lifecycle:
preStop:
exec:
command:  ['sh', '-c', '${WSO2_SERVER_HOME}/bin/wso2server.sh stop']
securityContext:
runAsUser: 802
env:
- name: NODE_IP
valueFrom:
fieldRef:
fieldPath: status.podIP
- name: HOST_NAME
value: wso2is
ports:
- containerPort: 9763
protocol: TCP
- containerPort: 9443
protocol: TCP
volumeMounts:
- name: identity-server-conf
mountPath: /home/wso2carbon/wso2-config-volume/repository/conf/deployment.toml
subPath: deployment.toml
serviceAccountName: "wso2svc-account"
imagePullSecrets:
- name: wso2is-deployment-creds
volumes:
- name: identity-server-conf
configMap:
name: identity-server-conf

El script anterior creará el namespace wso2, el service account wso2svc-account, el configmap identity-server-conf, el service wso2is-service y el deployment wso2is-deployment y utilizará la imagen de docker chakray/wso2is-rpi:5.10.0, la cuál está basada en la imagen de docker oficial de wso2 para Identity Server pero pensada para arquitecturas ARM.

Creamos los recursos:

sudo kubectl apply -f .

Listamos los pods en el namespace wso2

sudo kubectl get pods -n wso2

sudo kubectl describe pod wso2is-deployment-6f66b69947-6n4jw -n wso2

Lo siguiente será esperar a que concluya el proceso de creación esto sucederá cuando se visualice el mensaje: “Started container wso2is“

Volvemos a listar los pods y debe visualizarse el número de pods y el status

sudo kubectl get pods -n wso2

Podemos escalar el número de replicas con el siguiente comando, lo cuál creará nuevos pods de acuerdo al número de replicas que coloquemos:

sudo  kubectl scale --current-replicas=1 --replicas=2 deployment/wso2is-deployment -n wso2

El nombre del deployment se puede obtener listando los deployment en el namespace wso2

sudo kubectl get deployments -n wso2

Para visualizar el proceso de inicio de Identity Server podemos monitorear los logs de IS con el siguiente comando:

sudo kubectl logs wso2is-deployment-6f66b69947-9kkbm -n wso2 -f

Al concluir el proceso de inicio de Identity Server se puede validar accediendo a la ip del nodo donde se encuentre la instancia corriendo y el puerto 10443 (configurado en el archivo deployment.yaml) https://192.168.1.103:10443

10. Comandos útiles

Los siguientes comandos pueden ser útiles para depurar algún error durante la creación o para gestionar mejor el cluster:

1.Obtener información del cluster.

sudo kubectl cluster-info

2. Reiniciar k3s en el nodo master.

sudo systemctl restart k3s-agent

3. Reiniciar k3s agent en los nodos worker.

sudo systemctl restart k3s-agent

4. Revisar logs.

# Revisar logs de k3s
sudo journalctl -u k3s -r
# Revisar logs generales
sudo tail -f /var/log/syslog
# Revisar logs de un pod específico
sudo kubectl logs wso2is-deployment-6f66b69947-9kkbm -n wso2 -f
# Revisar logs de un deployment
sudo kubectl logs deploy/wso2is-deployment -f

5. Eliminar el namespace wso2 (donde se crean todos los recursos, pods, services, etc.)

sudo kubectl delete namespace wso2

11. Enlaces

• https://kubernetes.io/es/docs/tasks/tools/install-kubectl/

• https://kubernetes.io/docs/reference/kubectl/cheatsheet/

• https://hub.docker.com/r/jsurf/rpi-mariadb

Ebook

Caso de éxito: la implantación de la arquitectura WSO2

Aprende en este Ebook la implantación de la arquitectura wso2 y cómo una compañía en el sector automovilístico obtuvo una integración completa.

Descargar Ebook