Repository: ranger Updated Branches: refs/heads/ranger-0.7 b050618ae -> 7deed8eb5
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/7deed8eb Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/7deed8eb Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/7deed8eb Branch: refs/heads/ranger-0.7 Commit: 7deed8eb5abd9cf25e70ba6ece4d7b17c57ca119 Parents: b050618 Author: Pradeep <prad...@apache.org> Authored: Tue Oct 9 11:35:12 2018 +0530 Committer: Pradeep <prad...@apache.org> Committed: Tue Oct 9 14:05:50 2018 +0530 ---------------------------------------------------------------------- ...-add-unique-constraint-on-table-x_policy.sql | 35 +++++++++++++++++++ ...-add-unique-constraint-on-table-x_policy.sql | 35 +++++++++++++++++++ ...-add-unique-constraint-on-table-x_policy.sql | 34 ++++++++++++++++++ ...-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 ++++++++++++++++ 6 files changed, 181 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ranger/blob/7deed8eb/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/7deed8eb/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/7deed8eb/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/7deed8eb/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/7deed8eb/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 a79c954..bf73c34 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] @@ -1136,8 +1140,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, @@ -1146,7 +1150,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 @@ -2990,6 +2998,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 ('027',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('028',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('029',CURRENT_TIMESTAMP,'Ranger 0.7.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 0.7.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 0.7.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/7deed8eb/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
