OIM – clean identity audit data i.e. all policy violations, remediation and scans data while retaining rules, policies and scan definitions

# clean identity audit data i.e. all policy violations, remediation and scans data while retaining rules, policies and scan definitions

# run the following sql on db :


--
-- drop all IDA data except for
--   scan definitions, config and event listener data
--
alter table IDA_SCAN_RUN_POLICIES disable constraint FK_ISRP_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_POLICIES disable constraint FK_ISRP_POLICY_ID;

alter table IDA_SCAN_RUN_USER disable constraint FK_ISRU_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_USER disable constraint FK_ISRU_USR_KEY;

alter table IDA_SCAN_RUN_POLICY_VIOLATION disable constraint FK_ISRPV_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_POLICY_VIOLATION disable constraint FK_ISRPV_POLICY_VIOLATION_ID;

alter table IDA_TASK_POLICY_VIOLATION disable constraint FK_ITPV_PV_ID;

alter table IDA_REMEDIATOR disable constraint FK_IR_POLICY_VIOLATION_ID;
alter table IDA_POLICY_VIOLATION disable constraint FK_IPV_POLICY_ID;
alter table IDA_POLICY_VIOLATION_CAUSE disable constraint FK_IPVC_POLICY_VIOLATION_ID;
alter table IDA_POLICY_VIOLATION_CAUSE disable constraint FK_IPVC_RULE_ID;


truncate table ida_scan_run_user;
truncate table ida_scan_run_policy_violation;
truncate table ida_scan_run;
truncate table ida_scan_run_policies;

truncate table IDA_TASK_POLICY_VIOLATION;

truncate table IDA_REMEDIATOR;
truncate table IDA_POLICY_VIOLATION_CAUSE;
truncate table IDA_POLICY_VIOLATION;

alter table IDA_POLICY_VIOLATION enable constraint FK_IPV_POLICY_ID;
alter table IDA_POLICY_VIOLATION_CAUSE enable constraint FK_IPVC_POLICY_VIOLATION_ID;
alter table IDA_POLICY_VIOLATION_CAUSE enable constraint FK_IPVC_RULE_ID;
alter table IDA_REMEDIATOR enable constraint FK_IR_POLICY_VIOLATION_ID;

alter table IDA_SCAN_RUN_POLICIES enable constraint FK_ISRP_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_POLICIES enable constraint FK_ISRP_POLICY_ID;

alter table IDA_SCAN_RUN_USER enable constraint FK_ISRU_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_USER enable constraint FK_ISRU_USR_KEY;

alter table IDA_SCAN_RUN_POLICY_VIOLATION enable constraint FK_ISRPV_SCAN_RUN_ID;
alter table IDA_SCAN_RUN_POLICY_VIOLATION enable constraint FK_ISRPV_POLICY_VIOLATION_ID;

alter table IDA_TASK_POLICY_VIOLATION enable constraint FK_ITPV_PV_ID;

Advertisements

OIM SQL DB list of resource names


SELECT obj_name,
  parentform,
  LISTAGG(childform, '|') WITHIN GROUP (
ORDER BY SDH_CHILD_KEY) AS childform
FROM
  (SELECT obj_name,
    sdk.sdk_name parentform,
    childsdk.sdk_name childform,
    SDH_CHILD_KEY
  FROM tos,
    obj,
    pkg ,
    sdk,
    sdh,
    sdk childsdk
  WHERE tos.pkg_key    = pkg.pkg_key
  AND obj.obj_key      = pkg.obj_key
  AND sdk.sdk_key      = tos.sdk_key
  AND sdk.sdk_key      = sdh.sdh_parent_key
  AND childsdk.sdk_key = sdh.SDH_CHILD_KEY
  )
GROUP BY obj_name,
  parentform;

Sqls – how to create table and insert data


--Query to create the table

CREATE TABLE Contractor
(USERID varchar2(100) primary key,
CONTRACTORID varchar2(100),
FIRSTNAME varchar2 (100),
LASTNAME varchar2 (100));
	
		 

--Query to insert data into the table
insert into contractor values('Leelah','LDrubld','Leelah','Drubld');
insert into contractor values('Crissy','CKosten','Crissy','Kosten');
insert into contractor values('Vijay','VClaveau','Vijay','Claveau');

--commit
commit;