Laying down the law over warnings

Tim commented on yesterday's post by saying "I would not be overly disturbed if Oracle made this clause [DEFINER/INVOKER RIGHTS] mandatory in future."


With such a broad range of software that runs against Oracle, some of it from third party vendors, I don't see Oracle making changes that would break existing applications.


But they could and have allowed for you to make it mandatory yourself. Perhaps they even make it so in their internal development.


Start with a simple procedure:



create or replace procedure do_it is
begin
   dbms_output.put_line('Humbug');
end do_it;
/

If we compile it with all the warnings enabled, we get the "Missing AUTHID clause" warning.

XE> alter session set plsql_warnings='ENABLE:ALL';

XE> alter procedure do_it compile;

SP2-0805: Procedure altered with compilation warnings

XE> select text, attribute from user_errors where name = 'DO_IT';

TEXT                                                                             ATTRIBUTE

-------------------------------------------------------------------------------- ---------

PLW-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used WARNING
XE> exec do_it

Humbug

But we can use the PLSQL_WARNINGS setting to make enforcement more "Judge Dredd".

XE> alter procedure do_it compile plsql_warnings='ENABLE:ALL','ERROR:05018';

Warning: Procedure altered with compilation errors.

XE> select text, attribute from user_errors where name = 'DO_IT';

TEXT                                                                             ATTRIBUTE

-------------------------------------------------------------------------------- ---------

PLS-05018: unit DO_IT omitted optional AUTHID clause; default value DEFINER used ERROR

XE> exec do_it

BEGIN do_it; END;

      *

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00905: object GARY.DO_IT is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Rather than just enabling the warning, we can treat it as an error, effectively making the clause mandatory in our environment. Obviously it doesn't make sense for this particular warning to be set at the program unit level. It might make sense for other warnings to be set as errors at the schema or database level, and then individual overrides can be set for exceptional program units.