Reputation: 21
We have a situation whereby the code generates an intermetent 'Insert' on a table. The idea is to enable VPD (via DBMS_RLS.ADD_POLICY) methods, on 'insert' of the table, and policy function, which is going to generate Call stack to trace which piece of the code caused the insert to take place.
Unfortunately, what i get from the policy's function is -- an anonymous call to the policy function. (a) there a way to improve the policy function, to get the full nested list of functions which ulimatately triggered the insert, Or (b) if there a way without updating the code-base to keep on introducing debug messages, with callstack generation, to track whenever the code-base inserts any row to the <<original-table>>
Considering there exists several place where the (base-original-code) inserts data to the original-table in question.
----- PL/SQL Call Stack ----- object line object handle number name 0x164e02ee8 1 anonymous block 0x24ad087e8 37 function <<the policy function name>> 0x1f734c490 1 anonymous block
Sample - Code Policy enablement - >
DBMS_RLS.ADD_POLICY (
object_schema => <<**x_schema**>>,
object_name => <<<**original-table**>>>,
policy_name => l_policy_name,
function_schema => <<**x_schema**>>,
policy_function => <<ING_PIM_DOC_CAT_PLY_FN>>,
statement_types => 'INSERT',
update_check => True,
enable => l_enable,
policy_type => DBMS_RLS.DYNAMIC
);
-
-*/
--- Create a temp. table to capture the callstack and env. info.
Create table t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ns varchar2(255),
atr varchar2 (255),
val varchar2(4000),
os_user varchar2(255),
sessionid number,
caller varchar2(255),
callgrp number,
callstack varchar2(4000)
)
Policy Function --
CREATE OR REPLACE FUNCTION ING_PIM_DOC_CAT_PLY_FN (
object_schema IN VARCHAR2,
object_name VARCHAR2)
RETURN VARCHAR2
IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
--DECLARE
l_iSize INTEGER;
l_tblAppCtx DBMS_SESSION.APPCTXTABTYP;
--
l_ns VARCHAR2 (255);
l_atr VARCHAR2 (255);
l_atrVal VARCHAR2 (4000);
l_owner VARCHAR2 (255);
l_calCntr NUMBER;
--
l_sys_os_user VARCHAR2 (255) := 'piyush.ohri';
l_bUserSpecific BOOLEAN := TRUE;
l_insertRec BOOLEAN := TRUE;
BEGIN
DBMS_SESSION.LIST_CONTEXT (l_tblAppCtx, l_iSize);
l_owner := object_schema || '.' || object_name;
SELECT xxint_text_plcy_seq.NEXTVAL INTO l_calCntr FROM DUAL;
FOR idx IN NVL (l_tblAppCtx.FIRST, 0) .. NVL (l_tblAppCtx.LAST, -1)
--
LOOP
DBMS_OUTPUT.PUT_LINE (
l_tblAppCtx (idx).namespace
|| ': '
|| l_tblAppCtx (idx).attribute
|| ' - '
|| l_tblAppCtx (idx).VALUE);
l_ns := l_tblAppCtx (idx).namespace || '(' || TO_CHAR (l_iSize) || ')';
l_atr := l_tblAppCtx (idx).attribute;
l_atrVal := SUBSTR (l_tblAppCtx (idx).VALUE, 1, 255);
IF ( ( (UPPER (SYS_CONTEXT ('USERENV', 'OS_USER')) =
UPPER (l_sys_os_user))
OR (UPPER (SYS_CONTEXT ('FND', 'USER_NAME')) =
UPPER (l_sys_os_user)))
AND l_bUserSpecific
AND l_atr LIKE '%%')
THEN
--DBMS_OUTPUT.PUT_LINE (l_tblAppCtx(idx).namespace || ': ' || l_tblAppCtx(idx).attribute || ' - ' || l_tblAppCtx(idx).value);
--/*
INSERT INTO t1 (ns,
atr,
val,
os_user,
sessionid,
caller,
callgrp,
callstack)
VALUES (l_ns,
l_atr,
l_atrVal,
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'SESSIONID'),
l_owner,
l_calCntr,
DBMS_UTILITY.format_call_stack);
--*/
l_insertRec := TRUE;
END IF;
END LOOP;
--/*
INSERT ALL
INTO t1 (ns,
atr,
val,
os_user,
sessionid,
caller,
callgrp,
callstack)
VALUES (l_ns,
'user',
USER,
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'SESSIONID'),
l_owner,
l_calCntr,
DBMS_UTILITY.format_call_stack)
INTO t1 (ns,
atr,
val,
os_user,
sessionid,
caller,
callgrp,
callstack)
VALUES (l_ns,
'USERENV:SESSION_USER',
SYS_CONTEXT ('USERENV', 'SESSION_USER'),
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'SESSIONID'),
l_owner,
l_calCntr,
DBMS_UTILITY.format_call_stack)
SELECT 1 FROM DUAL;
--*/
IF (l_insertRec)
THEN
COMMIT;
END IF;
RETURN NULL;
END;
Trying to find - a method of generating call-stack when an insert of a record takes place (*original-table*), without changing the core-base to introduce debug messages explicitly.
Oracle DB version 19.18.0.0.0
Upvotes: 1
Views: 55