Permission issue while executing Stored Procedures

Database

Permission issue while executing Stored Procedures

Hi,

 

I am new to teradata and trying to understand the working of Stored procedures and its permissions. Recently faced the below issue while executing a procedure.

  • We have a procedure named STAT_PROC which was created using username AM_01 under database BASE_DB.
  • We use to run this procedure daily using another user LD_01.
  • Botht the users LD_01 and AM_01 have same role(BS_ROLE) assigned to it.
  • Recently we decided to restrict the access of the AM_01(which created the procedure) and dropped the assigned role and created a new role and assigned to it.
  • Once this was done, when we try to execute the procedure STAT_PROC uing the LD_01 username we are getting the error saying "User does not have insert access to table XXXX". The table is also present in the same DB where the procedure recides and the both the users have R, D, U and I access on the DB.
  • But we are not sure why the procedure failed to execute after the role of the user created is modified.

Note: The new role also has all access as earlier except Read access on a different DB which is not linked with this SP mentioned above.

 

Could anyone help me understand why was this behaving like this? Also the procedure is runing fine after it was recreated by the LD_01 user.

 

Thanks in Advance!!!


Accepted Solutions
Highlighted
Teradata Employee

Re: Permission issue while executing Stored Procedures

By default, the security privileges of a procedure are those of the definer of the procedure.  If the definer's rights have changed, this may have been revoked.  If AM_01 really does still have all the privileges needed by the SP, then if they Replace the procedure, the rights should be re-registered.  Otherwise, add the clause "SQL SECURITY INVOKER" to the Replace statement, and then only the privileges of the User (e.g. LD_01) will be checked, and the privileges of AM_01 won't matter as long as they have create-procedure rights.

1 ACCEPTED SOLUTION
3 REPLIES
Visitor

Re: Permission issue while executing Stored Procedures

Can you mention separately each user permission on procedure and DB , I can figured it out for you. You must be talking about EDW

Highlighted
Teradata Employee

Re: Permission issue while executing Stored Procedures

By default, the security privileges of a procedure are those of the definer of the procedure.  If the definer's rights have changed, this may have been revoked.  If AM_01 really does still have all the privileges needed by the SP, then if they Replace the procedure, the rights should be re-registered.  Otherwise, add the clause "SQL SECURITY INVOKER" to the Replace statement, and then only the privileges of the User (e.g. LD_01) will be checked, and the privileges of AM_01 won't matter as long as they have create-procedure rights.

Re: Permission issue while executing Stored Procedures

Thanks Coleman!!!

 

This was very helpful and it explains why we faced the error.

Tags (1)