Skip to main content

Search

Welcome to Tibero GTS!

Table Query Failure Issue (With Query Permission)

Document Type | Troubleshooting

Category | App Development

Applicable Product Versions | 6FS07, 7FS02

Document Number | TDETS001

 

Issue

This document explains the issue where table query fails despite having table query permissions.

Verification of Table Query Failure

Granted GRANT SELECT, INSERT, UPDATE, DELETE ON RES.RRMS001 TO MOB_APU; but query fails
The MOB_APU account cannot execute SELECT * FROM RES.RRMS001; query.
 
1. Among the indexes of RES.RRMS001 table, IDX_IF4_RRMS001 is configured as FBI (FUNCTION-BASED INDEX).
CREATE INDEX "IDX_IF4_RRMS001" ON "RRMS001" (
           "COM"."FN_CSYS_DECODE"("RRNO",'RRNO')
   )
   LOGGING
   TABLESPACE "TS_RES"
   NOPARALLEL
   PCTFREE 10
   INITRANS 2
   /

 

2. Checked the usage permission for COM.FN_CSYS_DECODE - MOB_APU account lacks EXECUTE permission for this FUNCTION. 

SQL> select grantee, owner, table_name, privilege 
     from dba_tab_privs 
     where table_name = 'FN_CSYS_DECODE' 
     ; 
     
GRANTEE      OWNER      TABLE_NAME       PRIVILEGE
----------   ---------  ---------------  -------------------------
ILK          COM        FN_CSYS_DECODE   EXECUTE
ILK_KEP      COM        FN_CSYS_DECODE   EXECUTE
ELK_RIPS     COM        FN_CSYS_DECODE   EXECUTE
ELK_EFNNC    COM        FN_CSYS_DECODE   EXECUTE
ELK_WRCARD   COM        FN_CSYS_DECODE   EXECUTE
RESNOTE_SOL  COM        FN_CSYS_DECODE   EXECUTE
ADM          COM        FN_CSYS_DECODE   EXECUTE
RES          COM        FN_CSYS_DECODE   EXECUTE
MIG          COM        FN_CSYS_DECODE   EXECUTE
ILK_RSERCH   COM        FN_CSYS_DECODE   EXECUTE
ILK_CCRF     COM        FN_CSYS_DECODE   EXECUTE

 

3. Checking the call stack for the statement SELECT * FROM RES.RRMS001; as MOB_APU user shows an issue at sql_hardparsing.

Parsing failed because there exists an OBJECT inaccessible to the MOB_APU user during permission check.

#19 0x00000000005ddaf2 in sql_hardparsing (alloc=0x7fc8080111d0, sql=0x7fc81e11a3d8 "select * from RES.RRMS001 where rownum <=5", pp_sql=0x7fc81e11a3d8 "select * from RES.RRMS001 where rownum <=5", psm_params=0x7fc81b4e92c0, csr=0x7fcd3d10b2c0, ecs_ctx=0x7fc81b4e8d20, dd_lock_acquired=0x7fc81b4e8fdb "\001", rc=0x7fc81b4e9008, pp=0x7fc81b4e9128) at /data/autodist/TLeft_261783/tibero6/src/tbsvr/body/tbsvr_sql_process.c:1728

#20 0x00000000005e1441 in tbsvr_sql_process (alloc=0x7fc8080111d0, sql=0x7fc81e11a3d8 "select * from RES.RRMS001 where rownum <=5", ppid=0, sql_state=0x7fc81b4e9120, prepared_ddl=0x7fc81b4e9127 "", csr=0x7fc81b4e9300, pp=0x7fc81b4e9128, psm_params=0x7fc81b4e92c0, only_csr_generate=0 '\000') at /data/autodist/TLeft_261783/tibero6/src/tbsvr/body/tbsvr_sql_process.c:338

 

Cause

Parsing fails because there is an object inaccessible during parsing, causing the select statement not to execute.
 
 

Solutions

Parsing succeeds by granting the execution permission.
Grant permission for MOB_APU to execute COM.FN_CSYS_DECODE

SQL> GRANT EXECUTE ON COM.FN_CSYS_DECODE TO MOB_APU;

 

 

 

Related to