Repository: ranger Updated Branches: refs/heads/master ec711360a -> 9fd6acf41
RANGER-2169: Create unique index on service and name column of x_policy table Project: http://git-wip-us.apache.org/repos/asf/ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/9fd6acf4 Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/9fd6acf4 Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/9fd6acf4 Branch: refs/heads/master Commit: 9fd6acf41f1f647c6948036c22d387f7d0e7b296 Parents: ec71136 Author: Pradeep <prad...@apache.org> Authored: Tue Jul 31 22:07:39 2018 +0530 Committer: Pradeep <prad...@apache.org> Committed: Wed Aug 1 09:37:48 2018 +0530 ---------------------------------------------------------------------- .../optimized/current/ranger_core_db_mysql.sql | 6 ++-- ...-add-unique-constraint-on-table-x_policy.sql | 35 +++++++++++++++++++ .../optimized/current/ranger_core_db_oracle.sql | 6 ++-- ...-add-unique-constraint-on-table-x_policy.sql | 35 +++++++++++++++++++ .../current/ranger_core_db_postgres.sql | 6 ++-- ...-add-unique-constraint-on-table-x_policy.sql | 34 ++++++++++++++++++ .../current/ranger_core_db_sqlanywhere.sql | 9 +++-- ...-add-unique-constraint-on-table-x_policy.sql | 36 ++++++++++++++++++++ .../current/ranger_core_db_sqlserver.sql | 15 ++++++-- ...-add-unique-constraint-on-table-x_policy.sql | 29 ++++++++++++++++ 10 files changed, 199 insertions(+), 12 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql index 174204e..e82df40 100644 --- a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql +++ b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql @@ -565,8 +565,8 @@ CREATE TABLE `x_policy` ( `added_by_id` bigint(20) DEFAULT NULL, `upd_by_id` bigint(20) DEFAULT NULL, `version` bigint(20) DEFAULT NULL, -`service` bigint(20) DEFAULT NULL, -`name` varchar(512) DEFAULT NULL, +`service` bigint(20) NOT NULL, +`name` varchar(512) NOT NULL, `policy_type` int(11) DEFAULT 0, `description` varchar(1024) DEFAULT NULL, `resource_signature` varchar(128) DEFAULT NULL, @@ -581,6 +581,7 @@ KEY `x_policy_cr_time` (`create_time`), KEY `x_policy_up_time` (`update_time`), KEY `x_policy_service` (`service`), KEY `x_policy_resource_signature` (`resource_signature`), +UNIQUE KEY `x_policy_UK_name_service` (`name`(180),`service`), CONSTRAINT `x_policy_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_policy_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_policy_FK_service` FOREIGN KEY (`service`) REFERENCES `x_service` (`id`) @@ -1343,6 +1344,7 @@ 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 ('030',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('031',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('032',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); +INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_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,UTC_TIMESTAMP(),UTC_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,UTC_TIMESTAMP(),UTC_TIMESTAMP(),1,1,1); http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql b/security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql new file mode 100644 index 0000000..8deb285 --- /dev/null +++ b/security-admin/db/mysql/patches/033-add-unique-constraint-on-table-x_policy.sql @@ -0,0 +1,35 @@ +-- 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 procedure if exists create_unique_constraint_on_name_service; + +delimiter ;; +create procedure create_unique_constraint_on_name_service() begin + /* check tables exist or not */ + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('service','name')) then + /* check unique constraint exist on service and name column or not */ + if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('name') and column_key in('UNI','MUL')) then + if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_policy' and constraint_name='x_policy_UK_name_service') then + UPDATE x_policy set name=concat(name,'-duplicate-',id) where id in (select id from (select id from x_policy where concat(service,name) in (select concat(service,name) from x_policy group by service,name having count(*) >1)) as tmp); + ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_name_service(name(180),service); + end if; + end if; + end if; +end;; + +delimiter ; +call create_unique_constraint_on_name_service(); + +drop procedure if exists create_unique_constraint_on_name_service; http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql index 2d18b50..9aadbde 100644 --- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql +++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql @@ -449,8 +449,8 @@ update_time DATE DEFAULT NULL NULL, added_by_id NUMBER(20) DEFAULT NULL NULL, upd_by_id NUMBER(20) DEFAULT NULL NULL, version NUMBER(20) DEFAULT NULL NULL, -service NUMBER(20) DEFAULT NULL NULL, -name VARCHAR(512) DEFAULT NULL NULL, +service NUMBER(20) NOT NULL, +name VARCHAR(512) NOT NULL, policy_type NUMBER(11) DEFAULT '0' NULL, description VARCHAR(1024) DEFAULT NULL NULL, resource_signature VARCHAR(128) DEFAULT NULL NULL, @@ -459,6 +459,7 @@ is_audit_enabled NUMBER(1) DEFAULT '0' NOT NULL, policy_options varchar(4000) DEFAULT NULL NULL, policy_priority NUMBER(11) DEFAULT 0 NOT 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), CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id), CONSTRAINT x_policy_FK_service FOREIGN KEY (service) REFERENCES x_service (id) @@ -1319,6 +1320,7 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '030',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '031',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '032',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '033',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,1,3,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),1,1,1); INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,1,1,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),1,1,1); http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql b/security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql new file mode 100644 index 0000000..dc97b37 --- /dev/null +++ b/security-admin/db/oracle/patches/033-add-unique-constraint-on-table-x_policy.sql @@ -0,0 +1,35 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +DECLARE + v_count number:=0; + sql_stmt VARCHAR2(1000); + duplicate VARCHAR2(11):='-duplicate-'; +BEGIN + select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('NAME','SERVICE'); + if (v_count = 2) then + v_count:=0; + select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_NAME_SERVICE' and constraint_type='U'; + if (v_count = 0) then + v_count:=0; + select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('NAME','SERVICE') and index_name='X_POLICY_UK_NAME_SERVICE'; + if (v_count = 0) THEN + sql_stmt := 'UPDATE x_policy set name=concat(concat(name,:1),id) where id in (select id from (select id from x_policy where concat(service,name) in (select concat(service,name) from x_policy group by service,name having count(*) >1)))'; + EXECUTE IMMEDIATE sql_stmt USING duplicate; + EXECUTE IMMEDIATE 'ALTER TABLE X_POLICY ADD CONSTRAINT x_policy_UK_name_service UNIQUE (NAME,SERVICE)'; + end if; + commit; + end if; + end if; +end;/ http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/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 0e0344d..4766910 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 @@ -456,8 +456,8 @@ update_time TIMESTAMP DEFAULT NULL NULL, added_by_id BIGINT DEFAULT NULL NULL, upd_by_id BIGINT DEFAULT NULL NULL, version BIGINT DEFAULT NULL NULL, -service BIGINT DEFAULT NULL NULL, -name VARCHAR(512) DEFAULT NULL NULL, +service BIGINT NOT NULL, +name VARCHAR(512) NOT NULL, policy_type int DEFAULT 0 NULL, description VARCHAR(1024) DEFAULT NULL NULL, resource_signature VARCHAR(128) DEFAULT NULL NULL, @@ -466,6 +466,7 @@ is_audit_enabled BOOLEAN DEFAULT '0' NOT NULL, policy_options VARCHAR(4000) DEFAULT NULL NULL, policy_priority INT DEFAULT 0 NOT 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), CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), CONSTRAINT x_policy_FK_service FOREIGN KEY(service) REFERENCES x_service(id) @@ -1432,6 +1433,7 @@ 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 ('030',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 ('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 ('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); http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql b/security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql new file mode 100644 index 0000000..e3ac945 --- /dev/null +++ b/security-admin/db/postgres/patches/033-add-unique-constraint-on-table-x_policy.sql @@ -0,0 +1,34 @@ +-- 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. +select 'delimiter start'; +CREATE OR REPLACE FUNCTION create_unique_constraint_on_policyname() +RETURNS void AS $$ +DECLARE + v_attnum integer := 0; +BEGIN + select attnum into v_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('name'); + IF v_attnum > 0 THEN + IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_policy') and conname='x_policy_uk_name_service' and contype='u') THEN + IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_policy') and indkey[0]=v_attnum) THEN + UPDATE x_policy set name=(name || '-duplicate-' || id) where id in (select id from (select id from x_policy where service || name in (select service || name from x_policy group by service,name having count(*) >1)) as tmp); + ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_name_service UNIQUE(name,service); + END IF; + END IF; + END IF; + +END; +$$ LANGUAGE plpgsql; +select create_unique_constraint_on_policyname(); +select 'delimiter end'; http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/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 4cf295c..82f638d 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 @@ -334,8 +334,8 @@ create table dbo.x_policy ( added_by_id bigint DEFAULT NULL NULL, upd_by_id bigint DEFAULT NULL NULL, version bigint DEFAULT NULL NULL, - service bigint DEFAULT NULL NULL, - name varchar(512) DEFAULT NULL NULL, + service bigint NOT NULL, + name varchar(512) NOT NULL, policy_type int DEFAULT 0 NULL, description varchar(1024) DEFAULT NULL NULL, resource_signature varchar(128) DEFAULT NULL NULL, @@ -343,7 +343,8 @@ 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, - CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id) + CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id), + CONSTRAINT x_policy_UK_name_service UNIQUE NONCLUSTERED (name,service) ) GO create table dbo.x_service_config_def ( @@ -1586,6 +1587,8 @@ 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 ('032',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 ('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 ('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); http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql b/security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql new file mode 100644 index 0000000..ace31d6 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/033-add-unique-constraint-on-table-x_policy.sql @@ -0,0 +1,36 @@ +-- 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. +BEGIN +DECLARE tableID INT = 0; +DECLARE columnID INT = 0; +DECLARE guTableID INT = 0; +DECLARE guColumnID INT = 0; + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname='name') THEN + IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_name_service') THEN + select table_id into tableID from SYS.SYSTAB where table_name = 'x_policy'; + select column_id into columnID from SYS.SYSTABCOL where table_id=tableID and column_name = 'name'; + IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id=columnID) THEN + UPDATE x_policy set name=(name || '-duplicate-' || id) where id in (select id from (select id from x_policy where service || name in (select service || name from x_policy group by service,name having count(*) >1)) as tmp); + DROP INDEX x_policy_service; + ALTER TABLE dbo.x_policy DROP CONSTRAINT x_policy_FK_service; + ALTER TABLE dbo.x_policy ALTER name varchar(512) NOT NULL, ALTER service bigint NOT NULL ; + ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_name_service UNIQUE NONCLUSTERED (name,service); + ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_FK_service FOREIGN KEY(service) REFERENCES dbo.x_service (id); + CREATE NONCLUSTERED INDEX x_policy_service ON dbo.x_policy(service ASC); + END IF; + END IF; + END IF; +END +GO http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql index 09701e2..46d92c9 100644 --- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql +++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql @@ -461,6 +461,10 @@ IF (OBJECT_ID('x_plugin_info_UK') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_plugin_info] DROP CONSTRAINT x_plugin_info_UK END +IF (OBJECT_ID('x_policy$x_policy_UK_name_service') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy] DROP CONSTRAINT x_policy$x_policy_UK_name_service +END IF (OBJECT_ID('vx_trx_log') IS NOT NULL) BEGIN DROP VIEW [dbo].[vx_trx_log] @@ -1148,8 +1152,8 @@ CREATE TABLE [dbo].[x_policy] ( [added_by_id] [bigint] DEFAULT NULL NULL, [upd_by_id] [bigint] DEFAULT NULL NULL, [version] [bigint] DEFAULT NULL NULL, - [service] [bigint] DEFAULT NULL NULL, - [name] [varchar](512) DEFAULT NULL NULL, + [service] [bigint] NOT NULL, + [name] [varchar](512) NOT NULL, [policy_type] [int] DEFAULT 0 NULL, [description] [varchar](1024) DEFAULT NULL NULL, [resource_signature] [varchar](128) DEFAULT NULL NULL, @@ -1160,7 +1164,11 @@ CREATE TABLE [dbo].[x_policy] ( PRIMARY KEY CLUSTERED ( [id] ASC -)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], +CONSTRAINT [x_policy$x_policy_UK_name_service] UNIQUE NONCLUSTERED +( + [name] ASC, [service] ASC +)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON @@ -3099,6 +3107,7 @@ 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 ('030',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 ('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 ('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); http://git-wip-us.apache.org/repos/asf/ranger/blob/9fd6acf4/security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql b/security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql new file mode 100644 index 0000000..075fe86 --- /dev/null +++ b/security-admin/db/sqlserver/patches/033-add-unique-constraint-on-table-x_policy.sql @@ -0,0 +1,29 @@ + + +-- 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 EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name = 'name') +BEGIN + IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='name' and constraint_name = 'x_policy$x_policy_UK_name_service') + BEGIN + IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_name_service' and CONSTRAINT_TYPE='UNIQUE') + BEGIN + UPDATE [dbo].[x_policy] set name=concat(name, '-duplicate-',id) where id in (select id from (select id from [dbo].[x_policy] where concat(service,name) in (select concat(service,name) from [dbo].[x_policy] group by service,name having count(*) >1)) as tmp); + ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_name_service] UNIQUE ([name],[service]); + END + END +END +GO +exit
