This is an automated email from the ASF dual-hosted git repository.
dineshkumar pushed a commit to branch ranger-2.6
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/ranger-2.6 by this push:
new 1a76c48dd RANGER-4949 : Creating security zone with any role selected
fails for oracle DB
1a76c48dd is described below
commit 1a76c48dd89c1d406215136467b3853d632b8bd9
Author: Dineshkumar Yadav <[email protected]>
AuthorDate: Thu Oct 3 17:02:21 2024 +0530
RANGER-4949 : Creating security zone with any role selected fails for
oracle DB
Signed-off-by: Dineshkumar Yadav <[email protected]>
---
.../optimized/current/ranger_core_db_oracle.sql | 2 ++
.../patches/066-create-sz-ref-role-table.sql | 26 ++++++++++++++++++++++
2 files changed, 28 insertions(+)
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 d9bd93c2f..14b1cdb9f 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
@@ -100,6 +100,7 @@ call spdropsequence('X_RANGER_GLOBAL_STATE_SEQ');
call spdropsequence('X_SECURITY_ZONE_SEQ');
call spdropsequence('X_POLICY_CHANGE_LOG_SEQ');
call spdropsequence('X_TAG_CHANGE_LOG_SEQ');
+call spdropsequence('X_SEC_ZONE_REF_ROLE_SEQ');
CREATE SEQUENCE SEQ_GEN_IDENTITY START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_ACCESS_AUDIT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
@@ -170,6 +171,7 @@ CREATE SEQUENCE X_SEC_ZONE_REF_USER_SEQ START WITH 1
INCREMENT BY 1 NOCACHE NOCY
CREATE SEQUENCE X_SEC_ZONE_REF_GROUP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
CREATE SEQUENCE X_POLICY_CHANGE_LOG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
CREATE SEQUENCE X_TAG_CHANGE_LOG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
+CREATE SEQUENCE X_SEC_ZONE_REF_ROLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
call spdropsequence('X_DB_VERSION_H_SEQ');
CREATE SEQUENCE X_DB_VERSION_H_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
diff --git a/security-admin/db/oracle/patches/066-create-sz-ref-role-table.sql
b/security-admin/db/oracle/patches/066-create-sz-ref-role-table.sql
index 990c8fd29..0e9d48736 100644
--- a/security-admin/db/oracle/patches/066-create-sz-ref-role-table.sql
+++ b/security-admin/db/oracle/patches/066-create-sz-ref-role-table.sql
@@ -13,7 +13,33 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name =
upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
+/
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name =
upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdropsequence('X_SEC_ZONE_REF_ROLE_SEQ');
+CREATE SEQUENCE X_SEC_ZONE_REF_ROLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
+
call spdroptable('x_security_zone_ref_role');
+commit;
CREATE TABLE x_security_zone_ref_role (
id NUMBER(20) NOT NULL,
