Repository: sentry Updated Branches: refs/heads/master c29f19bda -> 6c453a098
SENTRY-1254: Upgrading SQL script for implement grant user to role (Jia Ke via Dapeng Sun) Project: http://git-wip-us.apache.org/repos/asf/sentry/repo Commit: http://git-wip-us.apache.org/repos/asf/sentry/commit/6c453a09 Tree: http://git-wip-us.apache.org/repos/asf/sentry/tree/6c453a09 Diff: http://git-wip-us.apache.org/repos/asf/sentry/diff/6c453a09 Branch: refs/heads/master Commit: 6c453a0980f1e8621285c348e78d72c6a4d8e66e Parents: c29f19b Author: Jia Ke <[email protected]> Authored: Mon May 23 10:28:57 2016 +0800 Committer: Sun Dapeng <[email protected]> Committed: Mon May 23 10:29:36 2016 +0800 ---------------------------------------------------------------------- .../src/main/resources/006-SENTRY-711.derby.sql | 27 ++++++++++++++++++ .../src/main/resources/006-SENTRY-711.mysql.sql | 28 ++++++++++++++++++ .../main/resources/006-SENTRY-711.oracle.sql | 28 ++++++++++++++++++ .../main/resources/006-SENTRY-711.postgres.sql | 28 ++++++++++++++++++ .../src/main/resources/sentry-db2-1.8.0.sql | 28 ++++++++++++++++++ .../src/main/resources/sentry-derby-1.8.0.sql | 29 +++++++++++++++++++ .../src/main/resources/sentry-mysql-1.8.0.sql | 30 ++++++++++++++++++++ .../src/main/resources/sentry-oracle-1.8.0.sql | 29 +++++++++++++++++++ .../main/resources/sentry-postgres-1.8.0.sql | 29 +++++++++++++++++++ .../sentry-upgrade-db2-1.7.0-to-1.8.0.sql | 29 +++++++++++++++++++ .../sentry-upgrade-derby-1.7.0-to-1.8.0.sql | 2 ++ .../sentry-upgrade-mysql-1.7.0-to-1.8.0.sql | 1 + .../sentry-upgrade-oracle-1.7.0-to-1.8.0.sql | 1 + .../sentry-upgrade-postgres-1.7.0-to-1.8.0.sql | 1 + 14 files changed, 290 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.derby.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.derby.sql b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.derby.sql new file mode 100644 index 0000000..807a62b --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.derby.sql @@ -0,0 +1,27 @@ +CREATE TABLE SENTRY_USER +( + USER_ID BIGINT NOT NULL generated always as identity (start with 1), + CREATE_TIME BIGINT NOT NULL, + USER_NAME VARCHAR(128) +); + +ALTER TABLE SENTRY_USER ADD CONSTRAINT SENTRY_USER_PK PRIMARY KEY (USER_ID); + +CREATE UNIQUE INDEX SENTRYUSERNAME ON SENTRY_USER (USER_NAME); + +CREATE TABLE SENTRY_ROLE_USER_MAP +( + USER_ID BIGINT NOT NULL, + ROLE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_PK PRIMARY KEY (USER_ID,ROLE_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N49 ON SENTRY_ROLE_USER_MAP (USER_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N50 ON SENTRY_ROLE_USER_MAP (ROLE_ID); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK2 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID) ; + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_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/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.mysql.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.mysql.sql b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.mysql.sql new file mode 100644 index 0000000..b3a9828 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.mysql.sql @@ -0,0 +1,28 @@ +CREATE TABLE `SENTRY_USER` ( + `USER_ID` BIGINT NOT NULL, + `USER_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `CREATE_TIME` BIGINT NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_USER` + ADD CONSTRAINT `SENTRY_USER_PK` PRIMARY KEY (`USER_ID`); + +ALTER TABLE `SENTRY_USER` + ADD CONSTRAINT `SENTRY_USER_USER_NAME_UNIQUE` UNIQUE (`USER_NAME`); + +CREATE TABLE `SENTRY_ROLE_USER_MAP` ( + `ROLE_ID` BIGINT NOT NULL, + `USER_ID` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SENTRY_ROLE_USER_MAP_PK` PRIMARY KEY (`ROLE_ID`,`USER_ID`); + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SEN_ROLE_USER_MAP_SEN_ROLE_FK` + FOREIGN KEY (`ROLE_ID`) REFERENCES `SENTRY_ROLE`(`ROLE_ID`); + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SEN_ROLE_USER_MAP_SEN_USER_FK` + 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/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.oracle.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.oracle.sql b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.oracle.sql new file mode 100644 index 0000000..76ae0d5 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.oracle.sql @@ -0,0 +1,28 @@ +CREATE TABLE "SENTRY_USER" ( + "USER_ID" NUMBER NOT NULL, + "USER_NAME" VARCHAR2(128) NOT NULL, + "CREATE_TIME" NUMBER NOT NULL +); + +ALTER TABLE "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_PK" PRIMARY KEY ("USER_ID"); + +ALTER TABLE "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_USER_NAME_UNIQUE" UNIQUE ("USER_NAME"); + +CREATE TABLE "SENTRY_ROLE_USER_MAP" ( + "ROLE_ID" NUMBER NOT NULL, + "USER_ID" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR2(128) +); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SENTRY_ROLE_USER_MAP_PK" PRIMARY KEY ("ROLE_ID","USER_ID"); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") INITIALLY DEFERRED; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.postgres.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.postgres.sql b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.postgres.sql new file mode 100644 index 0000000..37e8abc --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/006-SENTRY-711.postgres.sql @@ -0,0 +1,28 @@ +CREATE TABLE "SENTRY_USER" ( + "USER_ID" BIGINT NOT NULL, + "USER_NAME" character varying(128) NOT NULL, + "CREATE_TIME" BIGINT NOT NULL +); + +ALTER TABLE ONLY "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_PK" PRIMARY KEY ("USER_ID"); + +ALTER TABLE ONLY "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_USER_NAME_UNIQUE" UNIQUE ("USER_NAME"); + +CREATE TABLE "SENTRY_ROLE_USER_MAP" ( + "ROLE_ID" BIGINT NOT NULL, + "USER_ID" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" character varying(128) +); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SENTRY_ROLE_USER_MAP_PK" PRIMARY KEY ("ROLE_ID","USER_ID"); + +ALTER TABLE ONLY "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") DEFERRABLE; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql index dd5db6a..6d08b5c 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql @@ -153,3 +153,31 @@ CREATE INDEX SENTRY_ROLE_GM_PRIVILEGE_MAP_N49 ON SENTRY_ROLE_GM_PRIVILEGE_MAP (G ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK2 FOREIGN KEY (GM_PRIVILEGE_ID) REFERENCES SENTRY_GM_PRIVILEGE (GM_PRIVILEGE_ID); ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK1 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID); + +CREATE TABLE SENTRY_USER +( + USER_ID BIGINT NOT NULL generated always as identity (start with 1), + CREATE_TIME BIGINT NOT NULL, + USER_NAME VARCHAR(128) +); + +ALTER TABLE SENTRY_USER ADD CONSTRAINT SENTRY_USER_PK PRIMARY KEY (USER_ID); + +CREATE UNIQUE INDEX SENTRYUSERNAME ON SENTRY_USER (USER_NAME); + +CREATE TABLE SENTRY_ROLE_USER_MAP +( + USER_ID BIGINT NOT NULL, + ROLE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_PK PRIMARY KEY (USER_ID,ROLE_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N49 ON SENTRY_ROLE_USER_MAP (USER_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N50 ON SENTRY_ROLE_USER_MAP (ROLE_ID); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK2 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID) ; + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_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/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql index 7e0974c..d522026 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql @@ -153,3 +153,32 @@ CREATE INDEX SENTRY_ROLE_GM_PRIVILEGE_MAP_N49 ON SENTRY_ROLE_GM_PRIVILEGE_MAP (G ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK2 FOREIGN KEY (GM_PRIVILEGE_ID) REFERENCES SENTRY_GM_PRIVILEGE (GM_PRIVILEGE_ID); ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK1 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID); + +CREATE TABLE SENTRY_USER +( + USER_ID BIGINT NOT NULL generated always as identity (start with 1), + CREATE_TIME BIGINT NOT NULL, + USER_NAME VARCHAR(128) +); + +ALTER TABLE SENTRY_USER ADD CONSTRAINT SENTRY_USER_PK PRIMARY KEY (USER_ID); + +CREATE UNIQUE INDEX SENTRYUSERNAME ON SENTRY_USER (USER_NAME); + +CREATE TABLE SENTRY_ROLE_USER_MAP +( + USER_ID BIGINT NOT NULL, + ROLE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_PK PRIMARY KEY (USER_ID,ROLE_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N49 ON SENTRY_ROLE_USER_MAP (USER_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N50 ON SENTRY_ROLE_USER_MAP (ROLE_ID); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK2 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID) ; + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK1 FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER (USER_ID) ; + http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql index d106f17..d27d7b9 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql @@ -191,3 +191,33 @@ ALTER TABLE `SENTRY_ROLE_GM_PRIVILEGE_MAP` ALTER TABLE `SENTRY_ROLE_GM_PRIVILEGE_MAP` ADD CONSTRAINT `SEN_RL_GM_PRV_MAP_SN_DB_PRV_FK` FOREIGN KEY (`GM_PRIVILEGE_ID`) REFERENCES `SENTRY_GM_PRIVILEGE`(`GM_PRIVILEGE_ID`); + +CREATE TABLE `SENTRY_USER` ( + `USER_ID` BIGINT NOT NULL, + `USER_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `CREATE_TIME` BIGINT NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_USER` + ADD CONSTRAINT `SENTRY_USER_PK` PRIMARY KEY (`USER_ID`); + +ALTER TABLE `SENTRY_USER` + ADD CONSTRAINT `SENTRY_USER_USER_NAME_UNIQUE` UNIQUE (`USER_NAME`); + +CREATE TABLE `SENTRY_ROLE_USER_MAP` ( + `ROLE_ID` BIGINT NOT NULL, + `USER_ID` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SENTRY_ROLE_USER_MAP_PK` PRIMARY KEY (`ROLE_ID`,`USER_ID`); + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SEN_ROLE_USER_MAP_SEN_ROLE_FK` + FOREIGN KEY (`ROLE_ID`) REFERENCES `SENTRY_ROLE`(`ROLE_ID`); + +ALTER TABLE `SENTRY_ROLE_USER_MAP` + ADD CONSTRAINT `SEN_ROLE_USER_MAP_SEN_USER_FK` + FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER`(`USER_ID`); + http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql index 3ad5edc..ced5c31 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql @@ -166,3 +166,32 @@ ALTER TABLE "SENTRY_ROLE_GM_PRIVILEGE_MAP" ALTER TABLE "SENTRY_ROLE_GM_PRIVILEGE_MAP" ADD CONSTRAINT "SEN_RL_GM_PRV_MAP_SN_DB_PRV_FK" FOREIGN KEY ("GM_PRIVILEGE_ID") REFERENCES "SENTRY_GM_PRIVILEGE"("GM_PRIVILEGE_ID") INITIALLY DEFERRED; + +CREATE TABLE "SENTRY_USER" ( + "USER_ID" NUMBER NOT NULL, + "USER_NAME" VARCHAR2(128) NOT NULL, + "CREATE_TIME" NUMBER NOT NULL +); + +ALTER TABLE "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_PK" PRIMARY KEY ("USER_ID"); + +ALTER TABLE "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_USER_NAME_UNIQUE" UNIQUE ("USER_NAME"); + +CREATE TABLE "SENTRY_ROLE_USER_MAP" ( + "ROLE_ID" NUMBER NOT NULL, + "USER_ID" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR2(128) +); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SENTRY_ROLE_USER_MAP_PK" PRIMARY KEY ("ROLE_ID","USER_ID"); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") INITIALLY DEFERRED; http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql index 11f214e..6d56332 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql @@ -179,4 +179,33 @@ ALTER TABLE ONLY "SENTRY_ROLE_GM_PRIVILEGE_MAP" ADD CONSTRAINT "SEN_RL_GM_PRV_MAP_SN_DB_PRV_FK" FOREIGN KEY ("GM_PRIVILEGE_ID") REFERENCES "SENTRY_GM_PRIVILEGE"("GM_PRIVILEGE_ID") DEFERRABLE; +CREATE TABLE "SENTRY_USER" ( + "USER_ID" BIGINT NOT NULL, + "USER_NAME" character varying(128) NOT NULL, + "CREATE_TIME" BIGINT NOT NULL +); + +ALTER TABLE ONLY "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_PK" PRIMARY KEY ("USER_ID"); + +ALTER TABLE ONLY "SENTRY_USER" + ADD CONSTRAINT "SENTRY_USER_USER_NAME_UNIQUE" UNIQUE ("USER_NAME"); + +CREATE TABLE "SENTRY_ROLE_USER_MAP" ( + "ROLE_ID" BIGINT NOT NULL, + "USER_ID" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" character varying(128) +); + +ALTER TABLE "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SENTRY_ROLE_USER_MAP_PK" PRIMARY KEY ("ROLE_ID","USER_ID"); + +ALTER TABLE ONLY "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_ROLE_USER_MAP" + ADD CONSTRAINT "SEN_ROLE_USER_MAP_SEN_USER_FK" + FOREIGN KEY ("USER_ID") REFERENCES "SENTRY_USER"("USER_ID") DEFERRABLE; + COMMIT; http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql index 6d4559a..927f302 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql @@ -1,2 +1,31 @@ +-- SENTRY-711 +CREATE TABLE SENTRY_USER +( + USER_ID BIGINT NOT NULL generated always as identity (start with 1), + CREATE_TIME BIGINT NOT NULL, + USER_NAME VARCHAR(128) +); + +ALTER TABLE SENTRY_USER ADD CONSTRAINT SENTRY_USER_PK PRIMARY KEY (USER_ID); + +CREATE UNIQUE INDEX SENTRYUSERNAME ON SENTRY_USER (USER_NAME); + +CREATE TABLE SENTRY_ROLE_USER_MAP +( + USER_ID BIGINT NOT NULL, + ROLE_ID BIGINT NOT NULL, + GRANTOR_PRINCIPAL VARCHAR(128) +); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_PK PRIMARY KEY (USER_ID,ROLE_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N49 ON SENTRY_ROLE_USER_MAP (USER_ID); + +CREATE INDEX SENTRY_ROLE_USER_MAP_N50 ON SENTRY_ROLE_USER_MAP (ROLE_ID); + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK2 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID) ; + +ALTER TABLE SENTRY_ROLE_USER_MAP ADD CONSTRAINT SENTRY_ROLE_USER_MAP_FK1 FOREIGN KEY (USER_ID) REFERENCES SENTRY_USER (USER_ID) ; + -- Version update UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.8.0', VERSION_COMMENT='Sentry release version 1.8.0' WHERE VER_ID=1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-1.7.0-to-1.8.0.sql index 6d4559a..fbe2dc8 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-1.7.0-to-1.8.0.sql @@ -1,2 +1,4 @@ +RUN '006-SENTRY-711.derby.sql'; + -- Version update UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.8.0', VERSION_COMMENT='Sentry release version 1.8.0' WHERE VER_ID=1; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-1.7.0-to-1.8.0.sql index 98a9056..f0df187 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-1.7.0-to-1.8.0.sql @@ -1,4 +1,5 @@ SELECT 'Upgrading Sentry store schema from 1.7.0 to 1.8.0' AS ' '; +SOURCE 006-SENTRY-711.mysql.sql; UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.8.0', VERSION_COMMENT='Sentry release version 1.8.0' WHERE VER_ID=1; http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-1.7.0-to-1.8.0.sql index 91bb82c..f1666be 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-1.7.0-to-1.8.0.sql @@ -1,4 +1,5 @@ SELECT 'Upgrading Sentry store schema from 1.7.0 to 1.8.0' AS Status from dual; [email protected]; UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.8.0', VERSION_COMMENT='Sentry release version 1.8.0' WHERE VER_ID=1; http://git-wip-us.apache.org/repos/asf/sentry/blob/6c453a09/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-1.7.0-to-1.8.0.sql index fe27bb8..b39292d 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-1.7.0-to-1.8.0.sql @@ -1,4 +1,5 @@ SELECT 'Upgrading Sentry store schema from 1.7.0 to 1.8.0'; +\i 006-SENTRY-711.postgres.sql; UPDATE "SENTRY_VERSION" SET "SCHEMA_VERSION"='1.8.0', "VERSION_COMMENT"='Sentry release version 1.8.0' WHERE "VER_ID"=1;
