Repository: incubator-ranger Updated Branches: refs/heads/master 93cb024dd -> 93e3bf1cd
RANGER-908: Ranger policy model to support data masking and row-filtering for All DB flavor 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/93e3bf1c Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/93e3bf1c Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/93e3bf1c Branch: refs/heads/master Commit: 93e3bf1cd2d7e26152b5c92cb9a9e767f714431c Parents: 93cb024 Author: Pradeep Agrawal <[email protected]> Authored: Wed Apr 6 10:33:08 2016 +0530 Committer: Madhan Neethiraj <[email protected]> Committed: Tue Apr 5 23:31:13 2016 -0700 ---------------------------------------------------------------------- .../db/mysql/patches/020-datamask-policy.sql | 56 +++--- .../db/oracle/patches/020-datamask-policy.sql | 117 +++++++++++ .../db/postgres/patches/020-datamask-policy.sql | 144 +++++++------- .../sqlanywhere/patches/020-datamask-policy.sql | 104 ++++++++++ .../sqlserver/patches/020-datamask-policy.sql | 193 +++++++++++++++++++ 5 files changed, 516 insertions(+), 98 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/93e3bf1c/security-admin/db/mysql/patches/020-datamask-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/020-datamask-policy.sql b/security-admin/db/mysql/patches/020-datamask-policy.sql index fffa613..ac5f404 100644 --- a/security-admin/db/mysql/patches/020-datamask-policy.sql +++ b/security-admin/db/mysql/patches/020-datamask-policy.sql @@ -20,10 +20,10 @@ delimiter ;; if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_access_type_def') then if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_access_type_def' and column_name = 'datamask_options') then - ALTER TABLE `x_access_type_def` ADD `datamask_options` varchar(1024) DEFAULT NULL; + ALTER TABLE `x_access_type_def` ADD `datamask_options` varchar(1024) NULL DEFAULT NULL; end if; if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_access_type_def' and column_name = 'rowfilter_options') then - ALTER TABLE `x_access_type_def` ADD `rowfilter_options` varchar(1024) DEFAULT NULL; + ALTER TABLE `x_access_type_def` ADD `rowfilter_options` varchar(1024) NULL DEFAULT NULL; end if; end if; end;; @@ -39,10 +39,10 @@ delimiter ;; if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_resource_def') then if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_resource_def' and column_name = 'datamask_options') then - ALTER TABLE `x_resource_def` ADD `datamask_options` varchar(1024) DEFAULT NULL; + ALTER TABLE `x_resource_def` ADD `datamask_options` varchar(1024) NULL DEFAULT NULL; end if; if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_resource_def' and column_name = 'rowfilter_options') then - ALTER TABLE `x_resource_def` ADD `rowfilter_options` varchar(1024) DEFAULT NULL; + ALTER TABLE `x_resource_def` ADD `rowfilter_options` varchar(1024) NULL DEFAULT NULL; end if; end if; end;; @@ -54,26 +54,22 @@ drop procedure if exists add_datamask_options_to_x_resource_def_table; DROP TABLE IF EXISTS `x_datamask_type_def`; CREATE TABLE `x_datamask_type_def` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , -`guid` varchar(1024) DEFAULT 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, +`guid` varchar(64) NULL 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, `def_id` bigint(20) NOT NULL, `item_id` bigint(20) NOT NULL, `name` varchar(1024) NOT NULL, `label` varchar(1024) NOT NULL, -`description` varchar(1024) DEFAULT NULL, -`transformer` varchar(1024) DEFAULT NULL, -`datamask_options` varchar(1024) DEFAULT NULL, -`rb_key_label` varchar(1024) DEFAULT NULL, +`description` varchar(1024) NULL DEFAULT NULL, +`transformer` varchar(1024) NULL DEFAULT NULL, +`datamask_options` varchar(1024) NULL DEFAULT NULL, +`rb_key_label` varchar(1024) NULL DEFAULT NULL, `rb_key_description` varchar(1024) DEFAULT NULL, `sort_order` tinyint(3) DEFAULT '0', primary key (`id`), -KEY `x_datamask_type_def_added_by_id` (`added_by_id`), -KEY `x_datamask_type_def_upd_by_id` (`upd_by_id`), -KEY `x_datamask_type_def_cr_time` (`create_time`), -KEY `x_datamask_type_def_up_time` (`update_time`), CONSTRAINT `x_datamask_type_def_FK_def_id` FOREIGN KEY (`def_id`) REFERENCES `x_service_def` (`id`) , CONSTRAINT `x_datamask_type_def_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_datamask_type_def_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) @@ -83,15 +79,15 @@ CREATE INDEX x_datamask_type_def_IDX_def_id ON x_datamask_type_def(def_id); DROP TABLE IF EXISTS `x_policy_item_datamask`; CREATE TABLE `x_policy_item_datamask` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , -`guid` varchar(1024) DEFAULT 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, +`guid` varchar(64) NULL 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, `policy_item_id` bigint(20) NOT NULL, `type` bigint(20) NOT NULL, -`condition_expr` varchar(1024) DEFAULT NULL, -`value_expr` varchar(1024) DEFAULT NULL, +`condition_expr` varchar(1024) NULL DEFAULT NULL, +`value_expr` varchar(1024) NULL DEFAULT NULL, primary key (id), CONSTRAINT `x_policy_item_datamask_FK_policy_item_id` FOREIGN KEY (`policy_item_id`) REFERENCES `x_policy_item` (`id`) , CONSTRAINT `x_policy_item_datamask_FK_type` FOREIGN KEY (`type`) REFERENCES `x_datamask_type_def` (`id`), @@ -103,13 +99,13 @@ CREATE INDEX x_policy_item_datamask_IDX_policy_item_id ON x_policy_item_datamask DROP TABLE IF EXISTS `x_policy_item_rowfilter`; CREATE TABLE `x_policy_item_rowfilter` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , -`guid` varchar(1024) DEFAULT 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, +`guid` varchar(64) NULL 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, `policy_item_id` bigint(20) NOT NULL, -`filter_expr` varchar(1024) DEFAULT NULL, +`filter_expr` varchar(1024) NULL DEFAULT NULL, primary key (id), CONSTRAINT `x_policy_item_rowfilter_FK_policy_item_id` FOREIGN KEY (`policy_item_id`) REFERENCES `x_policy_item` (`id`) , CONSTRAINT `x_policy_item_rowfilter_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/93e3bf1c/security-admin/db/oracle/patches/020-datamask-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/020-datamask-policy.sql b/security-admin/db/oracle/patches/020-datamask-policy.sql new file mode 100644 index 0000000..8448a85 --- /dev/null +++ b/security-admin/db/oracle/patches/020-datamask-policy.sql @@ -0,0 +1,117 @@ +-- 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_DATAMASK_TYPE_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_datamask_type_def ( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) DEFAULT NULL 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, +def_id NUMBER(20) NOT NULL, +item_id NUMBER(20) NOT NULL, +name VARCHAR(1024) NOT NULL, +label VARCHAR(1024) NOT NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +transformer VARCHAR(1024) DEFAULT NULL NULL, +datamask_options VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_description VARCHAR(1024) DEFAULT NULL NULL, +sort_order NUMBER(3) DEFAULT '0' NULL, +primary key (id), +CONSTRAINT x_dm_type_def_FK_def_id FOREIGN KEY (def_id) REFERENCES x_service_def(id), +CONSTRAINT x_dm_type_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_dm_type_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user(id) +); +CREATE INDEX x_dm_type_def_IDX_def_id ON x_datamask_type_def(def_id); +commit; +CREATE SEQUENCE X_POLICY_ITEM_DATAMASK_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_policy_item_datamask( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) DEFAULT NULL 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, +policy_item_id NUMBER(20) NOT NULL, +type NUMBER(20) NOT NULL, +condition_expr VARCHAR(1024) DEFAULT NULL NULL, +value_expr VARCHAR(1024) DEFAULT NULL NULL, +primary key (id), +CONSTRAINT x_plc_item_dm_FK_plc_item_id FOREIGN KEY (policy_item_id) REFERENCES x_policy_item(id), +CONSTRAINT x_plc_item_dm_FK_type FOREIGN KEY (type) REFERENCES x_datamask_type_def(id), +CONSTRAINT x_plc_item_dm_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_plc_item_dm_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user(id) +); +CREATE INDEX x_plc_item_dm_IDX_plc_item_id ON x_policy_item_datamask(policy_item_id); +commit; +CREATE SEQUENCE X_POLICY_ITEM_ROWFILTER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +CREATE TABLE x_policy_item_rowfilter( +id NUMBER(20) NOT NULL, +guid VARCHAR(64) DEFAULT NULL 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, +policy_item_id NUMBER(20) NOT NULL, +filter_expr VARCHAR(1024) DEFAULT NULL NULL, +primary key (id), +CONSTRAINT x_plc_item_rf_FK_plc_item_id FOREIGN KEY (policy_item_id) REFERENCES x_policy_item (id), +CONSTRAINT x_plc_item_rf_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), +CONSTRAINT x_plc_item_rf_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) +); +CREATE INDEX x_plc_item_rf_IDX_plc_item_id ON x_policy_item_rowfilter(policy_item_id); + +DECLARE + v_column1_exists number := 0; + v_column2_exists number := 0; +BEGIN + Select count(*) into v_column1_exists + from user_tab_cols + where column_name = upper('datamask_options') + and table_name = upper('x_access_type_def'); + + Select count(*) into v_column2_exists + from user_tab_cols + where column_name = upper('rowfilter_options') + and table_name = upper('x_access_type_def'); + + if (v_column1_exists = 0) AND (v_column2_exists = 0) then + execute immediate 'ALTER TABLE x_access_type_def ADD (datamask_options VARCHAR(1024) DEFAULT NULL NULL,rowfilter_options VARCHAR(1024) DEFAULT NULL NULL)'; + commit; + end if; +end;/ + +DECLARE + v_column1_exists number := 0; + v_column2_exists number := 0; +BEGIN + Select count(*) into v_column1_exists + from user_tab_cols + where column_name = upper('datamask_options') + and table_name = upper('x_resource_def'); + + Select count(*) into v_column2_exists + from user_tab_cols + where column_name = upper('rowfilter_options') + and table_name = upper('x_resource_def'); + + if (v_column1_exists = 0) AND (v_column2_exists = 0) then + execute immediate 'ALTER TABLE x_resource_def ADD (datamask_options VARCHAR(1024) DEFAULT NULL NULL,rowfilter_options VARCHAR(1024) DEFAULT NULL NULL)'; + commit; + end if; +end;/ http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/93e3bf1c/security-admin/db/postgres/patches/020-datamask-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/020-datamask-policy.sql b/security-admin/db/postgres/patches/020-datamask-policy.sql index 393684b..795721c 100644 --- a/security-admin/db/postgres/patches/020-datamask-policy.sql +++ b/security-admin/db/postgres/patches/020-datamask-policy.sql @@ -13,50 +13,6 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -select 'delimiter start'; - -/* add x_access_type_def.datamask_options column if it does not exist */ -CREATE OR REPLACE FUNCTION add_datamask_options_to_x_access_type_def_table() -RETURNS void AS $$ -DECLARE - exists_access_type_def_datamask_options integer := 0; - exists_access_type_def_rowfilter_options integer := 0; -BEGIN - select count(*) into exists_access_type_def_datamask_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_access_type_def') and attname='datamask_options'; - select count(*) into exists_access_type_def_rowfilter_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_access_type_def') and attname='rowfilter_options'; - IF exists_access_type_def_datamask_options = 0 THEN - ALTER TABLE x_access_type_def ADD COLUMN datamask_options VARCHAR(1024) DEFAULT NULL; - END IF; - IF exists_access_type_def_rowfilter_options = 0 THEN - ALTER TABLE x_access_type_def ADD COLUMN rowfilter_options VARCHAR(1024) DEFAULT NULL; - END IF; -END; -$$ LANGUAGE plpgsql; - -/* add x_resource_def.datamask_options column if it does not exist */ -CREATE OR REPLACE FUNCTION add_datamask_options_to_x_resource_def_table() -RETURNS void AS $$ -DECLARE - exists_resource_def_datamask_options integer := 0; - exists_resource_def_rowfilter_options integer := 0; -BEGIN - select count(*) into exists_resource_def_datamask_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_resource_def') and attname='datamask_options'; - select count(*) into exists_resource_def_rowfilter_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_resource_def') and attname='rowfilter_options'; - IF exists_resource_def_datamask_options = 0 THEN - ALTER TABLE x_resource_def ADD COLUMN datamask_options VARCHAR(1024) DEFAULT NULL; - END IF; - IF exists_resource_def_rowfilter_options = 0 THEN - ALTER TABLE x_resource_def ADD COLUMN rowfilter_options VARCHAR(1024) DEFAULT NULL; - END IF; -END; -$$ LANGUAGE plpgsql; - -select 'delimiter end'; - - -SELECT add_datamask_options_to_x_access_type_def_table(); -SELECT add_datamask_options_to_x_resource_def_table(); - DROP TABLE IF EXISTS x_datamask_type_def; DROP TABLE IF EXISTS x_policy_item_datamask; DROP SEQUENCE IF EXISTS x_datamask_type_def_seq; @@ -65,21 +21,21 @@ DROP SEQUENCE IF EXISTS x_policy_item_datamask_def_seq; CREATE SEQUENCE x_datamask_type_def_seq; CREATE TABLE x_datamask_type_def ( id BIGINT DEFAULT nextval('x_datamask_type_def_seq'::regclass), - guid VARCHAR(1024) DEFAULT NULL, - create_time TIMESTAMP DEFAULT NULL, - update_time TIMESTAMP DEFAULT NULL, - added_by_id BIGINT DEFAULT NULL, - upd_by_id BIGINT DEFAULT NULL, + guid VARCHAR(64) DEFAULT NULL 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, def_id BIGINT NOT NULL, item_id BIGINT NOT NULL, name VARCHAR(1024) NOT NULL, label VARCHAR(1024) NOT NULL, - description VARCHAR(1024) DEFAULT NULL, - transformer VARCHAR(1024) DEFAULT NULL, - datamask_options VARCHAR(1024) DEFAULT NULL, - rb_key_label VARCHAR(1024) DEFAULT NULL, - rb_key_description VARCHAR(1024) DEFAULT NULL, - sort_order SMALLINT DEFAULT '0', + description VARCHAR(1024) DEFAULT NULL NULL, + transformer VARCHAR(1024) DEFAULT NULL NULL, + datamask_options VARCHAR(1024) DEFAULT NULL NULL, + rb_key_label VARCHAR(1024) DEFAULT NULL NULL, + rb_key_description VARCHAR(1024) DEFAULT NULL NULL, + sort_order SMALLINT DEFAULT '0' NULL, primary key (id), CONSTRAINT x_datamask_type_def_FK_def_id FOREIGN KEY (def_id) REFERENCES x_service_def (id) , CONSTRAINT x_datamask_type_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), @@ -90,15 +46,15 @@ CREATE INDEX x_datamask_type_def_IDX_def_id ON x_datamask_type_def(def_id); CREATE SEQUENCE x_policy_item_datamask_seq; CREATE TABLE x_policy_item_datamask ( id BIGINT DEFAULT nextval('x_policy_item_datamask_seq'::regclass), - guid VARCHAR(1024) DEFAULT NULL, - create_time TIMESTAMP DEFAULT NULL, - update_time TIMESTAMP DEFAULT NULL, - added_by_id BIGINT DEFAULT NULL, - upd_by_id BIGINT DEFAULT NULL, + guid VARCHAR(64) DEFAULT NULL 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, policy_item_id BIGINT NOT NULL, type BIGINT NOT NULL, - condition_expr VARCHAR(1024) DEFAULT NULL, - value_expr VARCHAR(1024) DEFAULT NULL, + condition_expr VARCHAR(1024) DEFAULT NULL NULL, + value_expr VARCHAR(1024) DEFAULT NULL NULL, primary key (id), CONSTRAINT x_policy_item_datamask_FK_policy_item_id FOREIGN KEY (policy_item_id) REFERENCES x_policy_item (id) , CONSTRAINT x_policy_item_datamask_FK_type FOREIGN KEY (type) REFERENCES x_datamask_type_def (id), @@ -113,16 +69,68 @@ DROP SEQUENCE IF EXISTS x_policy_item_rowfilter_seq; CREATE SEQUENCE x_policy_item_rowfilter_seq; CREATE TABLE x_policy_item_rowfilter ( id BIGINT DEFAULT nextval('x_policy_item_rowfilter_seq'::regclass), - guid VARCHAR(1024) DEFAULT NULL, - create_time TIMESTAMP DEFAULT NULL, - update_time TIMESTAMP DEFAULT NULL, - added_by_id BIGINT DEFAULT NULL, - upd_by_id BIGINT DEFAULT NULL, + guid VARCHAR(64) DEFAULT NULL 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, policy_item_id BIGINT NOT NULL, - filter_expr VARCHAR(1024) DEFAULT NULL, + filter_expr VARCHAR(1024) DEFAULT NULL NULL, primary key (id), CONSTRAINT x_policy_item_rowfilter_FK_policy_item_id FOREIGN KEY (policy_item_id) REFERENCES x_policy_item (id) , CONSTRAINT x_policy_item_rowfilter_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), CONSTRAINT x_policy_item_rowfilter_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) ); CREATE INDEX x_policy_item_rowfilter_IDX_policy_item_id ON x_policy_item_rowfilter(policy_item_id); + +select 'delimiter start'; + +/* add x_access_type_def.datamask_options column if it does not exist */ +CREATE OR REPLACE FUNCTION add_datamask_options_to_x_access_type_def_table() +RETURNS void AS $$ +DECLARE + exists_access_type_def_datamask_options integer := 0; + exists_access_type_def_rowfilter_options integer := 0; +BEGIN + select count(*) into exists_access_type_def_datamask_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_access_type_def') and attname='datamask_options'; + select count(*) into exists_access_type_def_rowfilter_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_access_type_def') and attname='rowfilter_options'; + IF exists_access_type_def_datamask_options = 0 THEN + ALTER TABLE x_access_type_def ADD COLUMN datamask_options VARCHAR(1024) DEFAULT NULL NULL; + END IF; + IF exists_access_type_def_rowfilter_options = 0 THEN + ALTER TABLE x_access_type_def ADD COLUMN rowfilter_options VARCHAR(1024) DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +/* add x_resource_def.datamask_options column if it does not exist */ +CREATE OR REPLACE FUNCTION add_datamask_options_to_x_resource_def_table() +RETURNS void AS $$ +DECLARE + exists_resource_def_datamask_options integer := 0; + exists_resource_def_rowfilter_options integer := 0; +BEGIN + select count(*) into exists_resource_def_datamask_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_resource_def') and attname='datamask_options'; + select count(*) into exists_resource_def_rowfilter_options from pg_attribute where attrelid in(select oid from pg_class where relname='x_resource_def') and attname='rowfilter_options'; + IF exists_resource_def_datamask_options = 0 THEN + ALTER TABLE x_resource_def ADD COLUMN datamask_options VARCHAR(1024) DEFAULT NULL NULL; + END IF; + IF exists_resource_def_rowfilter_options = 0 THEN + ALTER TABLE x_resource_def ADD COLUMN rowfilter_options VARCHAR(1024) DEFAULT NULL NULL; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +-- function callLocalUDFs +CREATE OR REPLACE FUNCTION callLocalUDFs() +RETURNS void AS +$$ +BEGIN + perform add_datamask_options_to_x_access_type_def_table(); + perform add_datamask_options_to_x_resource_def_table(); +END; +$$ LANGUAGE plpgsql; +select callLocalUDFs(); +select 'delimiter end'; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/93e3bf1c/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql b/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql new file mode 100644 index 0000000..fe6fa9f --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql @@ -0,0 +1,104 @@ +-- 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_datamask_type_def( + id bigint IDENTITY NOT NULL, + guid varchar(64) DEFAULT NULL 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, + def_id bigint NOT NULL, + item_id bigint NOT NULL, + name varchar(1024) NOT NULL, + label varchar(1024) NOT NULL, + description varchar(1024) DEFAULT NULL NULL, + transformer varchar(1024) DEFAULT NULL NULL, + datamask_options varchar(1024) DEFAULT NULL NULL, + rb_key_label varchar(1024) DEFAULT NULL NULL, + rb_key_description varchar(1024) DEFAULT NULL NULL, + sort_order tinyint DEFAULT 0 NULL, + CONSTRAINT x_datamask_type_def_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +CREATE TABLE dbo.x_policy_item_datamask( + id bigint IDENTITY NOT NULL, + guid varchar(64) DEFAULT NULL 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, + policy_item_id bigint NOT NULL, + type bigint NOT NULL, + condition_expr varchar(1024) DEFAULT NULL NULL, + value_expr varchar(1024) DEFAULT NULL NULL, + CONSTRAINT x_policy_item_datamask_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +CREATE TABLE dbo.x_policy_item_rowfilter( + id bigint IDENTITY NOT NULL, + guid varchar(64) DEFAULT NULL 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, + policy_item_id bigint NOT NULL, + filter_expr varchar(1024) DEFAULT NULL NULL, + CONSTRAINT x_policy_item_rowfilter_PK_id PRIMARY KEY CLUSTERED(id) +) +GO +ALTER TABLE dbo.x_datamask_type_def ADD CONSTRAINT x_datamask_type_def_FK_def_id FOREIGN KEY(def_id) REFERENCES dbo.x_service_def (id) +GO +ALTER TABLE dbo.x_datamask_type_def ADD CONSTRAINT x_datamask_type_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_datamask_type_def ADD CONSTRAINT x_datamask_type_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_policy_item_datamask ADD CONSTRAINT x_policy_item_datamask_FK_policy_item_id FOREIGN KEY(policy_item_id) REFERENCES dbo.x_policy_item (id) +GO +ALTER TABLE dbo.x_policy_item_datamask ADD CONSTRAINT x_policy_item_datamask_FK_type FOREIGN KEY(type) REFERENCES dbo.x_datamask_type_def (id) +GO +ALTER TABLE dbo.x_policy_item_datamask ADD CONSTRAINT x_policy_item_datamask_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_policy_item_datamask ADD CONSTRAINT x_policy_item_datamask_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_policy_item_rowfilter ADD CONSTRAINT x_policy_item_rowfilter_FK_policy_item_id FOREIGN KEY(policy_item_id) REFERENCES dbo.x_policy_item (id) +GO +ALTER TABLE dbo.x_policy_item_rowfilter ADD CONSTRAINT x_policy_item_rowfilter_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id) +GO +ALTER TABLE dbo.x_policy_item_rowfilter ADD CONSTRAINT x_policy_item_rowfilter_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id) +GO +CREATE NONCLUSTERED INDEX x_datamask_type_def_IDX_def_id ON dbo.x_datamask_type_def(def_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_datamask_IDX_policy_item_id ON dbo.x_policy_item_datamask(policy_item_id ASC) +GO +CREATE NONCLUSTERED INDEX x_policy_item_rowfilter_IDX_policy_item_id ON dbo.x_policy_item_rowfilter(policy_item_id ASC) +GO + +-- add datamask_options column in x_access_type_def table if not exist +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_access_type_def' and cname = 'datamask_options') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_access_type_def' and cname = 'rowfilter_options') THEN + ALTER TABLE dbo.x_access_type_def ADD (datamask_options VARCHAR(1024) DEFAULT NULL NULL,rowfilter_options VARCHAR(1024) DEFAULT NULL NULL); + END IF; +END IF; +GO +-- add datamask_options column in x_resource_def table if not exist +IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_resource_def' and cname = 'datamask_options') THEN + IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_resource_def' and cname = 'rowfilter_options') THEN + ALTER TABLE dbo.x_resource_def ADD (datamask_options VARCHAR(1024) DEFAULT NULL NULL,rowfilter_options VARCHAR(1024) DEFAULT NULL NULL); + END IF; +END IF; +GO + +exit http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/93e3bf1c/security-admin/db/sqlserver/patches/020-datamask-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/020-datamask-policy.sql b/security-admin/db/sqlserver/patches/020-datamask-policy.sql new file mode 100644 index 0000000..c50d2b8 --- /dev/null +++ b/security-admin/db/sqlserver/patches/020-datamask-policy.sql @@ -0,0 +1,193 @@ +-- 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. + +GO +IF (OBJECT_ID('x_datamask_type_def_FK_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_datamask_type_def] DROP CONSTRAINT x_datamask_type_def_FK_def_id +END +GO +IF (OBJECT_ID('x_datamask_type_def_FK_added_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_datamask_type_def] DROP CONSTRAINT x_datamask_type_def_FK_added_by_id +END +GO +IF (OBJECT_ID('x_datamask_type_def_FK_upd_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_datamask_type_def] DROP CONSTRAINT x_datamask_type_def_FK_upd_by_id +END +GO +IF (OBJECT_ID('x_policy_item_datamask_FK_policy_item_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_datamask] DROP CONSTRAINT x_policy_item_datamask_FK_policy_item_id +END +GO +IF (OBJECT_ID('x_policy_item_datamask_FK_type') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_datamask] DROP CONSTRAINT x_policy_item_datamask_FK_type +END +GO +IF (OBJECT_ID('x_policy_item_datamask_FK_added_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_datamask] DROP CONSTRAINT x_policy_item_datamask_FK_added_by_id +END +GO +IF (OBJECT_ID('x_policy_item_datamask_FK_upd_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_datamask] DROP CONSTRAINT x_policy_item_datamask_FK_upd_by_id +END +GO +IF (OBJECT_ID('x_policy_item_rowfilter_FK_policy_item_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_rowfilter] DROP CONSTRAINT x_policy_item_rowfilter_FK_policy_item_id +END +GO +IF (OBJECT_ID('x_policy_item_rowfilter_FK_added_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_rowfilter] DROP CONSTRAINT x_policy_item_rowfilter_FK_added_by_id +END +GO +IF (OBJECT_ID('x_policy_item_rowfilter_FK_upd_by_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_item_rowfilter] DROP CONSTRAINT x_policy_item_rowfilter_FK_upd_by_id +END +GO +IF (OBJECT_ID('x_datamask_type_def') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_datamask_type_def] +END +GO +IF (OBJECT_ID('x_policy_item_datamask') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_item_datamask] +END +GO +IF (OBJECT_ID('x_policy_item_rowfilter') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_item_rowfilter] +END +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_datamask_type_def]( + [id] [bigint] IDENTITY(1,1) NOT NULL, + [guid] [varchar](64) DEFAULT NULL NULL, + [create_time] [datetime2] DEFAULT NULL NULL, + [update_time] [datetime2] DEFAULT NULL NULL, + [added_by_id] [bigint] DEFAULT NULL NULL, + [upd_by_id] [bigint] DEFAULT NULL NULL, + [def_id] [bigint] NOT NULL, + [item_id] [bigint] NOT NULL, + [name] [varchar](1024) NOT NULL, + [label] [varchar](1024) NOT NULL, + [description] [varchar](1024) DEFAULT NULL NULL, + [transformer] [varchar](1024) DEFAULT NULL NULL, + [datamask_options] [varchar](1024) DEFAULT NULL NULL, + [rb_key_label] [varchar](1024) DEFAULT NULL NULL, + [rb_key_description] [varchar](1024) DEFAULT NULL NULL, + [sort_order] [tinyint] DEFAULT 0 NULL, + PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] +GO +CREATE TABLE [dbo].[x_policy_item_datamask]( + [id] [bigint] IDENTITY(1,1) NOT NULL, + [guid] [varchar](64) DEFAULT NULL NULL, + [create_time] [datetime2] DEFAULT NULL NULL, + [update_time] [datetime2] DEFAULT NULL NULL, + [added_by_id] [bigint] DEFAULT NULL NULL, + [upd_by_id] [bigint] DEFAULT NULL NULL, + [policy_item_id] [bigint] NOT NULL, + [type] [bigint] NOT NULL, + [condition_expr] [varchar](1024) DEFAULT NULL NULL, + [value_expr] [varchar](1024) DEFAULT NULL NULL, + PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] +GO +CREATE TABLE [dbo].[x_policy_item_rowfilter]( + [id] [bigint] IDENTITY(1,1) NOT NULL, + [guid] [varchar](64) DEFAULT NULL NULL, + [create_time] [datetime2] DEFAULT NULL NULL, + [update_time] [datetime2] DEFAULT NULL NULL, + [added_by_id] [bigint] DEFAULT NULL NULL, + [upd_by_id] [bigint] DEFAULT NULL NULL, + [policy_item_id] [bigint] NOT NULL, + [filter_expr] [varchar](1024) DEFAULT NULL NULL, + PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_def_id] FOREIGN KEY([def_id]) REFERENCES [dbo].[x_service_def] ([id]) +GO +ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_datamask] WITH CHECK ADD CONSTRAINT [x_policy_item_datamask_FK_policy_item_id] FOREIGN KEY([policy_item_id]) REFERENCES [dbo].[x_policy_item] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_datamask] WITH CHECK ADD CONSTRAINT [x_policy_item_datamask_FK_type] FOREIGN KEY([type]) REFERENCES [dbo].[x_datamask_type_def] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_datamask] WITH CHECK ADD CONSTRAINT [x_policy_item_datamask_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_datamask] WITH CHECK ADD CONSTRAINT [x_policy_item_datamask_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_item_rowfilter_FK_policy_item_id] FOREIGN KEY([policy_item_id]) REFERENCES [dbo].[x_policy_item] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_item_rowfilter_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_item_rowfilter_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) +GO +CREATE NONCLUSTERED INDEX [x_datamask_type_def_IDX_def_id] ON [x_datamask_type_def] +( + [def_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_datamask_IDX_policy_item_id] ON [x_policy_item_datamask] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +CREATE NONCLUSTERED INDEX [x_policy_item_rowfilter_IDX_policy_item_id] ON [x_policy_item_rowfilter] +( + [policy_item_id] ASC +) +WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] +GO +-- add datamask_options column in x_access_type_def table if not exist +IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_access_type_def' and column_name in('datamask_options','rowfilter_options')) +BEGIN + ALTER TABLE [dbo].[x_access_type_def] ADD [datamask_options] [varchar](1024) DEFAULT NULL NULL,[rowfilter_options] [varchar](1024) DEFAULT NULL NULL; +END +GO +-- add datamask_options column in x_resource_def table if not exist +IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_resource_def' and column_name in ('datamask_options','rowfilter_options')) +BEGIN + ALTER TABLE [dbo].[x_resource_def] ADD [datamask_options] [varchar](1024) DEFAULT NULL NULL,[rowfilter_options] [varchar](1024) DEFAULT NULL NULL; +END +GO + +exit \ No newline at end of file
