You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by ab...@apache.org on 2018/10/18 22:23:28 UTC
[8/9] ranger git commit: RANGER-2203,
RANGER-2219: Review and update database schema for ranger policies
and tag objects to minimize database queries/updates
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
index bad32ef..a123911 100644
--- a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
+++ b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
@@ -465,6 +465,7 @@ is_enabled BOOLEAN DEFAULT '0' NOT NULL,
is_audit_enabled BOOLEAN DEFAULT '0' NOT NULL,
policy_options VARCHAR(4000) DEFAULT NULL NULL,
policy_priority INT DEFAULT 0 NOT NULL,
+policy_text TEXT DEFAULT NULL NULL,
primary key(id),
CONSTRAINT x_policy_uk_name_service UNIQUE(name,service),
CONSTRAINT x_policy_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
@@ -933,6 +934,7 @@ version BIGINT DEFAULT NULL NULL,
name VARCHAR(255) NOT NULL,
source VARCHAR(128) DEFAULT NULL NULL,
is_enabled BOOLEAN DEFAULT '0' NOT NULL,
+tag_attrs_def_text TEXT DEFAULT NULL NULL,
PRIMARY KEY (id),
CONSTRAINT x_tag_def_UK_guid UNIQUE (guid),
CONSTRAINT x_tag_def_UK_name UNIQUE (name),
@@ -955,6 +957,7 @@ version BIGINT DEFAULT NULL NULL,
type BIGINT NOT NULL,
owned_by SMALLINT DEFAULT 0 NOT NULL,
policy_options VARCHAR(4000) DEFAULT NULL NULL,
+tag_attrs_text TEXT DEFAULT NULL NULL,
primary key (id),
CONSTRAINT x_tag_UK_guid UNIQUE (guid),
CONSTRAINT x_tag_FK_type FOREIGN KEY (type) REFERENCES x_tag_def (id),
@@ -976,6 +979,8 @@ version BIGINT DEFAULT NULL NULL,
service_id BIGINT NOT NULL,
resource_signature VARCHAR(128) DEFAULT NULL NULL,
is_enabled BOOLEAN DEFAULT '1' NOT NULL,
+service_resource_elements_text TEXT DEFAULT NULL NULL,
+tags_text TEXT DEFAULT NULL 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),
@@ -983,63 +988,6 @@ CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_p
CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-DROP TABLE IF EXISTS x_service_resource_element CASCADE;
-DROP SEQUENCE IF EXISTS x_service_resource_element_seq;
-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)
-);
-
-DROP TABLE IF EXISTS x_tag_attr_def CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_attr_def_seq;
-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)
-);
-
-
-DROP TABLE IF EXISTS x_tag_attr CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_attr_seq;
-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)
-);
-
DROP TABLE IF EXISTS x_tag_resource_map CASCADE;
DROP SEQUENCE IF EXISTS x_tag_resource_map_seq;
CREATE SEQUENCE x_tag_resource_map_seq;
@@ -1060,24 +1008,6 @@ CONSTRAINT x_tag_res_map_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_p
CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-DROP TABLE IF EXISTS x_service_resource_element_val CASCADE;
-DROP SEQUENCE IF EXISTS x_service_res_el_val_seq;
-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)
-);
-
DROP TABLE IF EXISTS x_policy_item_datamask;
DROP SEQUENCE IF EXISTS x_policy_item_datamask_seq;
DROP TABLE IF EXISTS x_datamask_type_def;
@@ -1231,6 +1161,133 @@ session_id varchar(255) DEFAULT NULL,
primary key (id)
);
+DROP TABLE IF EXISTS x_policy_ref_resource CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+CREATE SEQUENCE x_policy_ref_resource_seq;
+CREATE TABLE x_policy_ref_resource(
+id BIGINT DEFAULT nextval('x_policy_ref_resource_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+resource_def_id BIGINT NOT NULL,
+resource_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY(resource_def_id) REFERENCES x_resource_def(id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+CREATE SEQUENCE x_policy_ref_access_type_seq;
+CREATE TABLE x_policy_ref_access_type(
+id BIGINT DEFAULT nextval('x_policy_ref_access_type_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+access_def_id BIGINT NOT NULL,
+access_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY(access_def_id) REFERENCES x_access_type_def(id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_condition_seq;
+CREATE SEQUENCE x_policy_ref_condition_seq;
+CREATE TABLE x_policy_ref_condition(
+id BIGINT DEFAULT nextval('x_policy_ref_condition_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+condition_def_id BIGINT NOT NULL,
+condition_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY(condition_def_id) REFERENCES x_policy_condition_def(id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+CREATE SEQUENCE x_policy_ref_datamask_type_seq;
+CREATE TABLE x_policy_ref_datamask_type(
+id BIGINT DEFAULT nextval('x_policy_ref_datamask_type_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+datamask_def_id BIGINT NOT NULL,
+datamask_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmk_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_dmk_FK_dmk_def_id FOREIGN KEY(datamask_def_id) REFERENCES x_datamask_type_def(id),
+CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_dmk_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+CREATE SEQUENCE x_policy_ref_user_seq;
+CREATE TABLE x_policy_ref_user(
+id BIGINT DEFAULT nextval('x_policy_ref_user_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+user_id BIGINT NOT NULL,
+user_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+CREATE SEQUENCE x_policy_ref_group_seq;
+CREATE TABLE x_policy_ref_group(
+id BIGINT DEFAULT nextval('x_policy_ref_group_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+group_id BIGINT NOT NULL,
+group_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+
CREATE INDEX xa_access_audit_added_by_id ON xa_access_audit(added_by_id);
CREATE INDEX xa_access_audit_upd_by_id ON xa_access_audit(upd_by_id);
CREATE INDEX xa_access_audit_cr_time ON xa_access_audit(create_time);
@@ -1340,21 +1397,10 @@ 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);
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);
-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);
-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);
-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);
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);
-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);
CREATE INDEX x_service_config_def_IDX_def_id ON x_service_config_def(def_id);
CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id);
CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id);
@@ -1376,8 +1422,6 @@ CREATE INDEX x_policy_item_user_perm_IDX_user_id ON x_policy_item_user_perm(user
CREATE INDEX x_policy_item_group_perm_IDX_policy_item_id ON x_policy_item_group_perm(policy_item_id);
CREATE INDEX x_policy_item_group_perm_IDX_group_id ON x_policy_item_group_perm(group_id);
CREATE INDEX x_service_resource_IDX_service_id ON x_service_resource(service_id);
-CREATE INDEX x_service_resource_element_IDX_res_id ON x_service_resource_element(res_id);
-CREATE INDEX x_service_resource_element_IDX_res_def_id ON x_service_resource_element(res_def_id);
CREATE INDEX x_datamask_type_def_IDX_def_id ON x_datamask_type_def(def_id);
CREATE INDEX x_policy_item_datamask_IDX_policy_item_id ON x_policy_item_datamask(policy_item_id);
CREATE INDEX x_policy_item_rowfilter_IDX_policy_item_id ON x_policy_item_rowfilter(policy_item_id);
@@ -1434,6 +1478,9 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('031',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('032',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('034',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('036',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,3,current_timestamp,current_timestamp,1,1,1);
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,1,current_timestamp,current_timestamp,1,1,1);
@@ -1471,7 +1518,8 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10014',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10015',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10018',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10019',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('JAVA_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
DROP VIEW IF EXISTS vx_trx_log;
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..cb87cd0
--- /dev/null
+++ b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,197 @@
+-- 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_policy_ref_resource CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+CREATE SEQUENCE x_policy_ref_resource_seq;
+CREATE TABLE x_policy_ref_resource(
+id BIGINT DEFAULT nextval('x_policy_ref_resource_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+resource_def_id BIGINT NOT NULL,
+resource_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY(resource_def_id) REFERENCES x_resource_def(id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+CREATE SEQUENCE x_policy_ref_access_type_seq;
+CREATE TABLE x_policy_ref_access_type(
+id BIGINT DEFAULT nextval('x_policy_ref_access_type_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+access_def_id BIGINT NOT NULL,
+access_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY(access_def_id) REFERENCES x_access_type_def(id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_condition_seq;
+CREATE SEQUENCE x_policy_ref_condition_seq;
+CREATE TABLE x_policy_ref_condition(
+id BIGINT DEFAULT nextval('x_policy_ref_condition_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+condition_def_id BIGINT NOT NULL,
+condition_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY(condition_def_id) REFERENCES x_policy_condition_def(id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+CREATE SEQUENCE x_policy_ref_datamask_type_seq;
+CREATE TABLE x_policy_ref_datamask_type(
+id BIGINT DEFAULT nextval('x_policy_ref_datamask_type_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+datamask_def_id BIGINT NOT NULL,
+datamask_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmk_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_dmk_FK_dmk_def_id FOREIGN KEY(datamask_def_id) REFERENCES x_datamask_type_def(id),
+CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_dmk_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+CREATE SEQUENCE x_policy_ref_user_seq;
+CREATE TABLE x_policy_ref_user(
+id BIGINT DEFAULT nextval('x_policy_ref_user_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+user_id BIGINT NOT NULL,
+user_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+CREATE SEQUENCE x_policy_ref_group_seq;
+CREATE TABLE x_policy_ref_group(
+id BIGINT DEFAULT nextval('x_policy_ref_group_seq'::regclass),
+guid VARCHAR(1024) 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_id BIGINT NOT NULL,
+group_id BIGINT NOT NULL,
+group_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION add_x_policy_json()
+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') and attname='policy_text';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_policy ADD COLUMN policy_text TEXT DEFAULT NULL NULL;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select add_x_policy_json();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+ OPEN curs;
+ loop
+ FETCH curs INTO tableName,constraintName;
+ EXIT WHEN NOT FOUND;
+ query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+ execute query;
+ end loop;
+ close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+ perform remove_foreign_key('x_policy_item');
+ perform remove_foreign_key('x_policy_item_access');
+ perform remove_foreign_key('x_policy_item_condition');
+ perform remove_foreign_key('x_policy_item_datamask');
+ perform remove_foreign_key('x_policy_item_group_perm');
+ perform remove_foreign_key('x_policy_resource');
+ perform remove_foreign_key('x_policy_resource_map');
+ perform remove_foreign_key('x_policy_item_user_perm');
+ perform remove_foreign_key('x_policy_item_rowfilter');
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';
+
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..e5ed272
--- /dev/null
+++ b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,79 @@
+-- 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 denormalize_tag_tables()
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION denormalize_tag_tables()
+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_tag_def') and attname='tag_attrs_def_text';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_tag_def ADD COLUMN tag_attrs_def_text TEXT DEFAULT NULL NULL;
+ END IF;
+ select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_tag') and attname='tag_attrs_text';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_tag ADD COLUMN tag_attrs_text TEXT DEFAULT NULL NULL;
+ END IF;
+ select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='service_resource_elements_text';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_service_resource ADD COLUMN service_resource_elements_text TEXT DEFAULT NULL NULL;
+ END IF;
+ select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='tags_text';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_service_resource ADD COLUMN tags_text TEXT DEFAULT NULL NULL;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select denormalize_tag_tables();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+ OPEN curs;
+ loop
+ FETCH curs INTO tableName,constraintName;
+ EXIT WHEN NOT FOUND;
+ query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+ execute query;
+ end loop;
+ close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+ perform remove_foreign_key('x_tag_attr_def');
+ perform remove_foreign_key('x_tag_attr');
+ perform remove_foreign_key('x_service_resource_element');
+ perform remove_foreign_key('x_service_resource_element_val');
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
index 9482992..142302a 100644
--- a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
+++ b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
@@ -343,6 +343,7 @@ create table dbo.x_policy (
is_audit_enabled tinyint DEFAULT 0 NOT NULL,
policy_options varchar(4000) DEFAULT NULL NULL,
policy_priority int DEFAULT 0 NOT NULL,
+ policy_text text DEFAULT NULL NULL,
CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_policy_UK_name_service UNIQUE NONCLUSTERED (name,service)
)
@@ -678,6 +679,7 @@ CREATE TABLE dbo.x_tag_def(
name varchar(255) NOT NULL,
source varchar(128) DEFAULT NULL NULL,
is_enabled tinyint DEFAULT 0 NOT NULL,
+ tag_attrs_def_text text DEFAULT NULL 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)
@@ -694,6 +696,7 @@ CREATE TABLE dbo.x_tag(
type bigint NOT NULL,
owned_by smallint DEFAULT 0 NOT NULL,
policy_options varchar(4000) DEFAULT NULL NULL,
+ tag_attrs_text text DEFAULT NULL NULL,
CONSTRAINT x_tag_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_tag_UK_guid UNIQUE NONCLUSTERED (guid)
)
@@ -709,47 +712,12 @@ CREATE TABLE dbo.x_service_resource(
service_id bigint NOT NULL,
resource_signature varchar(128) DEFAULT NULL NULL,
is_enabled tinyint DEFAULT 1 NOT NULL,
+ service_resource_elements_text text DEFAULT NULL NULL,
+ tags_text text DEFAULT NULL 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,
@@ -763,18 +731,6 @@ CREATE TABLE dbo.x_tag_resource_map(
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
CREATE TABLE dbo.x_datamask_type_def(
id bigint IDENTITY NOT NULL,
guid varchar(64) DEFAULT NULL NULL,
@@ -886,6 +842,90 @@ CREATE TABLE dbo.x_ugsync_audit_info(
CONSTRAINT x_ugsync_audit_info_PK_id PRIMARY KEY CLUSTERED(id)
)
GO
+create table dbo.x_policy_ref_resource (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ resource_def_id bigint NOT NULL,
+ resource_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_res_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE NONCLUSTERED (policy_id, resource_def_id)
+)
+GO
+create table dbo.x_policy_ref_access_type (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ access_def_id bigint NOT NULL,
+ access_type_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_acc_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE NONCLUSTERED (policy_id, access_def_id)
+)
+GO
+create table dbo.x_policy_ref_condition (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ condition_def_id bigint NOT NULL,
+ condition_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_cond_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE NONCLUSTERED (policy_id, condition_def_id)
+)
+GO
+create table dbo.x_policy_ref_datamask_type (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ datamask_def_id bigint NOT NULL,
+ datamask_type_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_dmk_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE NONCLUSTERED (policy_id, datamask_def_id)
+)
+GO
+create table dbo.x_policy_ref_user (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ user_id bigint NOT NULL,
+ user_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_user_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE NONCLUSTERED (policy_id, user_id)
+)
+GO
+create table dbo.x_policy_ref_group (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ group_id bigint NOT NULL,
+ group_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_group_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE NONCLUSTERED (policy_id, group_id)
+)
+GO
ALTER TABLE dbo.x_asset ADD CONSTRAINT x_asset_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user(id)
GO
@@ -1123,26 +1163,6 @@ ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_upd_by_id FOR
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)
@@ -1151,12 +1171,6 @@ ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_added_by_id F
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
ALTER TABLE dbo.x_service ADD CONSTRAINT x_service_FK_tag_service FOREIGN KEY(tag_service) REFERENCES dbo.x_service (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)
@@ -1193,7 +1207,55 @@ ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map_FK_policy_i
GO
ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map_FK_policy_label_id FOREIGN KEY (policy_label_id) REFERENCES dbo.x_policy_label (id)
GO
-ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT [x_policy_label_map$x_policy_label_map_pid_plid] UNIQUE (policy_id, policy_label_id)
+ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map$x_policy_label_map_pid_plid UNIQUE (policy_id, policy_label_id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_resource_def_id FOREIGN KEY (resource_def_id) REFERENCES dbo.x_resource_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_access_def_id FOREIGN KEY (access_def_id) REFERENCES dbo.x_access_type_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_condition_def_id FOREIGN KEY (condition_def_id) REFERENCES dbo.x_policy_condition_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_datamask_def_id FOREIGN KEY (datamask_def_id) REFERENCES dbo.x_datamask_type_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_user_id FOREIGN KEY (user_id) REFERENCES dbo.x_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_group_id FOREIGN KEY (group_id) REFERENCES dbo.x_group (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
GO
CREATE NONCLUSTERED INDEX x_asset_cr_time ON dbo.x_asset(create_time ASC)
GO
@@ -1415,22 +1477,6 @@ CREATE NONCLUSTERED INDEX x_service_res_IDX_added_by_id ON dbo.x_service_resourc
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)
@@ -1439,12 +1485,6 @@ CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_added_by_id ON dbo.x_tag_resource_ma
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
CREATE NONCLUSTERED INDEX x_service_config_def_IDX_def_id ON dbo.x_service_config_def(def_id ASC)
GO
CREATE NONCLUSTERED INDEX x_resource_def_IDX_def_id ON dbo.x_resource_def(def_id ASC)
@@ -1487,10 +1527,6 @@ CREATE NONCLUSTERED INDEX x_policy_item_group_perm_IDX_group_id ON dbo.x_policy_
GO
CREATE NONCLUSTERED INDEX x_service_resource_IDX_service_id ON dbo.x_service_resource(service_id ASC)
GO
-CREATE NONCLUSTERED INDEX x_service_resource_element_IDX_res_id ON dbo.x_service_resource_element(res_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_service_resource_element_IDX_res_def_id ON dbo.x_service_resource_element(res_def_id ASC)
-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)
@@ -1589,6 +1625,12 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('034',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('036',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,3,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
@@ -1661,9 +1703,11 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10015',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016,CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10019',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10018,CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('JAVA_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..24d072f
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,180 @@
+
+-- 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
+create table dbo.x_policy_ref_resource (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ resource_def_id bigint NOT NULL,
+ resource_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_res_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE NONCLUSTERED (policy_id, resource_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_access_type (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ access_def_id bigint NOT NULL,
+ access_type_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_acc_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE NONCLUSTERED (policy_id, access_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_condition (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ condition_def_id bigint NOT NULL,
+ condition_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_cond_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE NONCLUSTERED (policy_id, condition_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_datamask_type (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ datamask_def_id bigint NOT NULL,
+ datamask_type_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_dmk_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE NONCLUSTERED (policy_id, datamask_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_user (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ user_id bigint NOT NULL,
+ user_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_user_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE NONCLUSTERED (policy_id, user_id)
+)
+GO
+
+create table dbo.x_policy_ref_group (
+ id bigint IDENTITY NOT NULL,
+ guid varchar(1024) 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_id bigint NOT NULL,
+ group_id bigint NOT NULL,
+ group_name varchar(4000) DEFAULT NULL NULL,
+ CONSTRAINT x_policy_ref_group_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE NONCLUSTERED (policy_id, group_id)
+)
+GO
+
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname='policy_text') THEN
+ ALTER TABLE dbo.x_policy ADD (policy_text text DEFAULT NULL NULL);
+END IF;
+GO
+
+IF EXISTS (
+ SELECT 1
+ FROM sysobjects
+ WHERE NAME = 'removeForeignKeyConstraint'
+ AND TYPE = 'P'
+)
+BEGIN
+ drop procedure dbo.removeForeignKeyConstraint
+END
+GO
+
+CREATE PROCEDURE dbo.removeForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE cur CURSOR FOR
+ select 'alter table dbo.' + table_name + ' drop constraint ' + role
+ from SYS.SYSFOREIGNKEYS
+ where foreign_creator ='dbo' and foreign_tname = table_name
+
+ OPEN cur WITH HOLD
+ fetch cur into @stmt
+ if (@@sqlstatus = 2)
+ BEGIN
+ close cur
+ DEALLOCATE CURSOR cur
+ END
+
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_access')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_condition')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_datamask')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_group_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_user_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_rowfilter')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource_map')
+GO
+
+
+exit
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..1fdbfaa
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,71 @@
+-- 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 = 'x_tag_def' and cname = 'tag_attrs_def_text') THEN
+ ALTER TABLE dbo.x_tag_def ADD tag_attrs_def_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_tag' and cname = 'tag_attrs_text') THEN
+ ALTER TABLE dbo.x_tag ADD tag_attrs_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'service_resource_elements_text') THEN
+ ALTER TABLE dbo.x_service_resource ADD service_resource_elements_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'tags_text') THEN
+ ALTER TABLE dbo.x_service_resource ADD tags_text text DEFAULT NULL NULL;
+END IF;
+GO
+
+CREATE PROCEDURE dbo.removeTagForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE cur CURSOR FOR
+ select 'alter table dbo.' + table_name + ' drop constraint ' + role
+ from SYS.SYSFOREIGNKEYS
+ where foreign_creator ='dbo' and foreign_tname = table_name
+
+ OPEN cur WITH HOLD
+ fetch cur into @stmt
+ if (@@sqlstatus = 2)
+ BEGIN
+ close cur
+ DEALLOCATE CURSOR cur
+ END
+
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr_def')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element_val')
+GO
+
+exit