This is an automated email from the ASF dual-hosted git repository. pradeep pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/ranger.git
commit 8968eaef8693b39a32f10c2feebf0035b34e3763 Author: pradeep <prad...@apache.org> AuthorDate: Mon Nov 22 09:28:37 2021 +0530 RANGER-3435: Add unique index on guid, service and zone_id column of x_policy table --- .../db/mysql/optimized/current/ranger_core_db_mysql.sql | 2 +- ...unique-constraint-on-x_policy-table-guid-service-column.sql | 6 +++--- .../db/oracle/optimized/current/ranger_core_db_oracle.sql | 2 +- ...unique-constraint-on-x_policy-table-guid-service-column.sql | 10 +++++----- .../db/postgres/optimized/current/ranger_core_db_postgres.sql | 2 +- ...unique-constraint-on-x_policy-table-guid-service-column.sql | 10 ++++++---- .../optimized/current/ranger_core_db_sqlanywhere.sql | 2 +- ...unique-constraint-on-x_policy-table-guid-service-column.sql | 6 +++--- .../sqlserver/optimized/current/ranger_core_db_sqlserver.sql | 4 ++-- ...unique-constraint-on-x_policy-table-guid-service-column.sql | 8 ++++---- 10 files changed, 27 insertions(+), 25 deletions(-) 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 e444e78..df27a9d 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 @@ -694,7 +694,7 @@ 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_zone` (`name`(180),`service`, `zone_id`), -UNIQUE KEY `x_policy_UK_guid_service` (`guid`(180),`service`), +UNIQUE KEY `x_policy_UK_guid_service_zone` (`guid`(180),`service`, `zone_id`), UNIQUE KEY `x_policy_UK_service_signature` (`service`,`resource_signature`), 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`), diff --git a/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql index 357b7ef..65a78c9 100644 --- a/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql +++ b/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql @@ -18,10 +18,10 @@ drop procedure if exists create_unique_constraint_on_guid_service; delimiter ;; create procedure create_unique_constraint_on_guid_service() begin /* check table and columns exist or not */ - if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('guid','service')) then + if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('guid','service','zone_id')) then /* check unique constraint exist on guid and service column or not */ - if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_policy' and constraint_name='x_policy_UK_guid_service') then - ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_guid_service(guid(180),service); + if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_policy' and constraint_name='x_policy_UK_guid_service_zone') then + ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_guid_service_zone(guid(180),service,zone_id); end if; end if; end;; 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 9e5da70..5434f58 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 @@ -794,7 +794,7 @@ policy_text CLOB DEFAULT NULL NULL, zone_id NUMBER(20) DEFAULT '1' NOT NULL, primary key (id), CONSTRAINT x_policy_UK_name_service_zone UNIQUE (name,service,zone_id), -CONSTRAINT x_policy_UK_guid_service UNIQUE (guid,service), +CONSTRAINT x_policy_UK_guid_service_zone UNIQUE (guid,service,zone_id), CONSTRAINT x_policy_UK_service_signature UNIQUE (service,resource_signature), 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), diff --git a/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql index 580841c..aea3efd 100644 --- a/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql +++ b/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql @@ -16,14 +16,14 @@ DECLARE v_count number:=0; BEGIN - select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('GUID','SERVICE'); - if (v_count = 2) THEN + select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('GUID','SERVICE','ZONE_ID'); + if (v_count = 3) THEN v_count:=0; - select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_GUID_SERVICE' and constraint_type='U'; + select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_GUID_SERVICE_ZONE' and constraint_type='U'; if (v_count = 0) THEN - select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('GUID','SERVICE') and index_name='X_POLICY_UK_GUID_SERVICE'; + select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('GUID','SERVICE','ZONE_ID') and index_name='X_POLICY_UK_GUID_SERVICE_ZONE'; if (v_count = 0) THEN - execute immediate 'ALTER TABLE X_POLICY ADD CONSTRAINT X_POLICY_UK_GUID_SERVICE UNIQUE (GUID,SERVICE)'; + execute immediate 'ALTER TABLE X_POLICY ADD CONSTRAINT X_POLICY_UK_GUID_SERVICE_ZONE UNIQUE (GUID,SERVICE,ZONE_ID)'; end if; commit; end if; 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 9fd4503..3cfdb81 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 @@ -623,7 +623,7 @@ policy_text TEXT DEFAULT NULL NULL, zone_id BIGINT DEFAULT '1' NOT NULL, primary key(id), CONSTRAINT x_policy_uk_name_service_zone UNIQUE(name,service,zone_id), -CONSTRAINT x_policy_uk_guid_service UNIQUE(guid,service), +CONSTRAINT x_policy_uk_guid_service_zone UNIQUE(guid,service,zone_id), CONSTRAINT x_policy_uk_service_signature UNIQUE(service,resource_signature), 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), diff --git a/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql index 81718aa..e0b9e92 100644 --- a/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql +++ b/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql @@ -19,13 +19,15 @@ RETURNS void AS $$ DECLARE v_attnum1 integer := 0; v_attnum2 integer := 0; + v_attnum3 integer := 0; BEGIN select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('guid'); select attnum into v_attnum2 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('service'); - IF v_attnum1 > 0 and v_attnum2 > 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_guid_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_attnum1 and indkey[1]=v_attnum2) THEN - ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_guid_service UNIQUE(guid,service); + select attnum into v_attnum3 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('zone_id'); + IF v_attnum1 > 0 and v_attnum2 > 0 and v_attnum3 > 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_guid_service_zone' 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_attnum1 and indkey[1]=v_attnum2 and indkey[2]=v_attnum3) THEN + ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_guid_service_zone UNIQUE(guid,service,zone_id); END IF; END IF; END IF; 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 bdccecc..40dafaf 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 @@ -574,7 +574,7 @@ create table dbo.x_policy ( zone_id bigint DEFAULT '1' NOT NULL, CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id), CONSTRAINT x_policy_UK_name_service_zone UNIQUE NONCLUSTERED (name,service,zone_id), - CONSTRAINT x_policy_UK_guid_service UNIQUE NONCLUSTERED (guid,service), + CONSTRAINT x_policy_UK_guid_service_zone UNIQUE NONCLUSTERED (guid,service,zone_id), CONSTRAINT x_policy_UK_service_signature UNIQUE NONCLUSTERED (service,resource_signature), ) GO diff --git a/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql index 16ad476..4ae146f 100644 --- a/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql +++ b/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql @@ -13,9 +13,9 @@ -- See the License for the specific language governing permissions and -- limitations under the License. BEGIN - IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname in('guid','service') THEN - IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_guid_service') THEN - ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_guid_service UNIQUE NONCLUSTERED (guid,service); + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname in('guid','service','zone_id') THEN + IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_guid_service_zone') THEN + ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_guid_service_zone UNIQUE NONCLUSTERED (guid,service,zone_id); END IF; END IF; END 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 8515ac0..0b24c59 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 @@ -1465,9 +1465,9 @@ CONSTRAINT [x_policy$x_policy_UK_name_service_zone] UNIQUE NONCLUSTERED ( [name] ASC, [service] ASC, [zone_id] ASC )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_guid_service] UNIQUE NONCLUSTERED +CONSTRAINT [x_policy$x_policy_UK_guid_service_zone] UNIQUE NONCLUSTERED ( - [guid] ASC, [service] ASC + [guid] ASC, [service] ASC, [zone_id] ASC )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_service_signature] UNIQUE NONCLUSTERED ( diff --git a/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql index 3037988..baa20dd 100644 --- a/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql +++ b/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql @@ -16,13 +16,13 @@ -- limitations under the License. -IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name in ('guid','service')) +IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name in ('guid','service','zone_id')) BEGIN - IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='guid' and constraint_name = 'x_policy$x_policy_UK_guid_service') + IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='guid' and constraint_name = 'x_policy$x_policy_UK_guid_service_zone') BEGIN - IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_guid_service' and CONSTRAINT_TYPE='UNIQUE') + IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_guid_service_zone' and CONSTRAINT_TYPE='UNIQUE') BEGIN - ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_guid_service] UNIQUE ([guid],[service]); + ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_guid_service_zone] UNIQUE ([guid],[service],[zone_id]); END END END