Repository: incubator-sentry Updated Branches: refs/heads/db_policy_store a8cea47e9 -> 822f4bf4c
SENTRY-117: Create database schemas for mysql, postgres, and oracle (Brock via Shreepadma) Project: http://git-wip-us.apache.org/repos/asf/incubator-sentry/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-sentry/commit/822f4bf4 Tree: http://git-wip-us.apache.org/repos/asf/incubator-sentry/tree/822f4bf4 Diff: http://git-wip-us.apache.org/repos/asf/incubator-sentry/diff/822f4bf4 Branch: refs/heads/db_policy_store Commit: 822f4bf4c2240877b5bbcfffb78d41986f1cb673 Parents: a8cea47 Author: Shreepadma Venugopalan <shreepa...@apache.org> Authored: Thu Feb 20 15:50:56 2014 -0800 Committer: Shreepadma Venugopalan <shreepa...@apache.org> Committed: Thu Feb 20 15:50:56 2014 -0800 ---------------------------------------------------------------------- .../src/main/resources/sentry-mysql-1.4.0.sql | 97 ++++++++++++++++++ .../src/main/resources/sentry-oracle-1.4.0.sql | 86 ++++++++++++++++ .../main/resources/sentry-postgres-1.4.0.sql | 100 +++++++++++++++++++ 3 files changed, 283 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/822f4bf4/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql new file mode 100644 index 0000000..af38ee7 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.4.0.sql @@ -0,0 +1,97 @@ +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +CREATE TABLE `SENTRY_DB_PRIVILEGE` ( + `DB_PRIVILEGE_ID` BIGINT NOT NULL, + `PRIVILEGE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -- Name of the privilege + `PRIVILEGE_SCOPE` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -- Scope. Valid values are Server, Database, Table + `SERVER_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `DATABASE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, + `TABLE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, + `URI` VARCHAR(4000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, + `PRIVILEGE` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -- Allowed action. Valid values are ALL, INSERT, SELECT + `CREATE_TIME` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL -- principal of the creator +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `SENTRY_ROLE` ( + `ROLE_ID` BIGINT NOT NULL, + `ROLE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `CREATE_TIME` BIGINT NOT NULL, + `ROLE_OWNER` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `SENTRY_GROUP` ( + `GROUP_ID` BIGINT NOT NULL, + `GROUP_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `CREATE_TIME` BIGINT NOT NULL, + `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `SENTRY_ROLE_DB_PRIVILEGE_MAP` ( + `ROLE_PRIVILEGE_MAP_ID` BIGINT NOT NULL, + `ROLE_ID` BIGINT NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + `DB_PRIVILEGE_ID` BIGINT NOT NULL -- FK to SENTRY_DB_PRIVILEGE.DB_PRIVILEGE_ID +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `SENTRY_ROLE_GROUP_MAP` ( + `ROLE_GROUP_MAP_ID` BIGINT NOT NULL, + `ROLE_ID` BIGINT NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + `GROUP_ID` BIGINT NOT NULL -- FK to SENTRY_GROUP.GROUP_ID +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `SENTRY_VERSION` ( + `VER_ID` BIGINT NOT NULL, + `SCHEMA_VERSION` VARCHAR(127) NOT NULL, + `VERSION_COMMENT` VARCHAR(255) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `SENTRY_DB_PRIVILEGE` + ADD CONSTRAINT `SENTRY_DB_PRIV_PK` PRIMARY KEY (`DB_PRIVILEGE_ID`); + +ALTER TABLE `SENTRY_ROLE` + ADD CONSTRAINT `SENTRY_ROLE_PK` PRIMARY KEY (`ROLE_ID`); + +ALTER TABLE `SENTRY_GROUP` + ADD CONSTRAINT `SENTRY_GROUP_PK` PRIMARY KEY (`GROUP_ID`); + +ALTER TABLE `SENTRY_ROLE_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SENTRY_ROLE_DB_PRIV_MAP_PK` PRIMARY KEY (`ROLE_PRIVILEGE_MAP_ID`); + +ALTER TABLE `SENTRY_ROLE_GROUP_MAP` + ADD CONSTRAINT `SENTRY_ROLE_GROUP_MAP_PK` PRIMARY KEY (`ROLE_GROUP_MAP_ID`); + +ALTER TABLE `SENTRY_VERSION` + ADD CONSTRAINT `SENTRY_VERSION` PRIMARY KEY (`VER_ID`); + +ALTER TABLE `SENTRY_DB_PRIVILEGE` + ADD CONSTRAINT `SENTRY_DB_PRIV_PRIV_NAME_UNIQ` UNIQUE (`PRIVILEGE_NAME`); + +ALTER TABLE `SENTRY_ROLE` + ADD CONSTRAINT `SENTRY_ROLE_ROLE_NAME_UNIQUE` UNIQUE (`ROLE_NAME`); + +ALTER TABLE `SENTRY_ROLE_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_RLE_DB_PRV_MAP_SN_RLE_FK` + FOREIGN KEY (`ROLE_ID`) REFERENCES `SENTRY_ROLE`(`ROLE_ID`); + +ALTER TABLE `SENTRY_ROLE_DB_PRIVILEGE_MAP` + ADD CONSTRAINT `SEN_RL_DB_PRV_MAP_SN_DB_PRV_FK` + FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE`(`DB_PRIVILEGE_ID`); + +ALTER TABLE `SENTRY_ROLE_GROUP_MAP` + ADD CONSTRAINT `SEN_ROLE_GROUP_MAP_SEN_ROLE_FK` + FOREIGN KEY (`ROLE_ID`) REFERENCES `SENTRY_ROLE`(`ROLE_ID`); + +ALTER TABLE `SENTRY_ROLE_GROUP_MAP` + ADD CONSTRAINT `SEN_ROLE_GROUP_MAP_SEN_GRP_FK` + FOREIGN KEY (`GROUP_ID`) REFERENCES `SENTRY_GROUP`(`GROUP_ID`); + +INSERT INTO SENTRY_VERSION (VER_ID, SCHEMA_VERSION, VERSION_COMMENT) VALUES (1, '1.4.0', 'Sentry release version 1.4.0'); http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/822f4bf4/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql new file mode 100644 index 0000000..95c8907 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.4.0.sql @@ -0,0 +1,86 @@ +CREATE TABLE "SENTRY_DB_PRIVILEGE" ( + "DB_PRIVILEGE_ID" NUMBER NOT NULL, + "PRIVILEGE_NAME" VARCHAR2(128) NOT NULL, -- Name of the privilege + "PRIVILEGE_SCOPE" VARCHAR2(32) NOT NULL, -- Scope. Valid values are Server, Database, Table + "SERVER_NAME" VARCHAR2(128) NOT NULL, + "DATABASE_NAME" VARCHAR2(128) NULL, + "TABLE_NAME" VARCHAR2(128) NULL, + "URI" VARCHAR2(4000) NULL, + "PRIVILEGE" VARCHAR2(128) NOT NULL, -- Allowed action. Valid values are ALL, INSERT, SELECT + "CREATE_TIME" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR(128) NOT NULL -- principal of the creator +); + +CREATE TABLE "SENTRY_ROLE" ( + "ROLE_ID" NUMBER NOT NULL, + "ROLE_NAME" VARCHAR2(128) NOT NULL, + "CREATE_TIME" NUMBER NOT NULL, + "ROLE_OWNER" VARCHAR2(128) NOT NULL +); + +CREATE TABLE "SENTRY_GROUP" ( + "GROUP_ID" NUMBER NOT NULL, + "GROUP_NAME" VARCHAR2(128) NOT NULL, + "CREATE_TIME" NUMBER NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR2(128) NOT NULL +); + +CREATE TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP" ( + "ROLE_PRIVILEGE_MAP_ID" NUMBER NOT NULL, + "ROLE_ID" NUMBER NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + "DB_PRIVILEGE_ID" NUMBER NOT NULL -- FK to SENTRY_DB_PRIVILEGE.DB_PRIVILEGE_ID +); + +CREATE TABLE "SENTRY_ROLE_GROUP_MAP" ( + "ROLE_GROUP_MAP_ID" NUMBER NOT NULL, + "ROLE_ID" NUMBER NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + "GROUP_ID" NUMBER NOT NULL -- FK to SENTRY_GROUP.GROUP_ID +); + +CREATE TABLE "SENTRY_VERSION" ( + "VER_ID" NUMBER NOT NULL, + "SCHEMA_VERSION" VARCHAR(127) NOT NULL, + "VERSION_COMMENT" VARCHAR(255) NOT NULL +); + +ALTER TABLE "SENTRY_DB_PRIVILEGE" + ADD CONSTRAINT "SENTRY_DB_PRIV_PK" PRIMARY KEY ("DB_PRIVILEGE_ID"); + +ALTER TABLE "SENTRY_ROLE" + ADD CONSTRAINT "SENTRY_ROLE_PK" PRIMARY KEY ("ROLE_ID"); + +ALTER TABLE "SENTRY_GROUP" + ADD CONSTRAINT "SENTRY_GROUP_PK" PRIMARY KEY ("GROUP_ID"); + +ALTER TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SENTRY_ROLE_DB_PRIV_MAP_PK" PRIMARY KEY ("ROLE_PRIVILEGE_MAP_ID"); + +ALTER TABLE "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SENTRY_ROLE_GROUP_MAP_PK" PRIMARY KEY ("ROLE_GROUP_MAP_ID"); + +ALTER TABLE "SENTRY_VERSION" ADD CONSTRAINT "SENTRY_VERSION_PK" PRIMARY KEY ("VER_ID"); + +ALTER TABLE "SENTRY_DB_PRIVILEGE" + ADD CONSTRAINT "SENTRY_DB_PRIV_PRIV_NAME_UNIQ" UNIQUE ("PRIVILEGE_NAME"); + +ALTER TABLE "SENTRY_ROLE" + ADD CONSTRAINT "SENTRY_ROLE_ROLE_NAME_UNIQUE" UNIQUE ("ROLE_NAME"); + +ALTER TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_RLE_DB_PRV_MAP_SN_RLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_RL_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") INITIALLY DEFERRED; + +ALTER TABLE "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_GRP_FK" + FOREIGN KEY ("GROUP_ID") REFERENCES "SENTRY_GROUP"("GROUP_ID") INITIALLY DEFERRED; + +INSERT INTO VERSION (VER_ID, SCHEMA_VERSION, VERSION_COMMENT) VALUES (1, '1.4.0', 'Sentry release version 1.4.0'); + http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/822f4bf4/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql new file mode 100644 index 0000000..9dd0a00 --- /dev/null +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.4.0.sql @@ -0,0 +1,100 @@ +START TRANSACTION; + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = off; +SET check_function_bodies = false; +SET client_min_messages = warning; +SET escape_string_warning = off; +SET search_path = public, pg_catalog; +SET default_tablespace = ''; +SET default_with_oids = false; + +CREATE TABLE "SENTRY_DB_PRIVILEGE" ( + "DB_PRIVILEGE_ID" BIGINT NOT NULL, + "PRIVILEGE_NAME" character varying(128) NOT NULL, -- Name of the privilege + "PRIVILEGE_SCOPE" character varying(32) NOT NULL, -- Scope. Valid values are Server, Database, Table + "SERVER_NAME" character varying(128) NOT NULL, + "DATABASE_NAME" character varying(128) DEFAULT NULL::character varying, + "TABLE_NAME" character varying(128) DEFAULT NULL::character varying, + "URI" character varying(4000) DEFAULT NULL::character varying, + "PRIVILEGE" character varying(128) NOT NULL, -- Allowed action. Valid values are ALL, INSERT, SELECT + "CREATE_TIME" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" VARCHAR(128) NOT NULL -- principal of the creator +); + +CREATE TABLE "SENTRY_ROLE" ( + "ROLE_ID" BIGINT NOT NULL, + "ROLE_NAME" character varying(128) NOT NULL, + "CREATE_TIME" BIGINT NOT NULL, + "ROLE_OWNER" character varying(128) NOT NULL +); + +CREATE TABLE "SENTRY_GROUP" ( + "GROUP_ID" BIGINT NOT NULL, + "GROUP_NAME" character varying(128) NOT NULL, + "CREATE_TIME" BIGINT NOT NULL, + "GRANTOR_PRINCIPAL" character varying(128) NOT NULL +); + +CREATE TABLE "SENTRY_ROLE_DB_PRIVILEGE_MAP" ( + "ROLE_PRIVILEGE_MAP_ID" BIGINT NOT NULL, + "ROLE_ID" BIGINT NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + "DB_PRIVILEGE_ID" BIGINT NOT NULL -- FK to SENTRY_DB_PRIVILEGE.DB_PRIVILEGE_ID +); + +CREATE TABLE "SENTRY_ROLE_GROUP_MAP" ( + "ROLE_GROUP_MAP_ID" BIGINT NOT NULL, + "ROLE_ID" BIGINT NOT NULL, -- FK to SENTRY_ROLE.ROLE_ID + "GROUP_ID" BIGINT NOT NULL -- FK to SENTRY_GROUP.GROUP_ID +); + +CREATE TABLE "SENTRY_VERSION" ( + "VER_ID" bigint, + "SCHEMA_VERSION" character varying(127) NOT NULL, + "VERSION_COMMENT" character varying(255) NOT NULL +); + + +ALTER TABLE ONLY "SENTRY_DB_PRIVILEGE" + ADD CONSTRAINT "SENTRY_DB_PRIV_PK" PRIMARY KEY ("DB_PRIVILEGE_ID"); + +ALTER TABLE ONLY "SENTRY_ROLE" + ADD CONSTRAINT "SENTRY_ROLE_PK" PRIMARY KEY ("ROLE_ID"); + +ALTER TABLE ONLY "SENTRY_GROUP" + ADD CONSTRAINT "SENTRY_GROUP_PK" PRIMARY KEY ("GROUP_ID"); + +ALTER TABLE ONLY "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SENTRY_ROLE_DB_PRIV_MAP_PK" PRIMARY KEY ("ROLE_PRIVILEGE_MAP_ID"); + +ALTER TABLE ONLY "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SENTRY_ROLE_GROUP_MAP_PK" PRIMARY KEY ("ROLE_GROUP_MAP_ID"); + +ALTER TABLE ONLY "SENTRY_VERSION" ADD CONSTRAINT "SENTRY_VERSION_PK" PRIMARY KEY ("VER_ID"); + +ALTER TABLE ONLY "SENTRY_DB_PRIVILEGE" + ADD CONSTRAINT "SENTRY_DB_PRIV_PRIV_NAME_UNIQ" UNIQUE ("PRIVILEGE_NAME"); + +ALTER TABLE ONLY "SENTRY_ROLE" + ADD CONSTRAINT "SENTRY_ROLE_ROLE_NAME_UNIQUE" UNIQUE ("ROLE_NAME"); + +ALTER TABLE ONLY "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_RLE_DB_PRV_MAP_SN_RLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_ROLE_DB_PRIVILEGE_MAP" + ADD CONSTRAINT "SEN_RL_DB_PRV_MAP_SN_DB_PRV_FK" + FOREIGN KEY ("DB_PRIVILEGE_ID") REFERENCES "SENTRY_DB_PRIVILEGE"("DB_PRIVILEGE_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_ROLE_FK" + FOREIGN KEY ("ROLE_ID") REFERENCES "SENTRY_ROLE"("ROLE_ID") DEFERRABLE; + +ALTER TABLE ONLY "SENTRY_ROLE_GROUP_MAP" + ADD CONSTRAINT "SEN_ROLE_GROUP_MAP_SEN_GRP_FK" + FOREIGN KEY ("GROUP_ID") REFERENCES "SENTRY_GROUP"("GROUP_ID") DEFERRABLE; + +INSERT INTO "SENTRY_VERSION" ("VER_ID", "SCHEMA_VERSION", "VERSION_COMMENT") VALUES (1, '1.4.0', 'Sentry release version 1.4.0'); + +COMMIT;
