Problem: User running a job, but the job failed for privileges issue and DBA/Developer would like to identify which privileges are required to grant the user to run the job.
Solution: “diagnostic accessr” can be use in the session level to identify the missing privileges. The following way missing privileges can be identified by capturing real-time access right while the job is running: 1. Login to the database by using the userid where the job failing for privileges issue 2. Open “cnsterm 5” 3. Execute “diagnostic accessr on for session;” in the window in Step1 4. Execute the job or query in the window in Step1 5. Check the log in the Window in Step2
Example: # bteq Teradata BTEQ 16.20.00.04 for LINUX. PID: 20226 Copyright 1984-2018, Teradata Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command: .logon USER1 .logon USER1 Password: *** Logon successfully completed. *** Teradata Database Release is 16.20.24.01 *** Teradata Database Version is 16.20.24.01 *** Transaction Semantics are ANSI. *** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: diagnostic accessr on for session;
diagnostic accessr on for session; *** Null statement accepted. *** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command: call DLDB.TMP_TEST('2018-01-20');
call DLDB.TMP_TEST('2018-01-20'); *** Error 3523 TMP_TEST:The user does not have DELETE access to DLDB.TMP_SI_UPGRADE_SALES_A. *** Total elapsed time was 1 second.
# cnsterm 5 GncApply: PSteps = 62680 DSteps(1) = 1952 PE 30709 Parser done. CPU = 0.004 ClockTime = 0 Secs. 19/03/18 11:02:07. --- Accessrights: ** Request Text **: call DLDB.TMP_TEST('2018-01-20 ');
User 0:0 Needs ExecuteProcedure on procedure TMP_TEST in DLDB. --- GncApply: PSteps = 101960 DSteps(1) = 15776 PE 30709 Parser done. CPU = 0.008 ClockTime = 0 Secs. 19/03/18 11:02:29. --- Accessrights: ** Request Text **: USING _spVV1 (INTEGER), _spVV0 (DATE)SELECT (SELECT c alendar_week_id FROM SEMVOUT.C ALENDAR_DAY_D WHERE calendar_Dt = :_spVV0 ) INTO :_spVV1 ;
User 0:2548 Needs SELECT With Grant Option on column Calendar_Dt in table/view CALENDAR_DATE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Calendar_Week_Id in table/view CALENDAR_DATE in TargetDBT. User 0:2573 Needs SELECT With Grant Option on column Calendar_Dt in table/view CALENDAR_DATE in TargetDBVOUT. User 0:2573 Needs SELECT With Grant Option on column Calendar_Week_Id in table/view CALENDAR_DATE in TargetDBVOUT. User 0:4856 Needs SELECT on column Calendar_Dt in table/view CALENDAR_DAY_D in SEMVOUT. User 0:4856 Needs SELECT on column Calendar_Week_Id in table/view CALENDAR_DAY_D in SEMVOUT. --- GncApply: PSteps = 260536 DSteps(1) = 69264 PE 30709 Parser done. CPU = 0.076 ClockTime = 0 Secs. 19/03/18 11:02:29. --- Accessrights: ** Request Text **: USING _spVV1 (INTEGER)DELETE PRDLAD SBPT.TMP_SI_UPGRADE_SALES_A FROM P RDLADSBPT.TMP_SI_UPGRADE_SALES_A WHERE Upgrade_Offer_Seq_Num IN (S ELECT Upgrade_Offer_Seq_Num FROM SEMVOUT.UPGRADE_OFFER_D a INNER JOIN SEMVOUT.CALENDAR_DA Y_D cal ON a.Upgrade_offer_start _dt = cal.calendar_Dt WHERE cal.calendar_week_id = :_spV V1 );
User 0:2548 Needs SELECT With Grant Option on column Item_Seq_Num in table/view ITEM in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column N_Item_Id in table/view ITEM in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Calendar_Dt in table/view CALENDAR_DATE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Calendar_Week_Id in table/view CALENDAR_DATE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Upgrade_Offer_Seq_Num in table/view UPGRADE_OFFER in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Upgrade_Offer_Seq_Num in table/view CAMPAIGN_UPGRADE_OFFER in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Valid_To_Dttm in table/view CAMPAIGN_UPGRADE_OFFER in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Upgrade_Offer_Seq_Num in table/view UPGRADE_OFFER_NAME_B in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Valid_To_Dttm in table/view UPGRADE_OFFER_NAME_B in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Upgrade_Offer_Seq_Num in table/view UPGRADE_OFFER_PREREQUISITE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Item_Seq_Num in table/view UPGRADE_OFFER_PREREQUISITE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Minimum_Spend_Amt in table/view UPGRADE_OFFER_PREREQUISITE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Valid_To_Dttm in table/view UPGRADE_OFFER_PREREQUISITE in TargetDBT. User 0:2548 Needs SELECT With Grant Option on column Upgrade_Offer_Seq_Num in table/view UPGRADE_OFFER_STORE in TargetDBT. …………………………………. …………………………………. …………………………………. User 0:4856 Needs SELECT on column Calendar_Dt in table/view CALENDAR_DAY_D in SEMVOUT. User 0:4856 Needs SELECT on column Calendar_Week_Id in table/view CALENDAR_DAY_D in SEMVOUT. User 0:4856 Needs SELECT on column Upgrade_Offer_Seq_Num in table/view UPGRADE_OFFER_D in SEMVOUT. User 0:4856 Needs SELECT on column Upgrade_offer_start_dt in table/view UPGRADE_OFFER_D in SEMVOUT. User 0:4856 Needs DELETE on table TMP_SI_UPGRADE_SALES_A in DLDB. User 0:4856 Needs DELETE on table TMP_SI_UPGRADE_SALES_A in DLDB. User 0:4856 Needs SELECT on column Upgrade_Offer_Seq_Num in table/view TMP_SI_UPGRADE_SALES_A in DLDB. --- PE 30709 Parser done. CPU = 0.220 ClockTime = 0 Secs. 19/03/18 11:02:30. |