RANGER-645: updates to support all DB flavors for TagDBStore Signed-off-by: Madhan Neethiraj <[email protected]>
Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/f5245892 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/f5245892 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/f5245892 Branch: refs/heads/tag-policy Commit: f52458921ca47e53331ef2d14acb66200c90aedf Parents: e250f02 Author: Gautam Borad <[email protected]> Authored: Fri Sep 25 13:43:57 2015 -0700 Committer: Madhan Neethiraj <[email protected]> Committed: Fri Sep 25 14:01:18 2015 -0700 ---------------------------------------------------------------------- .../main/java/org/apache/util/sql/Jisql.java | 6 + security-admin/db/mysql/init/schema_mysql.sql | 1 - .../016-updated-schema-for-tag-based-policy.sql | 100 ++-- .../patches/audit/011-auditcolumnssize.sql | 11 +- .../patches/audit/015-auditlogaggregation.sql | 20 +- security-admin/db/mysql/xa_audit_db.sql | 1 - security-admin/db/mysql/xa_db.sql | 1 - .../db/oracle/patches/009-updated_schema.sql | 5 +- .../016-updated-schema-for-tag-based-policy.sql | 239 ++++++++ .../patches/audit/011-auditcolumnssize.sql | 14 +- .../patches/audit/015-auditlogaggregation.sql | 34 +- .../audit/017-add-new-column-to-store-tags.sql | 28 + security-admin/db/oracle/xa_audit_db_oracle.sql | 1 - security-admin/db/oracle/xa_core_db_oracle.sql | 1 - .../016-updated-schema-for-tag-based-policy.sql | 257 +++++++++ .../audit/017-add-new-column-to-store-tags.sql | 30 + .../db/postgres/xa_audit_db_postgres.sql | 1 - .../db/postgres/xa_core_db_postgres.sql | 6 +- .../016-updated-schema-for-tag-based-policy.sql | 233 ++++++++ .../audit/017-add-new-column-to-store-tags.sql | 20 + .../016-updated-schema-for-tag-based-policy.sql | 575 +++++++++++++++++++ .../audit/017-add-new-column-to-store-tags.sql | 21 + .../db/sqlserver/xa_audit_db_sqlserver.sql | 1 - .../db/sqlserver/xa_core_db_sqlserver.sql | 13 - security-admin/scripts/db_setup.py | 44 +- .../apache/ranger/entity/XXServiceDefBase.java | 20 +- .../apache/ranger/entity/XXServiceResource.java | 4 +- .../ranger/entity/XXServiceResourceElement.java | 4 +- .../entity/XXServiceResourceElementValue.java | 4 +- .../java/org/apache/ranger/entity/XXTag.java | 4 +- .../apache/ranger/entity/XXTagAttribute.java | 4 +- .../apache/ranger/entity/XXTagAttributeDef.java | 4 +- .../java/org/apache/ranger/entity/XXTagDef.java | 4 +- .../apache/ranger/entity/XXTagResourceMap.java | 4 +- .../service/RangerServiceDefServiceBase.java | 4 +- 35 files changed, 1531 insertions(+), 188 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/jisql/src/main/java/org/apache/util/sql/Jisql.java ---------------------------------------------------------------------- diff --git a/jisql/src/main/java/org/apache/util/sql/Jisql.java b/jisql/src/main/java/org/apache/util/sql/Jisql.java index cf7563c..cc1be2a 100644 --- a/jisql/src/main/java/org/apache/util/sql/Jisql.java +++ b/jisql/src/main/java/org/apache/util/sql/Jisql.java @@ -430,6 +430,12 @@ public class Jisql { continue; } } + if(connectString.toLowerCase().startsWith("jdbc:postgresql") && inputFileName!=null){ + if (trimmedLine.toLowerCase().startsWith("select 'delimiter start';")) { + commandTerminator="select 'delimiter end';"; + continue; + } + } if (line.trim().equalsIgnoreCase(commandTerminator) || line.trim().endsWith(commandTerminator)) { if (line.trim().endsWith(commandTerminator)) { http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/init/schema_mysql.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/init/schema_mysql.sql b/security-admin/db/mysql/init/schema_mysql.sql index 6df7195..1dd7420 100644 --- a/security-admin/db/mysql/init/schema_mysql.sql +++ b/security-admin/db/mysql/init/schema_mysql.sql @@ -289,7 +289,6 @@ create table xa_access_audit ( request_data VARCHAR (2000) , resource_path VARCHAR (2000) , resource_type VARCHAR (255) , - tags VARCHAR (2000) , PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql index 532e723..c5b813f 100644 --- a/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql +++ b/security-admin/db/mysql/patches/016-updated-schema-for-tag-based-policy.sql @@ -27,13 +27,13 @@ DROP TABLE IF EXISTS `x_tag_def` ; CREATE TABLE IF NOT EXISTS `x_tag_def` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `guid` VARCHAR(64) NOT NULL, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, -`version` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, +`version` BIGINT(20) NULL DEFAULT NULL, `name` VARCHAR(255) NOT NULL, -`source` VARCHAR(128) DEFAULT NULL, +`source` VARCHAR(128) NULL DEFAULT NULL, `is_enabled` TINYINT(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `x_tag_def_UK_guid` (`guid`), @@ -49,11 +49,11 @@ CONSTRAINT `x_tag_def_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_port CREATE TABLE IF NOT EXISTS `x_tag` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `guid` VARCHAR(64) NOT NULL, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, -`version` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, +`version` BIGINT(20) NULL DEFAULT NULL, `type` BIGINT(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `x_tag_UK_guid` (`guid`), @@ -70,16 +70,16 @@ CONSTRAINT `x_tag_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_u CREATE TABLE IF NOT EXISTS `x_service_resource` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `guid` VARCHAR(64) NOT NULL, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, -`version` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, +`version` BIGINT(20) NULL DEFAULT NULL, `service_id` BIGINT(20) NOT NULL, -`resource_signature` varchar(128) DEFAULT NULL, +`resource_signature` varchar(128) NULL DEFAULT NULL, `is_enabled` TINYINT NOT NULL DEFAULT '1', PRIMARY KEY (`id`), -UNIQUE KEY `x_service_resource_UK_guid` (`guid`), +UNIQUE KEY `x_service_res_UK_guid` (`guid`), KEY `x_service_res_IDX_added_by_id` (`added_by_id`), KEY `x_service_res_IDX_upd_by_id` (`upd_by_id`), CONSTRAINT `x_service_res_FK_service_id` FOREIGN KEY (`service_id`) REFERENCES `x_service` (`id`), @@ -91,10 +91,10 @@ CONSTRAINT `x_service_res_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_ -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `x_service_resource_element` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, `res_id` BIGINT(20) NOT NULL, `res_def_id` BIGINT(20) NOT NULL, `is_excludes` TINYINT(1) NOT NULL DEFAULT '0', @@ -112,13 +112,13 @@ CONSTRAINT `x_srvc_res_el_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_ -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `x_tag_attr_def` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, `tag_def_id` BIGINT(20) NOT NULL, `name` VARCHAR(255) NOT NULL, -`type` VARCHAR(45) NOT NULL, +`type` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), KEY `x_tag_attr_def_IDX_tag_def_id` (`tag_def_id`), KEY `x_tag_attr_def_IDX_added_by_id` (`added_by_id`), @@ -132,10 +132,10 @@ CONSTRAINT `x_tag_attr_def_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `x_tag_attr` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, `tag_id` BIGINT(20) NOT NULL, `name` VARCHAR(255) NOT NULL, `value` VARCHAR(512) NULL, @@ -153,14 +153,14 @@ CONSTRAINT `x_tag_attr_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_por CREATE TABLE IF NOT EXISTS `x_tag_resource_map` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `guid` VARCHAR(64) NOT NULL, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, `tag_id` BIGINT(20) NOT NULL, `res_id` BIGINT(20) NOT NULL, PRIMARY KEY (`id`), -UNIQUE KEY `x_tag_resource_map_UK_guid` (`guid`), +UNIQUE KEY `x_tag_res_map_UK_guid` (`guid`), KEY `x_tag_res_map_IDX_tag_id` (`tag_id`), KEY `x_tag_res_map_IDX_res_id` (`res_id`), KEY `x_tag_res_map_IDX_added_by_id` (`added_by_id`), @@ -175,13 +175,13 @@ CONSTRAINT `x_tag_res_map_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_ -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `x_service_resource_element_val` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME DEFAULT NULL, -`update_time` DATETIME DEFAULT NULL, -`added_by_id` BIGINT(20) DEFAULT NULL, -`upd_by_id` BIGINT(20) DEFAULT NULL, +`create_time` DATETIME NULL DEFAULT NULL, +`update_time` DATETIME NULL DEFAULT NULL, +`added_by_id` BIGINT(20) NULL DEFAULT NULL, +`upd_by_id` BIGINT(20) NULL DEFAULT NULL, `res_element_id` BIGINT(20) NOT NULL, `value` VARCHAR(1024) NOT NULL, -`sort_order` tinyint(3) DEFAULT '0', +`sort_order` tinyint(3) NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `x_srvc_res_el_val_IDX_resel_id` (`res_element_id`), KEY `x_srvc_res_el_val_IDX_addby_id` (`added_by_id`), @@ -190,23 +190,23 @@ CONSTRAINT `x_srvc_res_el_val_FK_res_el_id` FOREIGN KEY (`res_element_id`) REFER CONSTRAINT `x_srvc_res_el_val_FK_add_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_srvc_res_el_val_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) ); --- ------------------------------------- --- add column in x_service_def.options --- ------------------------------------- -DROP PROCEDURE IF EXISTS add_column_x_service_def_options; +-- --------------------------------------- +-- add column in x_service_def.def_options +-- --------------------------------------- +DROP PROCEDURE IF EXISTS add_columns_x_service_def; DELIMITER ;; -CREATE PROCEDURE add_column_x_service_def_options() BEGIN +CREATE PROCEDURE add_columns_x_service_def() BEGIN IF EXISTS (SELECT * FROM information_schema.tables WHERE table_schema=database() AND table_name = 'x_service_def') THEN - IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service_def' AND column_name = 'options') THEN - ALTER TABLE `x_service_def` ADD COLUMN `options` VARCHAR(1024) DEFAULT NULL NULL; + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=database() AND table_name = 'x_service_def' AND column_name = 'def_options') THEN + ALTER TABLE `x_service_def` ADD COLUMN `def_options` VARCHAR(1024) DEFAULT NULL NULL; END IF; END IF; END;; DELIMITER ; -CALL add_column_x_service_def_options(); -DROP PROCEDURE IF EXISTS add_column_x_service_def_options; +CALL add_columns_x_service_def(); +DROP PROCEDURE IF EXISTS add_columns_x_service_def; -- --------------------------------------------------------------------------------------- -- add column in x_policy_item.item_type, x_policy_item.is_enabled, x_policy_item.comments http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql b/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql index b38ae8c..a8c7b81 100644 --- a/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql +++ b/security-admin/db/mysql/patches/audit/011-auditcolumnssize.sql @@ -18,14 +18,9 @@ drop procedure if exists increase_column_size_of_xa_access_audit_table; delimiter ;; create procedure increase_column_size_of_xa_access_audit_table() begin - /* change request_data data size from 2000 to 4000 */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'request_data' and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then - ALTER TABLE `xa_access_audit` CHANGE `request_data` `request_data` VARCHAR(4000) NULL DEFAULT NULL ; - end if; - - /* change resource_path data size from 2000 to 4000 */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'resource_path' and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then - ALTER TABLE `xa_access_audit` CHANGE `resource_path` `resource_path` VARCHAR(4000) NULL DEFAULT NULL ; + /* change request_data and resource_path column size from 2000 to 4000 */ + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and (column_name = 'request_data' or column_name = 'resource_path') and data_type='varchar' and CHARACTER_MAXIMUM_LENGTH=2000) then + ALTER TABLE `xa_access_audit` CHANGE `request_data` `request_data` VARCHAR(4000) NULL DEFAULT NULL,CHANGE `resource_path` `resource_path` VARCHAR(4000) NULL DEFAULT NULL; end if; end;; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql b/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql index c88c5b4..7990f86 100644 --- a/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql +++ b/security-admin/db/mysql/patches/audit/015-auditlogaggregation.sql @@ -20,19 +20,11 @@ create procedure add_columns_to_support_audit_log_aggregation() begin if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'seq_num') then - ALTER TABLE `xa_access_audit` ADD `seq_num` bigint NULL DEFAULT 0; - end if; - end if; - - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then - if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_count') then - ALTER TABLE `xa_access_audit` ADD `event_count` bigint NULL DEFAULT 1; - end if; - end if; - - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit') then - if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_dur_ms') then - ALTER TABLE `xa_access_audit` ADD `event_dur_ms` bigint NULL DEFAULT 1; + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_count') then + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'xa_access_audit' and column_name = 'event_dur_ms') then + ALTER TABLE `xa_access_audit` ADD `seq_num` bigint NULL DEFAULT 0,ADD `event_count` bigint NULL DEFAULT 1,ADD `event_dur_ms` bigint NULL DEFAULT 1; + end if; + end if; end if; end if; @@ -41,4 +33,4 @@ end;; delimiter ; call add_columns_to_support_audit_log_aggregation(); -drop procedure if exists add_columns_to_support_audit_log_aggregation; \ No newline at end of file +drop procedure if exists add_columns_to_support_audit_log_aggregation; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/xa_audit_db.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/xa_audit_db.sql b/security-admin/db/mysql/xa_audit_db.sql index 8334dce..163d7b6 100644 --- a/security-admin/db/mysql/xa_audit_db.sql +++ b/security-admin/db/mysql/xa_audit_db.sql @@ -61,7 +61,6 @@ CREATE TABLE `xa_access_audit` ( `request_data` varchar(4000) DEFAULT NULL, `resource_path` varchar(4000) DEFAULT NULL, `resource_type` varchar(255) DEFAULT NULL, - PRIMARY KEY (`id`), KEY `xa_access_audit_added_by_id` (`added_by_id`), KEY `xa_access_audit_upd_by_id` (`upd_by_id`), http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/mysql/xa_db.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/xa_db.sql b/security-admin/db/mysql/xa_db.sql index 9aba91f..4b2919f 100644 --- a/security-admin/db/mysql/xa_db.sql +++ b/security-admin/db/mysql/xa_db.sql @@ -722,7 +722,6 @@ CREATE TABLE `xa_access_audit` ( `request_data` varchar(2000) DEFAULT NULL, `resource_path` varchar(2000) DEFAULT NULL, `resource_type` varchar(255) DEFAULT NULL, - `tags` varchar(2000) DEFAULT NULL, PRIMARY KEY (`id`), KEY `xa_access_audit_FK_added_by_id` (`added_by_id`), KEY `xa_access_audit_FK_upd_by_id` (`upd_by_id`), http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/patches/009-updated_schema.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/009-updated_schema.sql b/security-admin/db/oracle/patches/009-updated_schema.sql index ed47237..710bca4 100644 --- a/security-admin/db/oracle/patches/009-updated_schema.sql +++ b/security-admin/db/oracle/patches/009-updated_schema.sql @@ -46,7 +46,6 @@ name VARCHAR(1024) DEFAULT NULL NULL, impl_class_name VARCHAR(1024) DEFAULT NULL NULL, label VARCHAR(1024) DEFAULT NULL NULL, description VARCHAR(1024) DEFAULT NULL NULL, -options VARCHAR(1024) DEFAULT NULL NULL, rb_key_label VARCHAR(1024) DEFAULT NULL NULL, rb_key_description VARCHAR(1024) DEFAULT NULL NULL, is_enabled NUMBER(1) DEFAULT '1' NULL, @@ -69,13 +68,11 @@ policy_version NUMBER(20) DEFAULT NULL NULL, policy_update_time DATE DEFAULT NULL NULL, description VARCHAR(1024) DEFAULT NULL NULL, is_enabled NUMBER(1) DEFAULT '0' NOT NULL, -tag_service NUMBER(20) DEFAULT NULL NULL, primary key (id), CONSTRAINT x_service_name UNIQUE (name), CONSTRAINT x_service_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), CONSTRAINT x_service_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id), -CONSTRAINT x_service_FK_type FOREIGN KEY (type) REFERENCES x_service_def (id), -CONSTRAINT x_service_FK_tag_service FOREIGN KEY (tag_service) REFERENCES x_service (id) +CONSTRAINT x_service_FK_type FOREIGN KEY (type) REFERENCES x_service_def (id) ); commit; CREATE TABLE x_policy ( http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql new file mode 100644 index 0000000..1bc8921 --- /dev/null +++ b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql @@ -0,0 +1,239 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +/ +CREATE SEQUENCE X_TAG_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_tag_def ( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +version NUMBER(20) DEFAULT NULL NULL, +name VARCHAR(255) NOT NULL, +source VARCHAR(128) DEFAULT NULL NULL, +is_enabled NUMBER(1) DEFAULT '0' NOT NULL, +PRIMARY KEY (id), +CONSTRAINT x_tag_def_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_def_UK_name UNIQUE (name), +CONSTRAINT x_tag_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_def_IDX_added_by_id ON x_tag_def(added_by_id); +CREATE INDEX x_tag_def_IDX_upd_by_id ON x_tag_def(upd_by_id); +commit; +CREATE SEQUENCE X_TAG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_tag( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +version NUMBER(20) DEFAULT NULL NULL, +type NUMBER(20) NOT NULL, +primary key (id), +CONSTRAINT x_tag_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_FK_type FOREIGN KEY (type) REFERENCES x_tag_def (id), +CONSTRAINT x_tag_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_IDX_type ON x_tag(type); +CREATE INDEX x_tag_IDX_added_by_id ON x_tag(added_by_id); +CREATE INDEX x_tag_IDX_upd_by_id ON x_tag(upd_by_id); +commit; +CREATE SEQUENCE X_SERVICE_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_service_resource( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +version NUMBER(20) DEFAULT NULL NULL, +service_id NUMBER(20) NOT NULL, +resource_signature VARCHAR(128) DEFAULT NULL NULL, +is_enabled NUMBER(1) DEFAULT '1' NOT NULL, +primary key (id), +CONSTRAINT x_service_res_UK_guid UNIQUE (guid), +CONSTRAINT x_service_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id), +CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_service_res_IDX_added_by_id ON x_service_resource(added_by_id); +CREATE INDEX x_service_res_IDX_upd_by_id ON x_service_resource(upd_by_id); +commit; +CREATE SEQUENCE X_SERVICE_RESOURCE_ELEMENT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_service_resource_element( +id NUMBER(20) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +res_id NUMBER(20) NOT NULL, +res_def_id NUMBER(20) NOT NULL, +is_excludes NUMBER(1) DEFAULT '0' NOT NULL, +is_recursive NUMBER(1) DEFAULT '0' NOT NULL, +primary key (id), +CONSTRAINT x_srvc_res_el_FK_res_def_id FOREIGN KEY (res_def_id) REFERENCES x_resource_def (id), +CONSTRAINT x_srvc_res_el_FK_res_id FOREIGN KEY (res_id) REFERENCES x_service_resource (id), +CONSTRAINT x_srvc_res_el_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_srvc_res_el_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_srvc_res_el_IDX_added_by_id ON x_service_resource_element(added_by_id); +CREATE INDEX x_srvc_res_el_IDX_upd_by_id ON x_service_resource_element(upd_by_id); +commit; +CREATE SEQUENCE X_TAG_ATTR_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_tag_attr_def( +id NUMBER(20) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +tag_def_id NUMBER(20) NOT NULL, +name VARCHAR(255) NOT NULL, +type VARCHAR(50) NOT NULL, +primary key (id), +CONSTRAINT x_tag_attr_def_FK_tag_def_id FOREIGN KEY (tag_def_id) REFERENCES x_tag_def (id), +CONSTRAINT x_tag_attr_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_attr_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_attr_def_IDX_tag_def_id ON x_tag_attr_def(tag_def_id); +CREATE INDEX x_tag_attr_def_IDX_added_by_id ON x_tag_attr_def(added_by_id); +CREATE INDEX x_tag_attr_def_IDX_upd_by_id ON x_tag_attr_def(upd_by_id); +commit; +CREATE SEQUENCE X_TAG_ATTR_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_tag_attr( +id NUMBER(20) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +tag_id NUMBER(20) NOT NULL, +name VARCHAR(255) NOT NULL, +value VARCHAR(512) DEFAULT NULL NULL, +primary key (id), +CONSTRAINT x_tag_attr_FK_tag_id FOREIGN KEY (tag_id) REFERENCES x_tag (id), +CONSTRAINT x_tag_attr_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_attr_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_attr_IDX_tag_id ON x_tag_attr(tag_id); +CREATE INDEX x_tag_attr_IDX_added_by_id ON x_tag_attr(added_by_id); +CREATE INDEX x_tag_attr_IDX_upd_by_id ON x_tag_attr(upd_by_id); +commit; +CREATE SEQUENCE X_TAG_RESOURCE_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_tag_resource_map( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +tag_id NUMBER(20) NOT NULL, +res_id NUMBER(20) NOT NULL, +primary key (id), +CONSTRAINT x_tag_res_map_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_res_map_FK_tag_id FOREIGN KEY (tag_id) REFERENCES x_tag (id), +CONSTRAINT x_tag_res_map_FK_res_id FOREIGN KEY (res_id) REFERENCES x_service_resource (id), +CONSTRAINT x_tag_res_map_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_res_map_IDX_tag_id ON x_tag_resource_map(tag_id); +CREATE INDEX x_tag_res_map_IDX_res_id ON x_tag_resource_map(res_id); +CREATE INDEX x_tag_res_map_IDX_added_by_id ON x_tag_resource_map(added_by_id); +CREATE INDEX x_tag_res_map_IDX_upd_by_id ON x_tag_resource_map(upd_by_id); +commit; +CREATE SEQUENCE X_SERVICE_RES_EL_VAL_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_service_resource_element_val( +id NUMBER(20) NOT NULL, +create_time DATE DEFAULT NULL NULL, +update_time DATE DEFAULT NULL NULL, +added_by_id NUMBER(20) DEFAULT NULL NULL, +upd_by_id NUMBER(20) DEFAULT NULL NULL, +res_element_id NUMBER(20) NOT NULL, +value VARCHAR(1024) NOT NULL, +sort_order NUMBER(3) DEFAULT '0' NULL, +primary key (id), +CONSTRAINT x_srvc_res_el_val_FK_res_el_id FOREIGN KEY (res_element_id) REFERENCES x_service_resource_element (id), +CONSTRAINT x_srvc_res_el_val_FK_add_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_srvc_res_el_val_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_srvc_res_el_val_IDX_resel_id ON x_service_resource_element_val(res_element_id); +CREATE INDEX x_srvc_res_el_val_IDX_addby_id ON x_service_resource_element_val(added_by_id); +CREATE INDEX x_srvc_res_el_val_IDX_updby_id ON x_service_resource_element_val(upd_by_id); +INSERT INTO x_modules_master VALUES(X_MODULES_MASTER_SEQ.NEXTVAL,SYSDATE,SYSDATE,1,1,'Tag Based Policies',''); + +DECLARE + v_column_exists number := 0; +BEGIN + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('def_options') + and table_name = upper('x_service_def'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE x_service_def ADD def_options VARCHAR(1024) DEFAULT NULL NULL'; + commit; + end if; +end;/ + +DECLARE + v_column_exists number := 0; +BEGIN + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('item_type') + and table_name = upper('x_policy_item'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE x_policy_item ADD item_type NUMBER(10) DEFAULT 0 NOT NULL'; + commit; + end if; + + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('is_enabled') + and table_name = upper('x_policy_item'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE x_policy_item ADD is_enabled NUMBER(1) DEFAULT 1 NOT NULL'; + commit; + end if; + + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('comments') + and table_name = upper('x_policy_item'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE x_policy_item ADD comments VARCHAR(255) DEFAULT NULL NULL'; + commit; + end if; +end;/ + +DECLARE + v_column_exists number := 0; +BEGIN + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('tag_service') + and table_name = upper('x_service'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE x_service ADD (tag_service NUMBER(20) DEFAULT NULL NULL,tag_version NUMBER(20) DEFAULT 0 NOT NULL,tag_update_time DATE DEFAULT NULL NULL)'; + commit; + end if; +end;/ +commit; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql b/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql index 35a9320..ad60187 100644 --- a/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql +++ b/security-admin/db/oracle/patches/audit/011-auditcolumnssize.sql @@ -19,21 +19,11 @@ DECLARE BEGIN Select count(*) into v_column_exists from user_tab_cols - where column_name = upper('REQUEST_DATA') + where (column_name = upper('REQUEST_DATA') or column_name = upper('RESOURCE_PATH')) and table_name = upper('XA_ACCESS_AUDIT') and DATA_TYPE='VARCHAR2' and DATA_LENGTH=2000; if (v_column_exists = 1) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(REQUEST_DATA VARCHAR(4000) DEFAULT NULL)'; - commit; - end if; - v_column_exists:=0; - Select count(*) into v_column_exists - from user_tab_cols - where column_name = upper('RESOURCE_PATH') - and table_name = upper('XA_ACCESS_AUDIT') and DATA_TYPE='VARCHAR2' and DATA_LENGTH=2000; - - if (v_column_exists = 1) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(RESOURCE_PATH VARCHAR(4000) DEFAULT NULL)'; + execute immediate 'ALTER TABLE XA_ACCESS_AUDIT modify(REQUEST_DATA VARCHAR(4000) DEFAULT NULL,RESOURCE_PATH VARCHAR(4000) DEFAULT NULL)'; commit; end if; end;/ \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql b/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql index 756ee61..5c099e8 100644 --- a/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql +++ b/security-admin/db/oracle/patches/audit/015-auditlogaggregation.sql @@ -14,43 +14,27 @@ -- limitations under the License. DECLARE - v_column_exists number := 0; + v_column1_exists number := 0; + v_column2_exists number := 0; + v_column3_exists number := 0; BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column1_exists from user_tab_cols where column_name = upper('seq_num') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD seq_num NUMBER(20) DEFAULT 0 NULL'; - commit; - end if; -end;/ - -DECLARE - v_column_exists number := 0; -BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column2_exists from user_tab_cols where column_name = upper('event_count') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD event_count NUMBER(20) DEFAULT 1 NULL'; - commit; - end if; -end;/ - -DECLARE - v_column_exists number := 0; -BEGIN - Select count(*) into v_column_exists + Select count(*) into v_column3_exists from user_tab_cols where column_name = upper('event_dur_ms') and table_name = upper('XA_ACCESS_AUDIT'); - if (v_column_exists = 0) then - execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD event_dur_ms NUMBER(20) DEFAULT 1 NULL'; + if (v_column1_exists = 0) AND (v_column2_exists = 0) AND (v_column3_exists = 0) then + execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD (seq_num NUMBER(20) DEFAULT 0 NULL,event_count NUMBER(20) DEFAULT 1 NULL,event_dur_ms NUMBER(20) DEFAULT 1 NULL)'; commit; end if; -end;/ \ No newline at end of file +end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/patches/audit/017-add-new-column-to-store-tags.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/audit/017-add-new-column-to-store-tags.sql b/security-admin/db/oracle/patches/audit/017-add-new-column-to-store-tags.sql new file mode 100644 index 0000000..e8dca5f --- /dev/null +++ b/security-admin/db/oracle/patches/audit/017-add-new-column-to-store-tags.sql @@ -0,0 +1,28 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +DECLARE + v_column_exists number := 0; +BEGIN + Select count(*) into v_column_exists + from user_tab_cols + where column_name = upper('tags') + and table_name = upper('XA_ACCESS_AUDIT'); + + if (v_column_exists = 0) then + execute immediate 'ALTER TABLE XA_ACCESS_AUDIT ADD tags VARCHAR(4000) DEFAULT NULL NULL'; + commit; + end if; +end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/xa_audit_db_oracle.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/xa_audit_db_oracle.sql b/security-admin/db/oracle/xa_audit_db_oracle.sql index 607a8b2..67a99be 100644 --- a/security-admin/db/oracle/xa_audit_db_oracle.sql +++ b/security-admin/db/oracle/xa_audit_db_oracle.sql @@ -39,7 +39,6 @@ CREATE TABLE xa_access_audit ( request_data VARCHAR(4000) DEFAULT NULL NULL , resource_path VARCHAR(4000) DEFAULT NULL NULL , resource_type VARCHAR(255) DEFAULT NULL NULL , - tags VARCHAR(4000) DEFAULT NULL NULL , PRIMARY KEY (id) ); CREATE INDEX xa_access_audit_added_by_id ON xa_access_audit(added_by_id); http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/oracle/xa_core_db_oracle.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/xa_core_db_oracle.sql b/security-admin/db/oracle/xa_core_db_oracle.sql index 9c18ead..2ede810 100644 --- a/security-admin/db/oracle/xa_core_db_oracle.sql +++ b/security-admin/db/oracle/xa_core_db_oracle.sql @@ -99,7 +99,6 @@ CREATE TABLE xa_access_audit ( request_data VARCHAR(2000) DEFAULT NULL NULL , resource_path VARCHAR(2000) DEFAULT NULL NULL , resource_type VARCHAR(255) DEFAULT NULL NULL , - tags VARCHAR(2000) DEFAULT NULL NULL , PRIMARY KEY (id) ); http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql new file mode 100644 index 0000000..d84bb03 --- /dev/null +++ b/security-admin/db/postgres/patches/016-updated-schema-for-tag-based-policy.sql @@ -0,0 +1,257 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +DROP TABLE IF EXISTS x_service_resource_element_val CASCADE; +DROP SEQUENCE IF EXISTS x_service_res_el_val_seq; +DROP TABLE IF EXISTS x_tag_resource_map CASCADE; +DROP SEQUENCE IF EXISTS x_tag_resource_map_seq; +DROP TABLE IF EXISTS x_tag_attr CASCADE; +DROP SEQUENCE IF EXISTS x_tag_attr_seq; +DROP TABLE IF EXISTS x_tag_attr_def CASCADE; +DROP SEQUENCE IF EXISTS x_tag_attr_def_seq; +DROP TABLE IF EXISTS x_service_resource_element CASCADE; +DROP SEQUENCE IF EXISTS x_service_resource_element_seq; +DROP TABLE IF EXISTS x_service_resource CASCADE; +DROP SEQUENCE IF EXISTS x_service_resource_seq; +DROP TABLE IF EXISTS x_tag CASCADE; +DROP SEQUENCE IF EXISTS x_tag_seq; +DROP TABLE IF EXISTS x_tag_def CASCADE; +DROP SEQUENCE IF EXISTS x_tag_def_seq; +commit; +CREATE SEQUENCE x_tag_def_seq; +CREATE TABLE x_tag_def ( +id BIGINT DEFAULT nextval('x_tag_def_seq'::regclass), +guid VARCHAR(64) NOT NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, +name VARCHAR(255) NOT NULL, +source VARCHAR(128) DEFAULT NULL NULL, +is_enabled BOOLEAN DEFAULT '0' NOT NULL, +PRIMARY KEY (id), +CONSTRAINT x_tag_def_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_def_UK_name UNIQUE (name), +CONSTRAINT x_tag_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_def_IDX_added_by_id ON x_tag_def(added_by_id); +CREATE INDEX x_tag_def_IDX_upd_by_id ON x_tag_def(upd_by_id); +commit; + +CREATE SEQUENCE x_tag_seq; +CREATE TABLE x_tag( +id BIGINT DEFAULT nextval('x_tag_seq'::regclass), +guid VARCHAR(64) NOT NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +type BIGINT NOT NULL, +primary key (id), +CONSTRAINT x_tag_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_FK_type FOREIGN KEY (type) REFERENCES x_tag_def (id), +CONSTRAINT x_tag_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_IDX_type ON x_tag(type); +CREATE INDEX x_tag_IDX_added_by_id ON x_tag(added_by_id); +CREATE INDEX x_tag_IDX_upd_by_id ON x_tag(upd_by_id); +commit; + +CREATE SEQUENCE x_service_resource_seq; +CREATE TABLE x_service_resource( +id BIGINT DEFAULT nextval('x_service_resource_seq'::regclass), +guid VARCHAR(64) NOT NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, +service_id BIGINT NOT NULL, +resource_signature VARCHAR(128) DEFAULT NULL NULL, +is_enabled BOOLEAN DEFAULT '1' NOT NULL, +primary key (id), +CONSTRAINT x_service_res_UK_guid UNIQUE (guid), +CONSTRAINT x_service_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id), +CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_service_res_IDX_added_by_id ON x_service_resource(added_by_id); +CREATE INDEX x_service_res_IDX_upd_by_id ON x_service_resource(upd_by_id); +commit; + +CREATE SEQUENCE x_service_resource_element_seq; +CREATE TABLE x_service_resource_element( +id BIGINT DEFAULT nextval('x_service_resource_element_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +res_id BIGINT NOT NULL, +res_def_id BIGINT NOT NULL, +is_excludes BOOLEAN DEFAULT '0' NOT NULL, +is_recursive BOOLEAN DEFAULT '0' NOT NULL, +primary key (id), +CONSTRAINT x_srvc_res_el_FK_res_def_id FOREIGN KEY (res_def_id) REFERENCES x_resource_def (id), +CONSTRAINT x_srvc_res_el_FK_res_id FOREIGN KEY (res_id) REFERENCES x_service_resource (id), +CONSTRAINT x_srvc_res_el_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_srvc_res_el_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_srvc_res_el_IDX_added_by_id ON x_service_resource_element(added_by_id); +CREATE INDEX x_srvc_res_el_IDX_upd_by_id ON x_service_resource_element(upd_by_id); +commit; + +CREATE SEQUENCE x_tag_attr_def_seq; +CREATE TABLE x_tag_attr_def( +id BIGINT DEFAULT nextval('x_tag_attr_def_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +tag_def_id BIGINT NOT NULL, +name VARCHAR(255) NOT NULL, +type VARCHAR(50) NOT NULL, +primary key (id), +CONSTRAINT x_tag_attr_def_FK_tag_def_id FOREIGN KEY (tag_def_id) REFERENCES x_tag_def (id), +CONSTRAINT x_tag_attr_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_attr_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_attr_def_IDX_tag_def_id ON x_tag_attr_def(tag_def_id); +CREATE INDEX x_tag_attr_def_IDX_added_by_id ON x_tag_attr_def(added_by_id); +CREATE INDEX x_tag_attr_def_IDX_upd_by_id ON x_tag_attr_def(upd_by_id); +commit; + +CREATE SEQUENCE x_tag_attr_seq; +CREATE TABLE x_tag_attr( +id BIGINT DEFAULT nextval('x_tag_attr_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +tag_id BIGINT NOT NULL, +name VARCHAR(255) NOT NULL, +value VARCHAR(512) DEFAULT NULL NULL, +primary key (id), +CONSTRAINT x_tag_attr_FK_tag_id FOREIGN KEY (tag_id) REFERENCES x_tag (id), +CONSTRAINT x_tag_attr_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_attr_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_attr_IDX_tag_id ON x_tag_attr(tag_id); +CREATE INDEX x_tag_attr_IDX_added_by_id ON x_tag_attr(added_by_id); +CREATE INDEX x_tag_attr_IDX_upd_by_id ON x_tag_attr(upd_by_id); +commit; + +CREATE SEQUENCE x_tag_resource_map_seq; +CREATE TABLE x_tag_resource_map( +id BIGINT NOT NULL, +guid VARCHAR(64) NOT NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +tag_id BIGINT NOT NULL, +res_id BIGINT NOT NULL, +primary key (id), +CONSTRAINT x_tag_res_map_UK_guid UNIQUE (guid), +CONSTRAINT x_tag_res_map_FK_tag_id FOREIGN KEY (tag_id) REFERENCES x_tag (id), +CONSTRAINT x_tag_res_map_FK_res_id FOREIGN KEY (res_id) REFERENCES x_service_resource (id), +CONSTRAINT x_tag_res_map_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_tag_res_map_IDX_tag_id ON x_tag_resource_map(tag_id); +CREATE INDEX x_tag_res_map_IDX_res_id ON x_tag_resource_map(res_id); +CREATE INDEX x_tag_res_map_IDX_added_by_id ON x_tag_resource_map(added_by_id); +CREATE INDEX x_tag_res_map_IDX_upd_by_id ON x_tag_resource_map(upd_by_id); +commit; + +CREATE SEQUENCE x_service_res_el_val_seq; +CREATE TABLE x_service_resource_element_val( +id BIGINT DEFAULT nextval('x_service_res_el_val_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +res_element_id BIGINT NOT NULL, +value VARCHAR(1024) NOT NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key (id), +CONSTRAINT x_srvc_res_el_val_FK_res_el_id FOREIGN KEY (res_element_id) REFERENCES x_service_resource_element (id), +CONSTRAINT x_srvc_res_el_val_FK_add_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_srvc_res_el_val_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_srvc_res_el_val_IDX_resel_id ON x_service_resource_element_val(res_element_id); +CREATE INDEX x_srvc_res_el_val_IDX_addby_id ON x_service_resource_element_val(added_by_id); +CREATE INDEX x_srvc_res_el_val_IDX_updby_id ON x_service_resource_element_val(upd_by_id); +INSERT INTO x_modules_master(create_time,update_time,added_by_id,upd_by_id,module,url) VALUES(current_timestamp,current_timestamp,1,1,'Tag Based Policies',''); +commit; + +-- function add_column_x_service_def_options +select 'delimiter start'; +CREATE OR REPLACE FUNCTION add_column_x_service_def_options() +RETURNS void AS $$ +DECLARE + v_column_exists integer := 0; +BEGIN + select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_def') and attname='def_options'; + IF v_column_exists = 0 THEN + ALTER TABLE x_service_def ADD COLUMN def_options VARCHAR(1024) DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +-- function add_column_x_policy_item_item_type +CREATE OR REPLACE FUNCTION add_column_x_policy_item_item_type() +RETURNS void AS +$$ +DECLARE + v_column_exists integer := 0; +BEGIN + select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy_item') and attname in('item_type','is_enabled','comments'); + IF v_column_exists = 0 THEN + ALTER TABLE x_policy_item ADD COLUMN item_type INT DEFAULT 0 NOT NULL,ADD COLUMN is_enabled BOOLEAN DEFAULT '1' NOT NULL,ADD COLUMN comments VARCHAR(255) DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +-- function add_tag_columns_x_service +CREATE OR REPLACE FUNCTION add_tag_columns_x_service() +RETURNS void AS +$$ +DECLARE + v_column_exists integer := 0; +BEGIN + select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service') and attname in('tag_service','tag_version','tag_update_time') ; + IF v_column_exists = 0 THEN + ALTER TABLE x_service ADD COLUMN tag_service BIGINT DEFAULT NULL NULL,ADD COLUMN tag_version BIGINT DEFAULT 0 NOT NULL,ADD COLUMN tag_update_time TIMESTAMP DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +-- function callLocalUDFs +CREATE OR REPLACE FUNCTION callLocalUDFs() +RETURNS void AS +$$ +BEGIN + perform add_column_x_service_def_options(); + perform add_column_x_policy_item_item_type(); + perform add_tag_columns_x_service(); +END; +$$ LANGUAGE plpgsql; +select callLocalUDFs(); +select 'delimiter end'; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/postgres/patches/audit/017-add-new-column-to-store-tags.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/audit/017-add-new-column-to-store-tags.sql b/security-admin/db/postgres/patches/audit/017-add-new-column-to-store-tags.sql new file mode 100644 index 0000000..9ecc9a0 --- /dev/null +++ b/security-admin/db/postgres/patches/audit/017-add-new-column-to-store-tags.sql @@ -0,0 +1,30 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +-- function add_column_x_service_def_options +select 'delimiter start'; +CREATE OR REPLACE FUNCTION add_column_xa_access_audit_tags() +RETURNS void AS $$ +DECLARE + v_column_exists integer := 0; +BEGIN + select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='xa_access_audit') and attname='tags'; + IF v_column_exists = 0 THEN + ALTER TABLE xa_access_audit ADD COLUMN tags VARCHAR(4000) DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select add_column_xa_access_audit_tags(); +select 'delimiter end'; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/postgres/xa_audit_db_postgres.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/xa_audit_db_postgres.sql b/security-admin/db/postgres/xa_audit_db_postgres.sql index cace5c5..c12a854 100644 --- a/security-admin/db/postgres/xa_audit_db_postgres.sql +++ b/security-admin/db/postgres/xa_audit_db_postgres.sql @@ -40,7 +40,6 @@ action VARCHAR(2000) DEFAULT NULL NULL, request_data VARCHAR(4000) DEFAULT NULL NULL, resource_path VARCHAR(4000) DEFAULT NULL NULL, resource_type VARCHAR(255) DEFAULT NULL NULL, -tags VARCHAR(4000) DEFAULT NULL NULL, seq_num BIGINT DEFAULT '0' NULL, event_count BIGINT DEFAULT '1' NULL, event_dur_ms BIGINT DEFAULT '1' NULL, http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/postgres/xa_core_db_postgres.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/xa_core_db_postgres.sql b/security-admin/db/postgres/xa_core_db_postgres.sql index 123732e..5a76442 100644 --- a/security-admin/db/postgres/xa_core_db_postgres.sql +++ b/security-admin/db/postgres/xa_core_db_postgres.sql @@ -372,7 +372,6 @@ action VARCHAR(2000) DEFAULT NULL NULL, request_data VARCHAR(4000) DEFAULT NULL NULL, resource_path VARCHAR(4000) DEFAULT NULL NULL, resource_type VARCHAR(255) DEFAULT NULL NULL, -tags VARCHAR(4000) DEFAULT NULL NULL, PRIMARY KEY(id) ); @@ -474,7 +473,6 @@ name VARCHAR(1024) DEFAULT NULL NULL, impl_class_name VARCHAR(1024) DEFAULT NULL NULL, label VARCHAR(1024) DEFAULT NULL NULL, description VARCHAR(1024) DEFAULT NULL NULL, -options VARCHAR(1024) DEFAULT NULL NULL, rb_key_label VARCHAR(1024) DEFAULT NULL NULL, rb_key_description VARCHAR(1024) DEFAULT NULL NULL, is_enabled BOOLEAN DEFAULT '1' NULL, @@ -499,13 +497,11 @@ policy_version BIGINT DEFAULT NULL NULL, policy_update_time TIMESTAMP DEFAULT NULL NULL, description VARCHAR(1024) DEFAULT NULL NULL, is_enabled BOOLEAN DEFAULT '0' NOT NULL, -tag_service BIGINT DEFAULT NULL NULL, primary key(id), CONSTRAINT x_service_name UNIQUE(name), CONSTRAINT x_service_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), CONSTRAINT x_service_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), -CONSTRAINT x_service_FK_type FOREIGN KEY(type) REFERENCES x_service_def(id), -CONSTRAINT x_service_FK_tag_service FOREIGN KEY(tag_service) REFERENCES x_service(id) +CONSTRAINT x_service_FK_type FOREIGN KEY(type) REFERENCES x_service_def(id) ); DROP TABLE IF EXISTS x_policy CASCADE; DROP SEQUENCE IF EXISTS x_policy_seq; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql new file mode 100644 index 0000000..21f0fcc --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql @@ -0,0 +1,233 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +CREATE TABLE dbo.x_tag_def( + id bigint IDENTITY NOT NULL, + guid varchar(64) NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + version bigint DEFAULT NULL NULL, + name varchar(255) NOT NULL, + source varchar(128) DEFAULT NULL NULL, + is_enabled tinyint DEFAULT 0 NOT NULL, + CONSTRAINT x_tag_def_PK_id PRIMARY KEY CLUSTERED(id), + CONSTRAINT x_tag_def_UK_guid UNIQUE NONCLUSTERED (guid), + CONSTRAINT x_tag_def_UK_name UNIQUE NONCLUSTERED (name) +) +GO +CREATE TABLE dbo.x_tag( + id bigint IDENTITY NOT NULL, + guid varchar(64) NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + type bigint NOT NULL, + CONSTRAINT x_tag_PK_id PRIMARY KEY CLUSTERED(id), + CONSTRAINT x_tag_UK_guid UNIQUE NONCLUSTERED (guid) +) +GO +CREATE TABLE dbo.x_service_resource( + id bigint IDENTITY NOT NULL, + guid varchar(64) NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + version bigint DEFAULT NULL NULL, + service_id bigint NOT NULL, + resource_signature varchar(128) DEFAULT NULL NULL, + is_enabled tinyint DEFAULT 1 NOT NULL, + CONSTRAINT x_service_res_PK_id PRIMARY KEY CLUSTERED(id), + CONSTRAINT x_service_res_UK_guid UNIQUE NONCLUSTERED (guid) +) +GO +CREATE TABLE dbo.x_service_resource_element( + id bigint IDENTITY NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + res_id bigint NOT NULL, + res_def_id bigint NOT NULL, + is_excludes tinyint DEFAULT 0 NOT NULL, + is_recursive tinyint DEFAULT 0 NOT NULL, + CONSTRAINT x_srvc_res_el_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +CREATE TABLE dbo.x_tag_attr_def( + id bigint IDENTITY NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + tag_def_id bigint NOT NULL, + name varchar(255) NOT NULL, + type varchar(50) NOT NULL, + CONSTRAINT x_tag_attr_def_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +CREATE TABLE dbo.x_tag_attr( + id bigint IDENTITY NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + tag_id bigint NOT NULL, + name varchar(255) NOT NULL, + value varchar(512) DEFAULT NULL NULL, + CONSTRAINT x_tag_attr_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +CREATE TABLE dbo.x_tag_resource_map( + id bigint IDENTITY NOT NULL, + guid varchar(64) NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + tag_id bigint NOT NULL, + res_id bigint NOT NULL, + CONSTRAINT x_tag_res_map_PK_id PRIMARY KEY CLUSTERED(id), + CONSTRAINT x_tag_res_map_UK_guid UNIQUE NONCLUSTERED (guid) +) +GO +CREATE TABLE dbo.x_service_resource_element_val( + id bigint IDENTITY NOT NULL, + create_time datetime DEFAULT NULL NULL, + update_time datetime DEFAULT NULL NULL, + added_by_id bigint DEFAULT NULL NULL, + upd_by_id bigint DEFAULT NULL NULL, + res_element_id bigint NOT NULL, + value varchar(1024) NOT NULL, + sort_order tinyint DEFAULT 0 NULL, + CONSTRAINT x_srvc_res_el_val_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +ALTER TABLE dbo.x_tag_def ADD CONSTRAINT x_tag_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_def ADD CONSTRAINT x_tag_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag ADD CONSTRAINT x_tag_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag ADD CONSTRAINT x_tag_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag ADD CONSTRAINT x_tag_FK_type FOREIGN KEY(type) REFERENCES dbo.x_tag_def (id) +GO +ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_service_id FOREIGN KEY(service_id) REFERENCES dbo.x_service (id) +GO +ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_res_def_id FOREIGN KEY(res_def_id) REFERENCES dbo.x_resource_def (id) +GO +ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_res_id FOREIGN KEY(res_id) REFERENCES dbo.x_service_resource (id) +GO +ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_tag_def_id FOREIGN KEY(tag_def_id) REFERENCES dbo.x_tag_def (id) +GO +ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_tag_id FOREIGN KEY(tag_id) REFERENCES dbo.x_tag (id) +GO +ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_tag_id FOREIGN KEY(tag_id) REFERENCES dbo.x_tag (id) +GO +ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_res_id FOREIGN KEY(res_id) REFERENCES dbo.x_service_resource (id) +GO +ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_res_el_id FOREIGN KEY(res_element_id) REFERENCES dbo.x_service_resource_element (id) +GO +ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_add_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +CREATE NONCLUSTERED INDEX x_tag_def_IDX_added_by_id ON dbo.x_tag_def(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_def_IDX_upd_by_id ON dbo.x_tag_def(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_IDX_type ON dbo.x_tag(type ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_IDX_added_by_id ON dbo.x_tag(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_IDX_upd_by_id ON dbo.x_tag(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_service_res_IDX_added_by_id ON dbo.x_service_resource(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_service_res_IDX_upd_by_id ON dbo.x_service_resource(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_srvc_res_el_IDX_added_by_id ON dbo.x_service_resource_element(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_srvc_res_el_IDX_upd_by_id ON dbo.x_service_resource_element(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_tag_def_id ON dbo.x_tag_attr_def(tag_def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_added_by_id ON dbo.x_tag_attr_def(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_upd_by_id ON dbo.x_tag_attr_def(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_IDX_tag_id ON dbo.x_tag_attr(tag_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_IDX_added_by_id ON dbo.x_tag_attr(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_attr_IDX_upd_by_id ON dbo.x_tag_attr(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_tag_id ON dbo.x_tag_resource_map(tag_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_res_id ON dbo.x_tag_resource_map(res_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_added_by_id ON dbo.x_tag_resource_map(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_upd_by_id ON dbo.x_tag_resource_map(upd_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_resel_id ON dbo.x_service_resource_element_val(res_element_id ASC) +GO +CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_addby_id ON dbo.x_service_resource_element_val(added_by_id ASC) +GO +CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_updby_id ON dbo.x_service_resource_element_val(upd_by_id ASC) +GO +INSERT INTO dbo.x_modules_master(create_time,update_time,added_by_id,upd_by_id,module,url) VALUES(GETDATE(),GETDATE(),1,1,'Tag Based Policies','') +GO + +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_def' and cname = 'def_options') THEN + ALTER TABLE dbo.x_service_def ADD def_options varchar(1024) DEFAULT NULL NULL; +END IF; +GO + +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy_item' and cname in('item_type','is_enabled','comments')) THEN + ALTER TABLE dbo.x_policy_item ADD (item_type int DEFAULT 0 NOT NULL,is_enabled tinyint DEFAULT 1 NOT NULL,comments varchar(255) DEFAULT NULL NULL); +END IF; +GO + +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service' and cname in('tag_service','tag_version','tag_update_time')) THEN + ALTER TABLE dbo.x_service ADD (tag_service bigint DEFAULT NULL NULL,tag_version bigint DEFAULT 0 NOT NULL,tag_update_time datetime DEFAULT NULL NULL); +END IF; +GO + +exit http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/f5245892/security-admin/db/sqlanywhere/patches/audit/017-add-new-column-to-store-tags.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/audit/017-add-new-column-to-store-tags.sql b/security-admin/db/sqlanywhere/patches/audit/017-add-new-column-to-store-tags.sql new file mode 100644 index 0000000..c9b68c3 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/audit/017-add-new-column-to-store-tags.sql @@ -0,0 +1,20 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'xa_access_audit' and cname = 'tags') THEN + ALTER TABLE dbo.xa_access_audit ADD "tags" varchar(4000) DEFAULT NULL NULL; +END IF; +GO +exit \ No newline at end of file
