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;