Re: Missing Grant on Package Body

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Thu, 11 Jul 2024 13:55:28 +0100
Message-ID: <CALe4Hpk=BPHxs8c8Ca+-B=OfRqj-kh7FCW78OAAjM39sVPBzQA_at_mail.gmail.com>

>
> 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.
>

I verified that 23ai produces the actual missing object's name as I expected.
SQL> alter package tc_package compile body;

Warning: Package Body altered with compilation errors.

SQL> sho errors
Errors for PACKAGE BODY TC_PACKAGE:

LINE/COL ERROR

-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
6/26     PL/SQL: ORA-00942: table or view "TC_TABLE"."T_INSERT" does not
         exist

11/19 PL/SQL: SQL Statement ignored
11/42 PL/SQL: ORA-00942: table or view "TC_TABLE"."T_SELECT" does not

         exist

19/5 PL/SQL: SQL Statement ignored
19/21 PL/SQL: ORA-00942: table or view "TC_TABLE"."T_UPDATE" does not

         exist

LINE/COL ERROR

-------- -----------------------------------------------------------------

SQL> select banner_full from v$version;

BANNER_FULL

---

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

On Thu, 11 Jul 2024 at 13:00, Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> 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:55:28 CEST