Query execution error when run through a procedure on a different user

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Query execution error when run through a procedure on a different user

Hello all,

 

User setup

Database setup.JPG

 

> I have this procedure on DEV_DB

pr_Query_Run_Test()

> Running it through ETLADMIN

> The procedure runs a COUNT query on DEV_DB.DW_Table and OLTP_STG.OLTP_Table and returns the counts

DEV_DB.DW_Table count runs fine but OLTP_STG.OLTP_Table fails with the SQLSTATE : 42J15 (Queries run from within the procedure)

> Although, if I run the same OLTP_STG.OLTP_Table COUNT query manually on ETLADMIN, it runs fine

 

Procedure

REPLACE PROCEDURE pr_Query_Run_Test()L1:BEGIN
    DECLARE v_Count_Query_Status VARCHAR(10);
    DECLARE v_Etl_Minus_Query_Run_Prms_Sid INTEGER DEFAULT 0;
    DECLARE v_Etl_Target_Table_Cid INTEGER DEFAULT 0;
    DECLARE v_Source_Count_Query VARCHAR(5000);
    DECLARE v_Target_Count_Query VARCHAR(5000);
    DECLARE v_Target_Count INTEGER;
    DECLARE v_Source_Count INTEGER;
    
    SET v_Source_Count_Query = 'SELECT COUNT(*) FROM ( SELECT 
( SrcTab.AGE_RANGE_CODE) AGE_RANGE_CODE,
( SrcTab.AGE_RANGE_DESC) AGE_RANGE_DESC,
( SrcTab.AGE_FROM_YEAR) AGE_FROM_YEAR,
( SrcTab.AGE_FROM_MNTH) AGE_FROM_MNTH,
( SrcTab.AGE_FROM_WEEK) AGE_FROM_WEEK,
( SrcTab.AGE_FROM_DAY) AGE_FROM_DAY,
( SrcTab.AGE_TO_YEAR) AGE_TO_YEAR,
( SrcTab.AGE_TO_MNTH) AGE_TO_MNTH,
( SrcTab.AGE_TO_WEEK) AGE_TO_WEEK,
( SrcTab.AGE_TO_DAY) AGE_TO_DAY,
TO_CHAR( SrcTab.AGE_RANGE_SID) AGE_RANGE_SID,
 SrcTab.OPRTNL_FLAG
FROM OLTP_STG.AGE_RANGE SrcTab
) X'; --211 records present    
    SET v_Target_Count_Query = 'SELECT COUNT(*) FROM ( SELECT 
 DwTab.AGE_RANGE_CODE,
 DwTab.AGE_RANGE_DESC,
 DwTab.AGE_FROM_YEAR,
 DwTab.AGE_FROM_MNTH,
 DwTab.AGE_FROM_WEEK,
 DwTab.AGE_FROM_DAY,
 DwTab.AGE_TO_YEAR,
 DwTab.AGE_TO_MNTH,
 DwTab.AGE_TO_WEEK,
 DwTab.AGE_TO_DAY,
 DwTab.SOURCE_SYSTEM_IDNTFR,
