Piyush
Piyush

Reputation: 21

Oracle VPD to introduce non-invasive dbms_utility.format_call_stack

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

Answers (0)

Related Questions