Can anyone relate any risks they have encountered using dynamic sql in Teradata stored procedures?
Rumors that I have head (but have not seen any documentation on):
a) it is policy to not grant perm space to users. b) it may break the system. c) it is the DBA's responsibiliy to approve. d) it is risky to let us write sql (even though we write sql for bteq etc with no oversite from DBAs).
Any experience you can relate in this area would be of great help!
To use SysExecSQL, the user that is the creator of the stored procedure must also be the immediate owner. Statements run via SysExecSQL use the access rights of that user, not the caller.
There is the risk of a "SQL Injection" attack whenever a dynamic SQL statement incorporates arbitrary text supplied by a caller - but that's a general application design issue, not specific to Teradata SPs. (A Google search on that term will give you lots of info.)
And while strictly speaking it's not a "risk", some sites find it annoying that developers can't log on as themselves and create SPs with dynamic SQL in a "shared SP" database. This may require special policies and procedures for creation of SPs with dynamic SQL.