Pete Finnigan's Oracle Security Weblog

I was just doing some Sunday afternoon surfing before the Brazilian GP coverage starts at 5:20pm and went over to OTN and noticed an article by Steve on best practices on using NDS (Native Dynamic SQL) in 10g, the article is called "Better to Best NDS". With my interest in SQL injection techniques i found this article very interesting. Steve covers some good sense points, for example on error trapping, he suggests saving the string used so it can be printed out, include an exception handler in the block that includes the EXECUTE IMMEDIATE statement, consolidate the error handling in one function and use the new features of 10g such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and add specific error handlers for common errors. The main point he makes is to store and display the dynamic SQL close to where its used.

Steve also talks about using AUTHID CURRENT_USER for all code (procedures and functions) that eventually use NDS. This means that the dynamic code will run as the invoker of the procedure rather than the owner. There are for's and against's on this subject when we consider security and SQL Injection. If you create a procedure that has NDS in it and create it as definer rights then you have the option of absolutely controlling the privileges that the person using it has. i.e. you create the procedure with the least privileges possible. In the real world this doesn’t happen. If a procedure is created as an invoker rights procedure then there are less general errors due to object resolution as Steve suggests but we also benefit from the fact that the person running it (maybe a application user account) is not likely to have dangerous privileges that can be abused with SQL Injection. The jury is out on which is best but i would go with invoker rights.

Steve also talks about the benefits of binding rather than concatenation. Steve makes the point based on an example that shows better readability and maintainability with binds used. I would make the argument that SQL Injection is virtually impossible if bind variables are used. This and performance make it mandatory to use bind variables.

The article closes with some suggestions on when its better to use DBMS_SQL rather than NDS. Whether you use NDS or not then use bind variables and also trap errors and use AUTHID CURRENT_USER. Steve closes with a note to be careful about using NDS. This is an excellent short article and even though it doesn't mention security specifically its worth reading it and considering the points made.