From commits-return-4898-archive-asf-public=cust-asf.ponee.io@ranger.apache.org Fri Oct 19 00:23:24 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id D49BA1807A1 for ; Fri, 19 Oct 2018 00:23:22 +0200 (CEST) Received: (qmail 18734 invoked by uid 500); 18 Oct 2018 22:23:22 -0000 Mailing-List: contact commits-help@ranger.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ranger.apache.org Delivered-To: mailing list commits@ranger.apache.org Received: (qmail 18584 invoked by uid 99); 18 Oct 2018 22:23:21 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 18 Oct 2018 22:23:21 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 417CCE0C6A; Thu, 18 Oct 2018 22:23:21 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: abhay@apache.org To: commits@ranger.apache.org Date: Thu, 18 Oct 2018 22:23:28 -0000 Message-Id: In-Reply-To: <1d9db3493b0348928052ed35be8b4918@git.apache.org> References: <1d9db3493b0348928052ed35be8b4918@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [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