Update not working permission issues

Database

Update not working permission issues

Hello, 

I am able to update the following successfully 

SyntaxEditor Code Snippet

update DL_2015_PMD_POC.test_srvy 
from DDWV21.srvy sr
set call_tm = sr.call_tm 
where DL_2015_PMD_POC.test_srvy.srvy_id = sr.srvy_id

DL_2015_PMD_POC.test_srvy is my dev table. 

I then tried to do the same on my prod table :DL_2015_PMD_POC.srvy

and i get permission errors

SyntaxEditor Code Snippet

update DL_2015_PMD_POC.srvy 
from DDWV21.srvy sr
set call_tm = sr.call_tm 
where DL_2015_PMD_POC.srvy.srvy_id = sr.srvy_id
and DL_2015_PMD_POC.srvy.lst_updt_dt_tm > '2018/12/06 21:30:17.000000'

  UPDATE Failed. 5315:  An owner referenced by user does not have UPDATE WITH GRANT OPTION access to DDWTA.SRVY.CALL_TM.  

I am not sure why it is looking for the table under DDWTA in the second query but not the first. Thanks in advance.

5 REPLIES
Teradata Employee

Re: Update not working permission issues

DL_2015_PMD_POC.srvy would seem to be a view that points to table/view DDWTA.SRVY.CALL_TM.

In order to to do an UPDATE via the view,

The user issuing the UPDATE must have UPDATE access to the view, AND

The database containing the view (DL_2015_PMD_POC) must have UPDATE WITH GRANT OPTION access to DDWTA.SRVY (or to entire database DDWTA.All).

Re: Update not working permission issues

Thanks Fred for replying. The TABLE is not based on DDWTA.Srvy for sure. The view DDWV21.SRVY is based on DDWTA.SRVY. The table dl_2015_PMD_POC.srvy was created as select * from DDWV21.SRVY...but I believe that does not create dependancy on DDWTA IN turn. Pl advice.
Teradata Employee

Re: Update not working permission issues

OK, then the dependency is indirect:

The user must have UPDATE on view DL_2015_PMD_POC.srvy

DL_2015_PMD_POC must have UPDATE WITH GRANT OPTION on view DDWV21.SRVY (or database DDWV21)

DDWV21 must have UPDATE WITH GRANT OPTION on table DDWTA.Srvy (or database DDWTA)

The last one is what the message is complaining about. I suspect DDWV21 has SELECT WITH GRANT but not UPDATE WITH GRANT.

 

 

 

Re: Update not working permission issues

Appreciate it Fred. 

DDWTA.Srvy is managed by the DBA's and I do not have any previlidges to that table. It is used in a way like a staging view/table by them to populate our production view DDWV21.SRVY everyday after ETL run. Is there any way around this that I could use without having to engage the DBA's. Thanks.

Highlighted
Teradata Employee

Re: Update not working permission issues

No. Someone who has admin (GRANT) access to table DDWTA.Srvy will have to give permission to update the table (whether directly, through DDWV21.SRVY, or through some alternate view).