You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@sentry.apache.org by sh...@apache.org on 2014/02/21 00:51:21 UTC
git commit: SENTRY-117: Create database schemas for mysql, postgres,
and oracle (Brock via Shreepadma)
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 <sh...@apache.org>
Authored: Thu Feb 20 15:50:56 2014 -0800
Committer: Shreepadma Venugopalan <sh...@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;