Hi all, I am experiencing some odd behaviour with Procedures Permissions I have been unable to solve. I am new to Teradata and, coming from a SQL Server background, think procs should be the prefered way to access the database.
So I create a table, create a Proc to update the table, call and it runs fine. I leave for a week, call the same proc and this time I get an error about not having Execute permission, if I drop and re-create the proc, the proc will now run.
Another senerio, I have a table I created a few weeks ago, I create a new proc to update this table, and get a message
":An owner referenced by user does not have UPDATE access to tablename...."
when running the Create procedure. If I recreate the table, there are now no error messages creating the proc and it runs as expected.
It seems that if Prcedures and objects are created in a single session, I dont have problems. I can alos run all my code at anytime as adhoc queries without problems.
Hoefully I am just missing something simple here, the loacl admins/DBAs do not use procedures and have been unable to help.
We have a Teradata 13 envioment.
Anyhelp will be greatly appreciated.
The procedure database needs to have the update access on table db. You can grant the access through this sql.
GRANT UPDATE ON <<TABLE_DB>> TO <<PROCEDURE_DB>> WITH GRANT OPTION;
This should solve your update issue...
Thanks QK, I will try that when I get back to work. Just to be clear, the proc and table are in the same DB.
But it still leaves me confused as to why it all works when the table and proc are created and executed in the same session, but seem to need new set of Grants issued if run a few days latter, any ideas?
A user can create a stored procedure in its own space or in some other DB, so the owner of the stored procedure could be different from the creator of the stored procedure.
So whenever the stored procedure is created/executed, teradata checks the privelages of the immediate owner of the stored procedure for the objects referenced in the SP.
Thats why its important to look for the required privelages whenever a user trys to execute the stored procedure which is not the immediate owner of the SP. And those privelages should be 'WITH GRANT OPTION'...
This was some backgroud about stored procedure, but you need to consider these things as well,
- Whether the user who created the table and SP was creating in its own space or in some other database space.
- Whether the user who is executing the SP has the UPDATE access on the table?
I have created a procedure in different database than the user used to create it,
The user I used to create the procedure is like 'vbhas' and the database it is created is like 'IFSRD', when I login as 'vbhas' and try to execute the proc it gives the error, could you please let me know how to modify the execute access so that I will be able to execute this procedure successfully?