Re: Missing Grant on Package Body

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Thu, 11 Jul 2024 13:00:56 +0100
Message-ID: <CALe4Hp=uR-fXh7=2R7pwzR8X3Y2EqPOf1HCHV7T0xOUgBdJ0Qw_at_mail.gmail.com>

In practice you can always check DBA_DEPENDENCIES and run "GRANT ALL" for the referenced objects.
If you want to pinpoint the exact object(s) causing ORA-942, then you can use the errorstack event as Mladen mentioned. Level 2 would be enough to dump the necessary library cache structures: alter session set events '942 errorstack(2)';

For example, in my specific test case in 19.23 the error stack looks as follows:
qcsCheckPriv()+451 call kgesecl0() 7F7CD571B9C0 ? 7F7CD09C0050 ?

                                                   0000003AE ? 015CB8704 ?
                                                   015CB8710 ? 06EE39638
qcsbxl()+869         call     qcsCheckPriv()       7F7CD0B2CAD0 ?
7F7CD571B9C0 ?
                                                   0000003AE ? 077569110 ?
                                                   015CB8710 ? 06EE39638 ?
qcspqbDescendents()  call     qcsbxl()             7F7CD068D770 ? 000000011


?

+971                                               7F7CD068D770 ? 077569110


?

                                                   015CB8710 ? 06EE39638 ?
qcspqb()+280         call     qcspqbDescendents()  7FFEB0C31100 ? 000000002


?

                                                   7F7CD0B26800 ? 077569110


?

                                                   015CB8710 ? 7F7CD0B28E28
qcsdrv()+253         call     qcspqb()             7FFEB0C31100 ? 000000002


?

                                                   7F7CD0B26800 ? 077569110


?

                                                   015CB8710 ? 7F7CD0B28E28


?

qcitrans()+811       call     qcsdrv()             7F7CD0B24858 7F7CD571B9C0
                                                   7F7CD0B26800 ? 077569110


?

                                                   015CB8710 ? 7F7CD0B28E28

?

Where 0x77569110 is a library cache address of the table that is used in the package:
LibraryHandle: Address=0x77569110 Hash=2f28e9a2 LockMode=S PinMode=S LoadLockMode=0 Status=VALD

        ObjectName: Name=PDB.TC_TABLE.T

Based on this, I constructed the following bpftrace script: #!/usr/bin/env bpftrace
u:/u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle:qcsCheckPriv /pid/
{

        printf("%d: %s(param1=0x%lx, param2=0x%lx, param3=0x%lx, param4=0x%lx, param5=0x%lx, param6=0x%lx);\n", pid, func, arg0, arg1, arg2, arg3, arg4, arg5);

        $var5=*(int32 *)(arg3+0x18);
        $oid=*(int32 *)($var5+0x48);
        $nam=str($var5+0x58);
        printf("object_id=%d name=%s\n", $oid, $nam);

}
u:/u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle:qcuSigErr /pid/
{

        printf("%d: %s(param1=0x%lx, param2=0x%lx, param3=0x%lx, param4=0x%lx, param5=0x%lx, param6=0x%lx);\n", pid, func, arg0, arg1, arg2, arg3, arg4, arg5);
}

When it runs it produces the following output: ~oracle/qcsCheckPriv.bt -p 43251
WARNING: Cannot parse DWARF: libdw not available Attaching 2 probes...
43251: qcsCheckPriv(param1=0x7fc3e295ac20, param2=0x7fc3e76af9c0, param3=0x74b8afb0, param4=0x6e4f8750, param5=0x1, param6=0x7ffc7f39a0b8); object_id=73923 name=T_INSERTTC_TABLEPDB 43251: qcuSigErr(param1=0x7fc3e295aab8, param2=0x7fc3e76af9c0, param3=0x3ae, param4=0xfffffff5, param5=0x7fc3e2ac2bc0, param6=0x6e3e6668); 43251: qcsCheckPriv(param1=0x7fc3e2abe9e0, param2=0x7fc3e76af9c0, param3=0x74b8afb0, param4=0x6e4f48f0, param5=0x2, param6=0x0); object_id=73924 name=T_SELECTTC_TABLEPDB 43251: qcuSigErr(param1=0x7fc3e2ac3188, param2=0x7fc3e76af9c0, param3=0x3ae, param4=0xfffffff5, param5=0x7fc3e2aba800, param6=0x6e3e5f28); 43251: qcsCheckPriv(param1=0x7fc3e29554a8, param2=0x7fc3e76af9c0, param3=0x74b8afb0, param4=0x6e4f1d00, param5=0x3, param6=0x7ffc7f39a0b8); object_id=73925 name=T_UPDATETC_TABLEPDB 43251: qcuSigErr(param1=0x7fc3e2abad68, param2=0x7fc3e76af9c0, param3=0x3ae, param4=0xfffffff5, param5=0x7fc3e2955c68, param6=0x6e3e5b28);

If there is a qcuSigErr call after qcsCheckPriv, it means that the privilege is missing. You can just execute "GRANT ALL" on those objects. Your error stacks might be different but using the error stack dump approach you can still see the actual code path and should be able to identify the library cache objects being used. 23ai seems to have enhanced the reporting for ORA-942 errors but I don't have any 23ai at hand at the moment to confirm this.

Best regards,
*Mikhail Velikikh*

On Thu, 11 Jul 2024 at 02:32, Alan Sterger <asterger_at_earthlink.net> wrote:

> Hey List,
>
> Have a package body that does not compile:
> Errors for ...
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 10725/21 PL/SQL: SQL Statement ignored
> 10726/26 PL/SQL: ORA-00942: table or view does not exist
>
> Oh, and the package body is wrapped therefore, can't peek into the DDL.
> Is there a way to determine what/where the missing table or view is?
>
> -- Alan
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 11 2024 - 14:00:56 CEST