Why does my package loose owner privileges ?

Hi Everybody,
I wonder if somebody can explain this to me. For a Forms demonstration I'd like to allow a normal user to move the SYSDATE back and forth via the fixed_date built-in. This requires ALTER SYSTEM privileges, which the DBAs are not happy to grant to the normal user running the Forms demo. The idea is to create a separate user (FD) who will have the ALTER SYSTEM privilege, create a package with AUTHID DEFINER which provides a procedure that changes fixed_date. Allow the ordinary Forms user to execute that procedure, and since that will run with the privileges of FD it will be able to change the fixed_date.

Now, here is the package:

--

CREATE OR REPLACE PACKAGE suid_test AUTHID DEFINER AS
--

PROCEDURE alt_sys;
--

END;
/
--
--

CREATE OR REPLACE PACKAGE BODY suid_test AS
--

PROCEDURE alt_sys IS

   v_tmp_cmd VARCHAR2(100) := 'SELECT SYSDATE,SYSTIMESTAMP FROM DUAL';    v_sql_cmd VARCHAR2(100) := 'ALTER SYSTEM SET fixed_date=''NONE'''; BEGIN

• Let's see if EXECUTE IMMEDIATE needs any more privileges! DBMS_OUTPUT.PUT_LINE('<->'); DBMS_OUTPUT.PUT_LINE('<+> '||v_tmp_cmd); DBMS_OUTPUT.PUT_LINE('<->'); EXECUTE IMMEDIATE v_tmp_cmd;
• Now let's run the ALTER SYSTEM command! DBMS_OUTPUT.PUT_LINE('<+> '||v_sql_cmd); DBMS_OUTPUT.PUT_LINE('<->'); EXECUTE IMMEDIATE v_sql_cmd; -- EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('<->'); DBMS_OUTPUT.PUT_LINE('<+> Unexp. error in suid_test.alt_sys() .'); DBMS_OUTPUT.PUT_LINE('<!> '||SQLCODE||'='||SQLERRM); -- END alt_sys;
  --
  END suid_test; /
  --
  --
  --
  

And here is the output of a quick test which proves that the FD user does have all required privileges to change fixed_date, and although FD is the DEFINER or the package and the package tries to run the exact same command which worked from the command line, calling the package still fails stating there are no privileges.

SQL*Plus: Release 9.0.1.3.0 - Production on Thu Apr 21 14:43:06 2005

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production JServer Release 9.2.0.5.0 - Production

FD_at_SS1> SPOOL suid_test.log
FD_at_SS1> SET LINESIZE 250
FD_at_SS1> SET PAGESIZE 1000
FD_at_SS1> SET SERVEROUTPUT ON
FD_at_SS1> SET WRAP OFF
FD_at_SS1> @suid_test.sql

Package created.

Package body created.

FD_at_SS1> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24:MI:SS')      "SysDate",
   2            TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS') "TimeStamp"
   3            FROM DUAL;

SysDate             TimeStamp 
 
 



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

 
 

2005-04-21-14:44:03 2005-04-21-14:44:03    

FD_at_SS1> ALTER SYSTEM SET fixed_date='24-MAR-1979';

System altered.

FD_at_SS1> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24:MI:SS')      "SysDate",
   2            TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS') "TimeStamp"
   3            FROM DUAL;

SysDate             TimeStamp 
 
 



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

 
 

1979-03-24-00:00:00 2005-04-21-14:45:02    

FD_at_SS1> ALTER SYSTEM SET fixed_date='NONE';

System altered.

FD_at_SS1> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24:MI:SS')      "SysDate",
   2            TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS') "TimeStamp"
   3            FROM DUAL;

SysDate             TimeStamp 
 
 



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

 
 

2005-04-21-14:45:19 2005-04-21-14:45:19    

FD_at_SS1> EXEC suid_test.alt_sys;
<->    

<+> SELECT SYSDATE,SYSTIMESTAMP FROM DUAL    

<->    

<+> ALTER SYSTEM SET fixed_date='NONE'    

<->    

<->    

<+> Unexpected error in suid_test.alt_sys() .    

<!> -1031=ORA-01031: insufficient privileges    

PL/SQL procedure successfully completed.

FD_at_SS1> spool off;

Changing the AUTHID in the specs to CURRENT_USER allows FD to run the above EXEC command without errors, but that concept will not allow the Forms user to change fixed_date as that USER has no ALTER SYSTEM privilege. So, why is the above exmaple not working? Where and why does it loose the required privileges and how do I solve this?

Any comments, suggestion or idea is more than welcome! Regards,

Keve

--

If you need to reply directly:
keve(at)mail(dot)poliod(dot)hu Received on Thu Apr 21 2005 - 09:18:22 CDT