diff --git metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql new file mode 100644 index 0000000..ccad4e7 --- /dev/null +++ metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql @@ -0,0 +1,235 @@ +-- +-- HIVE-417 Implement Indexing in Hive +-- + +CREATE TABLE "IDXS" ( + "INDEX_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DEFERRED_REBUILD" CHAR(1) NOT NULL, + "INDEX_HANDLER_CLASS" VARCHAR(256), + "INDEX_NAME" VARCHAR(128), + "INDEX_TBL_ID" BIGINT, + "LAST_ACCESS_TIME" INTEGER NOT NULL, + "ORIG_TBL_ID" BIGINT, + "SD_ID" BIGINT); + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1" + FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2" + FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3" + FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK" + PRIMARY KEY ("INDEX_ID"); + +ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK" + CHECK (DEFERRED_REBUILD IN ('Y','N')); + + +CREATE TABLE "INDEX_PARAMS" ( + "INDEX_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(256) NOT NULL, + "PARAM_VALUE" VARCHAR(767)); + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" + FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" + PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); + +CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); + + +-- +-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs +-- +CREATE TABLE "DATABASE_PARAMS" ( + "DB_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(180) NOT NULL, + "PARAM_VALUE" VARCHAR(4000)); + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" + PRIMARY KEY ("DB_ID", "PARAM_KEY"); + +ALTER TABLE "DBS" DROP COLUMN "PARAMETERS"; + +-- +-- HIVE-78 Authorization model for Hive +-- + +CREATE TABLE "DB_PRIVS" ( + "DB_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DB_ID" BIGINT, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "DB_PRIV" VARCHAR(128)); + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" + PRIMARY KEY ("DB_GRANT_ID"); + +CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" ( + "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); + + +CREATE TABLE "PART_COL_PRIVS" ( + "PART_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_COL_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" + PRIMARY KEY ("PART_COLUMN_GRANT_ID"); + +CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" ( + "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE TABLE "PART_PRIVS" ( + "PART_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" + PRIMARY KEY ("PART_GRANT_ID"); + +CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" ( + "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); + + +CREATE TABLE "ROLES" ( + "ROLE_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "OWNER_NAME" VARCHAR(128), + "ROLE_NAME" VARCHAR(128)); + +ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK" + PRIMARY KEY ("ROLE_ID"); + +CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME"); + + +CREATE TABLE "ROLE_MAP" ( + "ROLE_GRANT_ID" BIGINT NOT NULL, + "ADD_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "ROLE_ID" BIGINT); + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" + FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" + PRIMARY KEY ("ROLE_GRANT_ID"); + +CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" ( + "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); + + +CREATE TABLE "TBL_COL_PRIVS" ( + "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_COL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" + PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); + +CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" ( + "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + + +CREATE TABLE "TBL_PRIVS" ( + "TBL_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" + PRIMARY KEY ("TBL_GRANT_ID"); + +CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" ( + "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + + +CREATE TABLE "GLOBAL_PRIVS" ( + "USER_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "USER_PRIV" VARCHAR(128)); + +ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" + PRIMARY KEY ("USER_GRANT_ID"); + +CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" ( + "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", + "GRANTOR", "GRANTOR_TYPE"); diff --git metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql new file mode 100644 index 0000000..aab664e --- /dev/null +++ metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql @@ -0,0 +1,160 @@ +-- +-- HIVE-417 Implement Indexing in Hive +-- +CREATE TABLE IF NOT EXISTS `IDXS` ( + `INDEX_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DEFERRED_REBUILD` bit(1) NOT NULL, + `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_TBL_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `ORIG_TBL_ID` bigint(20) DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`), + UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), + KEY `IDXS_FK1` (`SD_ID`), + KEY `IDXS_FK2` (`INDEX_TBL_ID`), + KEY `IDXS_FK3` (`ORIG_TBL_ID`), + CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), + CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), + CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( + `INDEX_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), + CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +-- +-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs +-- +CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( + `DB_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`,`PARAM_KEY`), + CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE `DBS` DROP COLUMN `PARAMETERS`; + +-- +-- HIVE-78 Authorization model for Hive +-- +CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( + `DB_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_GRANT_ID`), + UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( + `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_COLUMN_GRANT_ID`), + KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( + `PART_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_GRANT_ID`), + KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `ROLES` ( + `ROLE_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`ROLE_ID`), + UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( + `ROLE_GRANT_ID` bigint(20) NOT NULL, + `ADD_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`ROLE_GRANT_ID`), + UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( + `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), + KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( + `TBL_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_GRANT_ID`), + KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( + `USER_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`USER_GRANT_ID`), + UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1;