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
