This is an automated email from the ASF dual-hosted git repository. rmani pushed a commit to branch RANGER-5287_master in repository https://gitbox.apache.org/repos/asf/ranger.git
commit e9fb4b6cc0c8d1876f8b877fb3b425365ec8120b Author: Ramesh Mani <[email protected]> AuthorDate: Fri Aug 15 10:59:12 2025 -0700 RANGER-5287:Add sort by added user Id and action for the admin audit log Signed-off-by: Ramesh Mani <[email protected]> --- .../optimized/current/ranger_core_db_mysql.sql | 1 + .../patches/076-create-index-for-x_trx_log_v2.sql | 28 ++++++++++++++++ .../optimized/current/ranger_core_db_oracle.sql | 1 + .../patches/075-create-index-for-x_trx_log_v2.sql | 29 +++++++++++++++++ .../optimized/current/ranger_core_db_postgres.sql | 1 + .../patches/076-create-index-for-x_trx_log_v2.sql | 37 ++++++++++++++++++++++ .../current/ranger_core_db_sqlanywhere.sql | 2 ++ .../patches/075-create-index-for-x_trx_log_v2.sql | 23 ++++++++++++++ .../optimized/current/ranger_core_db_sqlserver.sql | 6 +++- .../patches/075-create-index-for-x_trx_log_v2.sql | 29 +++++++++++++++++ .../ranger/service/RangerTrxLogV2Service.java | 2 ++ 11 files changed, 158 insertions(+), 1 deletion(-) 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 5fb0d95db..4b5572432 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 @@ -455,6 +455,7 @@ CREATE TABLE `x_trx_log_v2` ( PRIMARY KEY (`id`), KEY `x_trx_log_v2_FK_added_by_id` (`added_by_id`), KEY `x_trx_log_v2_cr_time` (`create_time`), + KEY `x_trx_log_v2_action` (`action`), KEY `x_trx_log_v2_trx_id` (`trx_id`) )ROW_FORMAT=DYNAMIC; diff --git a/security-admin/db/mysql/patches/076-create-index-for-x_trx_log_v2.sql b/security-admin/db/mysql/patches/076-create-index-for-x_trx_log_v2.sql new file mode 100644 index 000000000..bc43aa4a9 --- /dev/null +++ b/security-admin/db/mysql/patches/076-create-index-for-x_trx_log_v2.sql @@ -0,0 +1,28 @@ +-- 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_index_for_x_trx_log_v2; + +delimiter ;; +create procedure create_index_for_x_trx_log_v2() begin +if exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_trx_log_v2') then + if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_trx_log_v2' AND index_name='x_trx_log_v2_action') then + CREATE INDEX x_trx_log_v2_action ON x_trx_log_v2(action); + end if; +end if; +end;; + +delimiter ; +call create_index_for_x_trx_log_v2(); 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 e1217f0c4..8be8d8fd4 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 @@ -1760,6 +1760,7 @@ CREATE INDEX x_resource_FK_parent_id ON x_resource (parent_id); CREATE INDEX x_resource_cr_time ON x_resource(create_time); CREATE INDEX x_resource_up_time ON x_resource (update_time); CREATE INDEX x_trx_log_v2_FK_added_by_id ON x_trx_log_v2 (added_by_id); +CREATE INDEX x_trx_log_v2_action ON x_trx_log_v2 (action); CREATE INDEX x_trx_log_v2_cr_time ON x_trx_log_v2 (create_time); CREATE INDEX x_trx_log_v2_trx_id ON x_trx_log_v2 (trx_id); CREATE INDEX x_user_FK_added_by_id ON x_user (added_by_id); diff --git a/security-admin/db/oracle/patches/075-create-index-for-x_trx_log_v2.sql b/security-admin/db/oracle/patches/075-create-index-for-x_trx_log_v2.sql new file mode 100644 index 000000000..a3c031aed --- /dev/null +++ b/security-admin/db/oracle/patches/075-create-index-for-x_trx_log_v2.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. +-- sync_source_info CLOB NOT NULL, + +DECLARE + v_index_exists number:=0; + v_table_exists number := 0; +BEGIN + SELECT COUNT(*) INTO v_table_exists FROM USER_TABLES WHERE TABLE_NAME = upper('x_trx_log_v2'); + IF (v_table_exists > 0) THEN + SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_trx_log_v2_action') AND TABLE_NAME= upper('x_trx_log_v2'); + IF (v_index_exists = 0) THEN + execute IMMEDIATE 'CREATE INDEX x_trx_log_v2_action ON x_trx_log_v2(action)'; + commit; + END IF; + END IF; +END;/ 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 16263a2d7..56652c936 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 @@ -1980,6 +1980,7 @@ CREATE INDEX x_resource_FK_parent_id ON x_resource(parent_id); CREATE INDEX x_resource_cr_time ON x_resource(create_time); CREATE INDEX x_resource_up_time ON x_resource(update_time); CREATE INDEX x_trx_log_v2_FK_added_by_id ON x_trx_log_v2(added_by_id); +CREATE INDEX x_trx_log_v2_action ON x_trx_log_v2(action); CREATE INDEX x_trx_log_v2_cr_time ON x_trx_log_v2(create_time); CREATE INDEX x_trx_log_v2_trx_id ON x_trx_log_v2(trx_id); CREATE INDEX x_user_FK_added_by_id ON x_user(added_by_id); diff --git a/security-admin/db/postgres/patches/076-create-index-for-x_trx_log_v2.sql b/security-admin/db/postgres/patches/076-create-index-for-x_trx_log_v2.sql new file mode 100644 index 000000000..a5b37acae --- /dev/null +++ b/security-admin/db/postgres/patches/076-create-index-for-x_trx_log_v2.sql @@ -0,0 +1,37 @@ +-- 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_index_for_x_trx_log_v2() +RETURNS void AS $$ +DECLARE + v_attnum1 integer := 0; + v_table_exists integer := 0; +BEGIN + SELECT COUNT(*) INTO v_table_exists FROM pg_class WHERE relname = 'x_trx_log_v2'; + IF v_table_exists > 0 THEN + select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_trx_log_v2') and attname in('action'); + IF v_attnum1 > 0 THEN + IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_trx_log_v2') and indkey[0]=v_attnum1) THEN + CREATE INDEX x_trx_log_v2_action ON x_trx_log_v2(action); + END IF; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +select create_index_for_x_trx_log_v2(); +select 'delimiter end'; 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 fa9832314..f6988b402 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 @@ -1900,6 +1900,8 @@ CREATE NONCLUSTERED INDEX x_resource_up_time ON dbo.x_resource(update_time ASC) GO CREATE NONCLUSTERED INDEX x_trx_log_v2_FK_cr_time ON dbo.x_trx_log_v2(create_time ASC) GO +CREATE NONCLUSTERED INDEX x_trx_log_v2_action ON dbo.x_trx_log_v2(action ASC) +GO CREATE NONCLUSTERED INDEX x_trx_log_v2_FK_added_by_id ON dbo.x_trx_log_v2(added_by_id ASC) GO CREATE NONCLUSTERED INDEX x_trx_log_v2_FK_trx_id ON dbo.x_trx_log_v2(trx_id ASC) diff --git a/security-admin/db/sqlanywhere/patches/075-create-index-for-x_trx_log_v2.sql b/security-admin/db/sqlanywhere/patches/075-create-index-for-x_trx_log_v2.sql new file mode 100644 index 000000000..8d6d54f63 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/075-create-index-for-x_trx_log_v2.sql @@ -0,0 +1,23 @@ +-- 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 SYS.SYSTABLES where tname = 'x_trx_log_v2') THEN + IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_trx_log_v2' and cname = 'action') THEN + CREATE INDEX IF NOT EXISTS x_trx_log_v2_action ON x_trx_log_v2(action); + END IF; +END IF; +GO + +EXIT \ No newline at end of file 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 a0e47004b..80de4e0cf 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 @@ -3445,7 +3445,11 @@ CREATE NONCLUSTERED INDEX [x_resource_up_time] ON [x_resource] [update_time] ASC ) WITH (SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,IGNORE_DUP_KEY = OFF,ONLINE = OFF) ON [PRIMARY] - +CREATE NONCLUSTERED INDEX [x_trx_log_v2_action] ON [x_trx_log_v2] +( + [action] ASC +) +WITH (SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,IGNORE_DUP_KEY = OFF,ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [x_trx_log_v2_cr_time] ON [x_trx_log_v2] ( [create_time] ASC diff --git a/security-admin/db/sqlserver/patches/075-create-index-for-x_trx_log_v2.sql b/security-admin/db/sqlserver/patches/075-create-index-for-x_trx_log_v2.sql new file mode 100644 index 000000000..dedff8d50 --- /dev/null +++ b/security-admin/db/sqlserver/patches/075-create-index-for-x_trx_log_v2.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. +GO +IF OBJECT_ID('x_trx_log_v2') IS NOT NULL +BEGIN + IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_trx_log_v2_action' AND object_id = OBJECT_ID('x_trx_log_v2')) + BEGIN + CREATE NONCLUSTERED INDEX [x_trx_log_v2_action] ON [x_trx_log_v2] + ( + [action] ASC + ) + WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] + END +END +Go + +EXIT; diff --git a/security-admin/src/main/java/org/apache/ranger/service/RangerTrxLogV2Service.java b/security-admin/src/main/java/org/apache/ranger/service/RangerTrxLogV2Service.java index 46866065f..4021e61c8 100644 --- a/security-admin/src/main/java/org/apache/ranger/service/RangerTrxLogV2Service.java +++ b/security-admin/src/main/java/org/apache/ranger/service/RangerTrxLogV2Service.java @@ -87,6 +87,8 @@ public RangerTrxLogV2Service() { sortFields.add(new SortField("id", "obj.id", true, SortField.SORT_ORDER.DESC)); sortFields.add(new SortField("createDate", "obj.createTime", true, SortField.SORT_ORDER.DESC)); + sortFields.add(new SortField("addedByUserId", "obj.addedByUserId", true, SortField.SORT_ORDER.DESC)); + sortFields.add(new SortField("action", "obj.action", true, SortField.SORT_ORDER.DESC)); } public List<SearchField> getSearchFields() {