DECODE( DwTab.CURRENT_FLAG, ''Y'', ''A'', ''N'', ''A'', ''I'') OPRTNL_FLAG
FROM DEV_DB.REF_AGE_RANGE_H DwTab
) X'; --211 records present    
    SET v_Source_Count_Query = OREPLACE(v_Source_Count_Query, CHR(13), ' ') ;
    SET v_Target_Count_Query = OREPLACE(v_Target_Count_Query, CHR(13), ' ') ;
    CALL DEV_DB.WriteToLog('v_Source_Count_Query : ' || v_Source_Count_Query, v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
    CALL DEV_DB.WriteToLog('v_Target_Count_Query : ' || v_Target_Count_Query, v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
    
    --Source Count Query execution    L7:BEGIN
        DECLARE C7 CURSOR FOR S7;
        DECLARE EXIT HANDLER
            FOR SQLEXCEPTION
            BEGIN
                CALL DEV_DB.WriteToLog('Source COUNT Query exec failed with SQLSTATE : ' || SQLSTATE, v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
            END;
        CALL DEV_DB.WriteToLog('Entered Source Table COUNT loop L7', v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
        PREPARE S7 FROM v_Source_Count_Query;
        OPEN C7;
            FETCH C7 INTO v_Source_Count;
        CLOSE C7;    
    END L7;
    CALL DEV_DB.WriteToLog('v_Source_Count : ' || NVL(v_Source_Count, 999), v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
    
    --Target Count Query execution    L8:BEGIN
        DECLARE C8 CURSOR FOR S8;
        DECLARE EXIT HANDLER
            FOR SQLEXCEPTION
            BEGIN
                CALL DEV_DB.WriteToLog('Target COUNT Query exec failed with SQLSTATE : ' || SQLSTATE, v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
            END;
        CALL DEV_DB.WriteToLog('Entered Target Table COUNT loop L8', v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
        PREPARE S8 FROM v_Target_Count_Query;
        OPEN C8;
            FETCH C8 INTO v_Target_Count;
        CLOSE C8;    
    END L8;
    
    CALL DEV_DB.WriteToLog('v_Target_Count : ' || NVL(v_Target_Count, 999), v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
    CALL DEV_DB.WriteToLog('COUNT queries ran', v_Etl_Minus_Query_Run_Prms_Sid, v_Etl_Target_Table_Cid);
END L1;

 

Log of the Procedure

MSG_IDRUN_IDMSG_TIMESTAMPMSG
996308/17/2017 02:35:10.690000v_Source_Count_Query : SELECT COUNT(*) FROM ( SELECT  ( SrcTab.AGE_RANGE_CODE) AGE_RANGE_CODE, ( SrcTab.AGE_RANGE_DESC) AGE_RANGE_DESC, ( SrcTab.AGE_FROM_YEAR) AGE_FROM_YEAR, ( SrcTab.AGE_FROM_MNTH) AGE_FROM_MNTH, ( SrcTab.AGE_FROM_WEEK) AGE_FROM_WEEK, ( SrcTab.AGE_FROM_DAY) AGE_FROM_DAY, ( SrcTab.AGE_TO_YEAR) AGE_TO_YEAR, ( SrcTab.AGE_TO_MNTH) AGE_TO_MNTH, ( SrcTab.AGE_TO_WEEK) AGE_TO_WEEK, ( SrcTab.AGE_TO_DAY) AGE_TO_DAY, TO_CHAR( SrcTab.AGE_RANGE_SID) AGE_RANGE_SID,  SrcTab.OPRTNL_FLAG FROM OLTP_STG.AGE_RANGE SrcTab ) X
997308/17/2017 02:35:10.740000v_Target_Count_Query : SELECT COUNT(*) FROM ( SELECT   DwTab.AGE_RANGE_CODE,  DwTab.AGE_RANGE_DESC,  DwTab.AGE_FROM_YEAR,  DwTab.AGE_FROM_MNTH,  DwTab.AGE_FROM_WEEK,  DwTab.AGE_FROM_DAY,  DwTab.AGE_TO_YEAR,  DwTab.AGE_TO_MNTH,  DwTab.AGE_TO_WEEK,  DwTab.AGE_TO_DAY,  DwTab.SOURCE_SYSTEM_IDNTFR, DECODE( DwTab.CURRENT_FLAG, 'Y', 'A', 'N', 'A', 'I') OPRTNL_FLAG FROM DEV_DB.REF_AGE_RANGE_H DwTab ) X
998308/17/2017 02:35:10.790000Entered Source Table COUNT loop L7
999308/17/2017 02:35:10.850000Source COUNT Query exec failed with SQLSTATE : 42J15
1,000308/17/2017 02:35:10.910000v_Source_Count :         999  --Failing, count supposed to be 211
1,001308/17/2017 02:35:10.970000Entered Target Table COUNT loop L8
1,002308/17/2017 02:35:11.080000v_Target_Count :         211 --Running
1,003308/17/2017 02:35:11.130000COUNT queries ran

 

Do we need to give any additional permissions or am I missing something trivial??

 

Appreciate any help.

 

Thanks,

Pattabhi.

2 REPLIES
Junior Supporter

Re: Query execution error when run through a procedure on a different user

Hi Pattabhi,

 

As far as I understand "DEV_DB" must be given access on "OLTP_STG" because the query is executed inside the procedure which resides in "DEV_DB".

 

Thanks,

Rohan Sawant

Highlighted
Teradata Employee

Re: Query execution error when run through a procedure on a different user

Correct. You didn't include a SQL SECURITY clause in the SP definition, so it is defaulted to DEFINER. In that case both owner and definer must hold permissions on the objects in question at run time, as documented in the chart in the manual.

 

So either grant permissions to the OWNER (DEV_DB) as well, or change SQL SECURITY to CREATOR.