The SQL SECURITY clause is supported only for those external procedures where both of the following statements are true:
- The procedure has one of the following AppCategory codes in DBC.UDFInfo :
C (CLIv2)
J (Java)
N (.NET)
O (ODBC)
The only non-valid AppCategory code is S.
See Teradata Vantage™ - Data Dictionary, B035-1092 for details.
The SQL ACCESS clause in the procedure definition specifies one of the following options that supports SQL.
- CONTAINS SQL
- MODIFIES SQL DATA
- READS SQL DATA
The SQL ACCESS option cannot be NO SQL. If you specify NO SQL, Vantage aborts the request and returns an error to the requestor.
The following bullets list the valid privilege options.
- CREATOR
- DEFINER
- INVOKER
- OWNER
See “CREATE PROCEDURE (External Form)” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for the definitions of the privilege options.
Vantage treats procedures as follows when the procedure is defined as a DEFINER whether explicitly or by default. This is for the case when the CREATOR of the procedure is not its OWNER.
- Vantage checks the privileges of the CREATOR during compilation of the procedure. The CREATOR must have the appropriate privilege for any object that a statement in the procedure references.
- Vantage checks the privileges of the OWNER (the containing database or user for the procedure) during compilation, and if the OWNER is missing any privileges for any object that is referenced by a statement in the procedure, Vantage returns a warning message.
- During execution, Vantage checks the OWNER privileges for any object referenced by any statement in the procedure. Any access failures caused by not having the appropriate privileges return an error to the procedure, which it can handle if it is written to do so.
- Vantage grants the DROP and EXECUTE privileges to a UDF created in a database or user different from the CREATOR. An OWNER always has the implicit privilege to drop any object it owns. If the OWNER wants to execute the UDF, then it must grant the EXECUTE FUNCTION privilege to that function.
The following rules apply to all procedures:
- Objects referenced by any statement in the procedure need not have the WITH GRANT OPTION privilege. They require only the GRANT privilege on the referenced object.
- Vantage check CREATOR and OWNER privileges on DDL statements during execution.
The following paragraphs summarize how Vantage treats privilege violations for procedures during procedure compilation.
For DCL and DDL requests, Vantage checks the CREATOR and OWNER privileges during compilation.
The system returns warning messages to the procedure for any privilege violations that occur.
For DML requests, Vantage checks the CREATOR and OWNER privileges during compilation:
- For the CREATOR, the system returns error messages to the procedure for any privilege violations that occur.
- For the OWNER, the system returns warning messages to the procedure for any privilege violations that occur.
For dynamic SQL requests, Vantage does not check any privileges.
The following bullets summarize how Vantage treats privilege violations for procedures during procedure execution.
- For DCL and DDL requests, Vantage checks the CREATOR and OWNER privileges during execution.
The system returns error messages to the procedure for any privilege violations that occur.
- For DML requests, Vantage checks the OWNER privileges during execution.
The system returns error messages to the procedure for any privilege violations that occur.
- For dynamic SQL requests, Vantage checks the CREATOR and OWNER privileges during execution.