How to Identify which privileges are required by capturing real time access rights in Teradata

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.

Comments