SQL SECURITY options available in Stored Procedures in Teradata ?

In Teradata, Stored Procedures have 4 different options for SQL SECURITY. It mainly has to do with access rights , privileges or permissions which will be granted to Procedure while calling it. Let us see the four options in Teradata for SQL Security:

Teradata Stored Procedure Access Rights
Teradata Stored Procedure Access Rights

1) SQL SECURITY CREATOR: If you select this option then all the rights/privileges available to the procedure will be of the CREATOR of the procedure. So if user TD_USER has created the Stored Procedure then procedure will have access rights that of CREATOR ‘TD_USER’ in this case. Also, the volatile tables and default database will be TD_USER.

2) SQL SECURITY OWNER: If you select this option then all the rights/privileges available to the procedure will be of the OWNER of the procedure. So if user TD_USER has created the Stored Procedure in TD_PROC schema, then procedure will have access rights that of OWNER ‘TD_PROC’ in this case. Also, the volatile tables and default database will be TD_PROC.

3) SQL SECURITY DEFINER: If you select this option then all the rights/privileges available to the procedure will be of the DEFINER, the default database of the user invoking the procedure. So if user TD_USER has created the Stored Procedure in TD_PROC schema however the default database for TD_USER is TD_DB_PROCS then procedure will have access rights that of  ‘TD_DB_PROCS’ in this case. Also, the volatile tables and default database will be TD_DB_PROCS.

4) SQL SECURITY INVOKER: If you select this option then all the rights/privileges available to the procedure will be of the INVOKER of the procedure i.e. the user who is calling/executing the procedure. So if user TD_PROC_USER is executing the Stored Procedure then procedure will have access rights that of INVOKER ‘TD_PROC_USER’ in this case. Also, the volatile tables and default database will be TD_PROC_USER.

Default option is DEFINER for SQL SECURITY.

Leave a Reply

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