How to write a procedure that lets developers set the time for the database using the FIXED_DATE procedure

The following procedure may be used to let designated database developers change the system time according to their testing needs.

CREATE OR REPLACE PACKAGE admin_pack AUTHID DEFINER
AS
 PROCEDURE set_fixed_date(vv_fixed_date VARCHAR2);
 PROCEDURE unset_fixed_date;
END admin_pack;
/

CREATE OR REPLACE PACKAGE body admin_pack
AS
PROCEDURE set_fixed_date (vv_fixed_date VARCHAR2) IS
 BEGIN
  IF UPPER(vv_fixed_date) = 'NONE' THEN
     EXECUTE IMMEDIATE 'alter system set fixed_date=none';
  ELSE
     EXECUTE IMMEDIATE 'alter system set fixed_date=''' || vv_fixed_date || '''';
  END IF;
END;
PROCEDURE unset_fixed_date IS
 BEGIN
  EXECUTE IMMEDIATE 'alter system set fixed_date=none';
 END;
END admin_pack;
/


CREATE PUBLIC SYNONYM ADMIN_PACK FOR SYS.ADMIN_PACK;
GRANT EXECUTE ON ADMIN_PACK TO SCOTT;

Note the AUTHID clause in the package declaration:

A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:

• If the context for name resolution is CURRENT_SCHEMA.
• If the privileges checked are those of the CURRENT_USER and the enabled roles.

At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

Author: Tomasz Gozdz
Documentation for the AUTHID DEFINER SETTING
Documentation for the FIXED_DATE procedure