Teradata Stored Procedure Access Rights

teradata

When you write a stored procedure a very common consideration is regarding the access rights which should be granted to it. I wrote a post long back regarding SQL SECURITY option available in Teradata. Before going forward make sure to read it: SQL SECURITY OPTION FOR STORED PROCEDURE IN TERADATA . So now you are aware of four options available in Teradata for assigning proper permissions to it. Lets see a small example to check it.

Teradata Stored Procedure Access Rights
Teradata Stored Procedure Access Rights

=== Compile This Stored Procedure in Teradata ===
REPLACE PROCEDURE ACCESSRIGHTSINFO (OUT DBNAME VARCHAR(30))
SQL SECURITY DEFINER /* [OWNER, INVOKER, CREATOR] */
BEGIN
SEL DATABASE INTO :DBNAME;
END;

=== Execute it to check the value of DEFINER ===
CALL ACCESSRIGHTSINFO(DBNAME);

Change value to OWNER, INVOKER & CREATOR one-by-one and see the output returned.
With the output you can relate more to the concept of SQL SECURITY OPTION.

In case you need more clarity, feel free to leave a comment.

Leave a Reply

Your email address will not be published. Required fields are marked *