Repository: sentry Updated Branches: refs/heads/master 1efc55628 -> 4c49a3fb0
SENTRY-2154: Update schema to grant privileges to user (Na Li, reviewed by Sergio Pena, Kalyan Kumar Kalvagadda, Arjun Mishra) Project: http://git-wip-us.apache.org/repos/asf/sentry/repo Commit: http://git-wip-us.apache.org/repos/asf/sentry/commit/4c49a3fb Tree: http://git-wip-us.apache.org/repos/asf/sentry/tree/4c49a3fb Diff: http://git-wip-us.apache.org/repos/asf/sentry/diff/4c49a3fb Branch: refs/heads/master Commit: 4c49a3fb094223f5801239d9aaaa8c420b696fa7 Parents: 1efc556 Author: lina.li <[email protected]> Authored: Tue May 8 13:44:21 2018 -0500 Committer: lina.li <[email protected]> Committed: Tue May 8 13:44:21 2018 -0500 ---------------------------------------------------------------------- .../main/resources/011-SENTRY-2154.derby.sql | 21 +++++++++++++++++ .../main/resources/011-SENTRY-2154.mysql.sql | 21 +++++++++++++++++ .../main/resources/011-SENTRY-2154.oracle.sql | 20 ++++++++++++++++ .../main/resources/011-SENTRY-2154.postgres.sql | 21 +++++++++++++++++ .../src/main/resources/sentry-db2-2.1.0.sql | 21 ++++++++++++++++- .../src/main/resources/sentry-derby-2.1.0.sql | 22 ++++++++++++++++++ .../src/main/resources/sentry-mysql-2.1.0.sql | 24 +++++++++++++++++++- .../src/main/resources/sentry-oracle-2.1.0.sql | 24 +++++++++++++++++++- .../main/resources/sentry-postgres-2.1.0.sql | 22 ++++++++++++++++++ .../sentry-upgrade-db2-2.0.0-to-2.1.0.sql | 19 ++++++++++++++++ .../sentry-upgrade-derby-2.0.0-to-2.1.0.sql | 1 + .../sentry-upgrade-mysql-2.0.0-to-2.1.0.sql | 1 + .../sentry-upgrade-oracle-2.0.0-to-2.1.0.sql | 1 + .../sentry-upgrade-postgres-2.0.0-to-2.1.0.sql | 1 + 14 files changed, 216 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql new file mode 100644 index 0000000..e042ec4 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql @@ -0,0 +1,21 @@ +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE SENTRY_USER_DB_PRIVILEGE_MAP ( + USER_ID BIGINT NOT NULL, + DB_PRIVILEGE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_PK PRIMARY KEY (USER_ID,DB_PRIVILEGE_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SEN_USR_DB_PRV_MAP_SN_USR_FK + FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER(USER_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK + FOREIGN KEY (DB_PRIVILEGE_ID) REFERENCES SENTRY_DB_PRIVILEGE(DB_PRIVILEGE_ID); + +CREATE INDEX SEN_USR_DB_PRV_MAP_USR_FK_IDX ON SENTRY_USER_DB_PRIVILEGE_MAP (USER_ID); + +CREATE INDEX SEN_USR_DB_PRV_MAP_PRV_FK_IDX ON SENTRY_USER_DB_PRIVILEGE_MAP (DB_PRIVILEGE_ID); http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql new file mode 100644 index 0000000..db12019 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql @@ -0,0 +1,21 @@ +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( + `USER_ID` BIGINT NOT NULL, + `DB_PRIVILEGE_ID` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SENTRY_USER_DB_PRIVILEGE_MAP_PK` PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`); + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` + FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER`(`USER_ID`); + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` + FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE`(`DB_PRIVILEGE_ID`); + +CREATE INDEX `SEN_USR_DB_PRV_MAP_USR_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`USER_ID`); + +CREATE INDEX `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.oracle.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.oracle.sql b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.oracle.sql new file mode 100644 index 0000000..9852ac5 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.oracle.sql @@ -0,0 +1,20 @@ +CREATE TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" ( + "USER_ID" NUMBER NOT NULL, + "DB_PRIVILEGE_ID" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR2(128) +); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_PRIV_MAP_PK" PRIMARY KEY ("USER_ID","DB_PRIVILEGE_ID"); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_DB_PRV_MAP_SN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") INITIALLY DEFERRED; + +CREATE INDEX "SEN_USR_DB_PRV_MAP_USR_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("USER_ID"); + +CREATE INDEX "SEN_USR_DB_PRV_MAP_PRV_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("DB_PRIVILEGE_ID"); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.postgres.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.postgres.sql b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.postgres.sql new file mode 100644 index 0000000..a57e4f2 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.postgres.sql @@ -0,0 +1,21 @@ +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" ( + "USER_ID" BIGINT NOT NULL, + "DB_PRIVILEGE_ID" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" character varying(128) +); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SENTRY_USER_DB_PRIVILEGE_MAP_PK" PRIMARY KEY ("USER_ID","DB_PRIVILEGE_ID"); + +ALTER TABLE ONLY "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SN_USR_DB_PRV_MAP_SN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SN_USR_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") DEFERRABLE; + +CREATE INDEX "SEN_USR_DB_PRV_MAP_USR_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("USER_ID"); + +CREATE INDEX "SEN_USR_DB_PRV_MAP_PRV_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("DB_PRIVILEGE_ID"); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql index 5e36ee3..f2a109e 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql @@ -251,4 +251,23 @@ CREATE TABLE SENTRY_HMS_NOTIFICATION_ID NOTIFICATION_ID bigint NOT NULL ); -CREATE INDEX SENTRY_HMS_NOTIF_ID_INDEX ON SENTRY_HMS_NOTIFICATION_ID (NOTIFICATION_ID); \ No newline at end of file +CREATE INDEX SENTRY_HMS_NOTIF_ID_INDEX ON SENTRY_HMS_NOTIFICATION_ID (NOTIFICATION_ID); + +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE SENTRY_USER_DB_PRIVILEGE_MAP +( + USER_ID BIGINT NOT NULL, + DB_PRIVILEGE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +-- Constraints for table SENTRY_USER_DB_PRIVILEGE_MAP +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_PK PRIMARY KEY (USER_ID,DB_PRIVILEGE_ID); + +CREATE INDEX SENTRY_USER_DB_PRIVILEGE_MAP_N50 ON SENTRY_USER_DB_PRIVILEGE_MAP (USER_ID); + +CREATE INDEX SENTRY_USER_DB_PRIVILEGE_MAP_N49 ON SENTRY_USER_DB_PRIVILEGE_MAP (DB_PRIVILEGE_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_FK2 FOREIGN KEY (DB_PRIVILEGE_ID) REFERENCES SENTRY_DB_PRIVILEGE (DB_PRIVILEGE_ID) ; + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_FK1 FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER (USER_ID) ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql index 374d59e..b23e782 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql @@ -252,3 +252,25 @@ CREATE TABLE SENTRY_HMS_NOTIFICATION_ID ); CREATE INDEX SENTRY_HMS_NOTIF_ID_INDEX ON SENTRY_HMS_NOTIFICATION_ID (NOTIFICATION_ID); + +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE SENTRY_USER_DB_PRIVILEGE_MAP ( + USER_ID BIGINT NOT NULL, + DB_PRIVILEGE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_PK PRIMARY KEY (USER_ID,DB_PRIVILEGE_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SEN_USR_DB_PRV_MAP_SN_USR_FK + FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER(USER_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP + ADD CONSTRAINT SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK + FOREIGN KEY (DB_PRIVILEGE_ID) REFERENCES SENTRY_DB_PRIVILEGE(DB_PRIVILEGE_ID); + +CREATE INDEX SEN_USR_DB_PRV_MAP_USR_FK_IDX ON SENTRY_USER_DB_PRIVILEGE_MAP (USER_ID); + +CREATE INDEX SEN_USR_DB_PRV_MAP_PRV_FK_IDX ON SENTRY_USER_DB_PRIVILEGE_MAP (DB_PRIVILEGE_ID); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql index 8876872..2630e01 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql @@ -286,4 +286,26 @@ CREATE TABLE `SENTRY_HMS_NOTIFICATION_ID` `NOTIFICATION_ID` BIGINT NOT NULL )ENGINE=INNODB; -CREATE INDEX `SENTRY_HMS_NOTIF_ID_INDEX` ON `SENTRY_HMS_NOTIFICATION_ID` (`NOTIFICATION_ID`); \ No newline at end of file +CREATE INDEX `SENTRY_HMS_NOTIF_ID_INDEX` ON `SENTRY_HMS_NOTIFICATION_ID` (`NOTIFICATION_ID`); + +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( + `USER_ID` BIGINT NOT NULL, + `DB_PRIVILEGE_ID` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SENTRY_USER_DB_PRIVILEGE_MAP_PK` PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`); + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` + FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER`(`USER_ID`); + +ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` + FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE`(`DB_PRIVILEGE_ID`); + +CREATE INDEX `SEN_USR_DB_PRV_MAP_USR_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`USER_ID`); + +CREATE INDEX `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql index 46d3fc8..847a359 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql @@ -266,4 +266,26 @@ CREATE TABLE "SENTRY_HMS_NOTIFICATION_ID" "NOTIFICATION_ID" NUMBER NOT NULL ); -CREATE INDEX "SENTRY_HMS_NOTIF_ID_INDEX" ON "SENTRY_HMS_NOTIFICATION_ID" ("NOTIFICATION_ID"); \ No newline at end of file +CREATE INDEX "SENTRY_HMS_NOTIF_ID_INDEX" ON "SENTRY_HMS_NOTIFICATION_ID" ("NOTIFICATION_ID"); + +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" ( + "USER_ID" NUMBER NOT NULL, + "DB_PRIVILEGE_ID" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR2(128) +); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_PRIV_MAP_PK" PRIMARY KEY ("USER_ID","DB_PRIVILEGE_ID"); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_DB_PRV_MAP_SN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_USER_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") INITIALLY DEFERRED; + +CREATE INDEX "SEN_USR_DB_PRV_MAP_USR_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("USER_ID"); + +CREATE INDEX "SEN_USR_DB_PRV_MAP_PRV_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("DB_PRIVILEGE_ID"); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql index c28dd9f..97a666a 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql @@ -276,4 +276,26 @@ CREATE TABLE "SENTRY_HMS_NOTIFICATION_ID" CREATE INDEX "SENTRY_HMS_NOTIF_ID_INDEX" ON "SENTRY_HMS_NOTIFICATION_ID" ("NOTIFICATION_ID"); +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" ( + "USER_ID" BIGINT NOT NULL, + "DB_PRIVILEGE_ID" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" character varying(128) +); + +ALTER TABLE "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SENTRY_USER_DB_PRIVILEGE_MAP_PK" PRIMARY KEY ("USER_ID","DB_PRIVILEGE_ID"); + +ALTER TABLE ONLY "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SN_USR_DB_PRV_MAP_SN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_USER_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SN_USR_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") DEFERRABLE; + +CREATE INDEX "SEN_USR_DB_PRV_MAP_USR_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("USER_ID"); + +CREATE INDEX "SEN_USR_DB_PRV_MAP_PRV_FK_IDX" ON "SENTRY_USER_DB_PRIVILEGE_MAP" ("DB_PRIVILEGE_ID"); + COMMIT; http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql index 3e0373d..d6be0d6 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql @@ -1,6 +1,25 @@ -- create index for foreign key AUTHZ_OBJ_ID CREATE INDEX AUTHZ_PATH_FK_IDX ON AUTHZ_PATH (AUTHZ_OBJ_ID); +-- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship +CREATE TABLE SENTRY_USER_DB_PRIVILEGE_MAP +( + USER_ID BIGINT NOT NULL, + DB_PRIVILEGE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +-- Constraints for table SENTRY_USER_DB_PRIVILEGE_MAP +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_PK PRIMARY KEY (USER_ID,DB_PRIVILEGE_ID); + +CREATE INDEX SENTRY_USER_DB_PRIVILEGE_MAP_N50 ON SENTRY_USER_DB_PRIVILEGE_MAP (USER_ID); + +CREATE INDEX SENTRY_USER_DB_PRIVILEGE_MAP_N49 ON SENTRY_USER_DB_PRIVILEGE_MAP (DB_PRIVILEGE_ID); + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_FK2 FOREIGN KEY (DB_PRIVILEGE_ID) REFERENCES SENTRY_DB_PRIVILEGE (DB_PRIVILEGE_ID) ; + +ALTER TABLE SENTRY_USER_DB_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_USER_DB_PRIVILEGE_MAP_FK1 FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER (USER_ID) ; + -- Version update UPDATE SENTRY_VERSION SET SCHEMA_VERSION='2.1.0', VERSION_COMMENT='Sentry release version 2.1.0' WHERE VER_ID=1; http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql index 5640b76..0ea7f79 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql @@ -1,4 +1,5 @@ RUN '010-SENTRY-2210.derby.sql'; +RUN '011-SENTRY-2154.derby.sql'; -- Version update UPDATE SENTRY_VERSION SET SCHEMA_VERSION='2.1.0', VERSION_COMMENT='Sentry release version 2.1.0' WHERE VER_ID=1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql index 350bc48..d679da8 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql @@ -1,5 +1,6 @@ SELECT 'Upgrading Sentry store schema from 2.0.0 to 2.1.0' AS ' '; SOURCE 010-SENTRY-2210.mysql.sql; +SOURCE 011-SENTRY-2154.mysql.sql; UPDATE SENTRY_VERSION SET SCHEMA_VERSION='2.1.0', VERSION_COMMENT='Sentry release version 2.1.0' WHERE VER_ID=1; http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql index 3afd788..4a1d7c4 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql @@ -1,5 +1,6 @@ SELECT 'Upgrading Sentry store schema from 2.0.0 to 2.1.0' AS Status from dual; @010-SENTRY-2210.oracle.sql; [email protected]; UPDATE SENTRY_VERSION SET SCHEMA_VERSION='2.1.0', VERSION_COMMENT='Sentry release version 2.1.0' WHERE VER_ID=1; http://git-wip-us.apache.org/repos/asf/sentry/blob/4c49a3fb/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql index 13df239..4507d08 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql @@ -1,5 +1,6 @@ SELECT 'Upgrading Sentry store schema from 2.0.0 to 2.1.0'; \i 010-SENTRY-2210.postgres.sql; +\i 011-SENTRY-2154.postgres.sql; UPDATE "SENTRY_VERSION" SET "SCHEMA_VERSION"='2.1.0', "VERSION_COMMENT"='Sentry release version 2.1.0' WHERE "VER_ID"=1;
