My new contract is working with an Oracle 11gR2 database. [Aside: It's great using up-to-date versions and with the new 11.2.0.2 version of XE, it's almost like Christmas.]
With new versions come new features and several new PL/SQL compiler warnings have been added to 11gR2. One of those is "PLW-05018: unit <program unit> omitted optional AUTHID clause; default value DEFINER used". This message was described by Steven Feuerstein as "one of those "nuisance" warnings that will pop up on almost every one of our compiles, because so few developers use AUTHID (for invoker rights) and legacy code certainly does not include this clause"
This description was in the answer to one of the Championship playoff questions which asked players to look at some code and give the number of warnings that it would generate. Most players underestimated the answer, partly because the number of warnings is going up with each version.
For most developers this warning is pretty much superfluous. It is pretty rare to be developing DEFINER rights code for applications. However utilities and some of the built-in schemas (eg MDSYS etc) might have elevated privileges and exposing that power through a procedure is something that needs careful consideration.
It is possible to enable and disable specific warnings. Personally I use the fine-grained option available on the command line through ALTER SESSION.
ALTER SESSION SET PLSQL_WARNINGS=
'ENABLE:ALL','DISABLE:07203','DISABLE:05018','DISABLE:06009';
The DBAs can set these using ALTER SYSTEM, or might set up LOGON triggers on specific schemas to set appropriate values. You may also want a different level of warnings in your development databases that your testing and production databases.
It may also be possible to set your preferred options in your IDE. However looking at "SQL Developer", the options in the GUI to enable/disable these warnings are pretty coarse-grained, so I wouldn't recommend this solution.
 |
| SQL Developer options for setting PL/SQL Warnings |
Another location for defining the PLSQL_WARNINGS level is on the object itself.
XE> create or replace procedure do_it (i_text IN OUT VARCHAR) is
2 begin
3 dbms_output.put_line(i_text);
4 end do_it;
5 /
XE> alter session set plsql_warnings='ENABLE:ALL';
Session altered.
XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
-------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used
This simple procedure gives two errors when all warnings are enabled. If I then recompile it with those warnings disabled, it comes out clean.
XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected
If I do a straight compile on that procedure afterwards, it will use the current session settings (which will be inherited from the system settings if not set specifically).
XE> alter procedure do_it compile;
SP2-0805: Procedure altered with compilation warnings
XE> select text from user_errors where name = 'DO_IT';
TEXT
--------------------------------------------------------------------------------
PLW-07203: parameter 'I_TEXT' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used
However you can specify 'REUSE SETTINGS' so that the explicitly define settings are retained.
XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','DISABLE:07203','DISABLE:05018';
Procedure altered.
XE> alter procedure do_it compile reuse settings;
Procedure altered.
XE> select text from user_errors where name = 'DO_IT';
no rows selected
My recommendations would be for the development environment to have at least some warnings enabled, either at the system level or for specific schemas. Then, if individual modules need differing settings, these can be specified and retained (assuming your IDE is up to the job).
For testing environments, having warnings enabled might be a useful step as this may be the first 'independent' compilation of the code. A second set of eyes reviewing the warnings could be a basic 'code review'.
I don't see a benefit in warnings in production environments. But I don't see a real downside either.
2 comments:
Hi.
Over the last few years the SQL injection and privilege escalation problems have forced Oracle themselves into using invoker rights on many internal packages. This should be a message to us that we need to think more seriously about it ourselves.
I would not be overly disturbed if Oracle made this clause mandatory in future. If nothing else, people would be forced to understand the implications... :)
I think people should leave the warnings on and add the clause to all their code. Of course, I've not bothered to do that yet. :)
Cheers
Tim...
They love backwards compatibility too much to make it mandatory (especially for users of third party products). But you've prompted the next blog post....
I wouldn't be surprised if the next version includes a warning if the program unit uses dynamic SQL or anything else vulnerable to injection.
Post a Comment