All Forums Database
suhailmemon84 47 posts Joined 09/10
06 Oct 2014

Can someone elaborate on the SQL SECURITY INVOKER option in stored procedures? I need to create a stored procedure and ensure the privilege(i.e. the privilege on the underlying database objects inside the procedure) of the user calling the procedure is always checked and I think the SQL SECURITY INVOKER option achieves that. 


As a test, I created one user: testuser and one database: testprocdb. Initially, I ensured these 2 had absolutely 0 access rights. Then I granted create procedure, drop procedure and execute procedure rights on testprocdb to the user: testuser

Then I logged in as testuser and created a static procedure sp1 that looks like this:

REPLACE PROCEDURE testprocdb.sp1() 



delete from db1.tbl1; 

insert into db1.tbl1 select * from  db2.tbl2;



Teradata successfully created this procedure and gave no error. However, the call to this procedure by testuser failed with access error which was expected. 


The successful procedure creation came as a bit of surprise to me because from the documentation:


the privilege of the creator(in this case it is testuser which has absolutely no access rights over db1.tbl1 or db2.tbl2. it only has CP, PE and DP rights on sp1) is checked during compilation of the procedure when the sql security option is INVOKER and the sqls inside the procedure are STATIC SQL.


Reference Link: index.html#page/SQL_Reference/B035_1144_111A/ Create_Procedure-Syntax.023.21.html 


According to the documentation, the above replace procedure should have failed too. 


Am I missing something here?




suhailmemon84 47 posts Joined 09/10
06 Oct 2014

fyi...the correct link to the documentation is:

suhailmemon84 47 posts Joined 09/10
09 Oct 2014

Can anyone throw any light on this issue?

tomnolan 594 posts Joined 01/08
09 Oct 2014

I agree that the documentation indicates that your REPLACE PROCEDURE command should fail in your scenario.
Your profile says that you're with I recommend that you open a Teradata Customer Service incident for this issue.

suhailmemon84 47 posts Joined 09/10
13 Oct 2014

Thanks Tom,
I have gone ahead and created an incident for this.

You must sign in to leave a comment.