Re: execute privileges error

From: Finn Ellebæk Nielsen <ellebuk_at_vip.cybercity.dk>
Date: 1996/12/17
Message-ID: <32B7004E.2FEA@vip.cybercity.dk>#1/1

Thomas J. Kyte wrote:
>
> The user system gets it's ability to drop users via the DBA role. Since roles
> are never enabled when a stored procedure executes, SYSTEM doesn't have the
> ability to drop the user. You need to grant drop user to system first.
>
> On 16 Dec 1996 22:27:56 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:
>
> >I created the following procedure under SYSTEM
> >but keep getting the following error
> >
> >ERROR at line 1:
> >ORA-01031: insufficient privileges
> >ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> >ORA-06512: at "SYS.DBMS_SQL", line 25
> >ORA-06512: at "SYSTEM.TEST_DBMSSQL", line 7
> >ORA-06512: at line 1
> >
> >SYSTEM has been granted execute any procedure privilege but
> >it does not help.
> >
> >The procedure would only work when created under SYS schema
> >and logging in as SYS.
> >
> >What is wrong???
> >
> >
> >create or replace procedure test_dbmssql
> >as
> > cursor_name NUMBER;
> > ignore NUMBER;
> > BEGIN
> > cursor_name := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(cursor_name,'drop user Austin', DBMS_SQL.V7);
> > ignore := DBMS_SQL.EXECUTE(cursor_name);
> > END;
> >
>
> Thomas Kyte
> Oracle Government
> tkyte_at_us.oracle.com
>
> http://govt.us.oracle.com
>
> ---- Check out Oracle Governments web site! -----
> Follow the link to "Tech Center"
> and then downloadable Utilities for some free software...
>
> -------------------
> statements and opinions are mine and do not necessarily
> reflect the opinions of Oracle Corporation

I don't think it's a case about privileges on the drop, but privileges on doing dynamic DDL as the following case illustrates:

The user has DBA rights.
Can create other users and drop them directly, but not from dynamic DDL.
What's wrong here?

SQL> select *
2 from session_roles;

ROLE

CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE SQL>
SQL> create user austin identified by austin;

User created.

SQL>
SQL> create or replace procedure test_dbmssql 2 as

  3   cursor_name                NUMBER;
  4   ignore                     NUMBER;

5 BEGIN
6 cursor_name := DBMS_SQL.OPEN_CURSOR; 7 DBMS_SQL.PARSE(cursor_name,'drop user Austin', DBMS_SQL.V7); 8 ignore := DBMS_SQL.EXECUTE(cursor_name); 9 END;
10 /

Procedure created.

SQL>
SQL> execute test_dbmssql;
begin test_dbmssql; end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "FKOTEXT.TEST_DBMSSQL", line 7
ORA-06512: at line 1

SQL>
SQL> drop user austin;

User dropped.

SQL> BTW, running 7.3.2.2 under NT 4.0 Intel, Service Pack 1.

Thanks for any help.

Cheers,

Finn Ellebaek

-- 
--------------------------------------------------------------------------------
 Finn Ellebaek Nielsen                      Oracle Associated Senior Consultant
 E-mail:         ellebuk_at_vip.cybercity.dk   Ellebaek Consulting
 Mob. Phone:     +45 20 32 49 25            Niels Ebbesens Vej 9, 3. th.
 Mob. Phone SMS: 20324925_at_sms.tdm.dk        DK-1911  Frederiksberg C
 Private Phone:  +45 33 25 34 50            Denmark
--------------------------------------------------------------------------------
 "Life is a beach and then you dive"                      "Divers do it deeper"

Received on Tue Dec 17 1996 - 00:00:00 CST