SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

Database
Enthusiast

SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

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() 

SQL SECURITY INVOKER 

BEGIN 

delete from db1.tbl1; 

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

END;

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.


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

Am I missing something here?

-Suhail

Tags (2)
4 REPLIES
Enthusiast

Re: SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

Enthusiast

Re: SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

Can anyone throw any light on this issue?

Teradata Employee

Re: SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

I agree that the documentation indicates that your REPLACE PROCEDURE command should fail in your scenario.

Your profile says that you're with Overstock.com. I recommend that you open a Teradata Customer Service incident for this issue.

Enthusiast

Re: SQL STORED PROCEDURES SQL SECURITY INVOKER OPTION

Thanks Tom,

I have gone ahead and created an incident for this.

-Suhail