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.