Re: Why does my package loose owner privileges ?

From: Chet Justice <chet.justice_at_pfsf.org>
Date: 21 Apr 2005 11:29:47 -0700
Message-ID: <1114108187.576939.145950@o13g2000cwo.googlegroups.com>

Why not just GRANT EXECUTE to the user?

CDJUSTICE_at_TESTING>CREATE USER testuser IDENTIFIED BY TESTING   2 DEFAULT TABLESPACE USERS
  3 QUOTA 0M ON USERS; User created.

Elapsed: 00:00:00.00
CDJUSTICE_at_TESTING>
CDJUSTICE_at_TESTING>GRANT CONNECT TO testuser;

Grant succeeded.

Elapsed: 00:00:00.00
CDJUSTICE_at_TESTING>
CDJUSTICE_at_TESTING>CREATE OR REPLACE PACKAGE suid_test AUTHID DEFINER AS

  2 PROCEDURE alt_sys;
  3 END;
  4 /

Package created.

Elapsed: 00:00:00.00
CDJUSTICE_at_TESTING>show errors
No errors.
CDJUSTICE_at_TESTING>
CDJUSTICE_at_TESTING>CREATE OR REPLACE
  2 PACKAGE BODY suid_test
  3 AS
  4 PROCEDURE alt_sys IS
  5 v_tmp_cmd VARCHAR2(100) := 'SELECT SYSDATE,SYSTIMESTAMP FROM DUAL';
  6 v_sql_cmd VARCHAR2(100) := 'ALTER SYSTEM SET fixed_date=''NONE''';
  7 BEGIN

  8      -- Let's see if EXECUTE IMMEDIATE needs any more privileges!
  9      DBMS_OUTPUT.PUT_LINE('<->');
 10      DBMS_OUTPUT.PUT_LINE('<+> '||v_tmp_cmd);
 11      DBMS_OUTPUT.PUT_LINE('<->');
 12      EXECUTE IMMEDIATE v_tmp_cmd;
 13      -- Now let's run the ALTER SYSTEM command!
 14      DBMS_OUTPUT.PUT_LINE('<+> '||v_sql_cmd);
 15      DBMS_OUTPUT.PUT_LINE('<->');
 16      EXECUTE IMMEDIATE v_sql_cmd;
 17     EXCEPTION
 18       WHEN OTHERS THEN
 19         DBMS_OUTPUT.PUT_LINE('<->');
 20         DBMS_OUTPUT.PUT_LINE('<+> Unexp. error in
suid_test.alt_sys() .');
 21         DBMS_OUTPUT.PUT_LINE('<!> '||SQLCODE||'='||SQLERRM);

 22
 23 END alt_sys;
 24 END suid_test;
 25 /

Package body created.

Elapsed: 00:00:00.01
CDJUSTICE_at_TESTING>show errors
No errors.
CDJUSTICE_at_TESTING>
CDJUSTICE_at_TESTING>GRANT EXECUTE ON suid_test TO testuser;

Grant succeeded.

Elapsed: 00:00:00.01
CDJUSTICE_at_TESTING>
CDJUSTICE_at_TESTING>connect testuser/testing_at_testing Connected.
TESTUSER_at_TESTING>
TESTUSER_at_TESTING>EXEC CDJUSTICE.SUID_TEST.ALT_SYS;
<->

<+> SELECT SYSDATE,SYSTIMESTAMP FROM DUAL

<->

<+> ALTER SYSTEM SET fixed_date='NONE'
<->

PL/SQL procedure successfully completed. Received on Thu Apr 21 2005 - 13:29:47 CDT