Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!

stephen O'D wrote:
> Michel Cadot wrote:
>
> > <Bullseye> a écrit dans le message de news: 50lmo1hnd1l7u7l1i0ghloljb22pfe1vm9_at_4ax.com...
> > | Can anyone help me.
> > |
> > | I have a table in SCHEMA1:=
> > |
> > | create table TESTTAB (Dummy varchar2(10) );
> > | grant alter,insert, update, delete, select ON TESTTAB to SCHEMA2
> > |
> > |
> > | Within SCHEMA2 I can do:-
> > | truncate table SCHEMA1.TESTTAB;
> > |
> > | But The following aborts with a Insufficient privileges:-
> > |
> > | create procedure DoIt IS
> > | BEGIN
> > | execute immediate 'TRUNCATE TABLE SCHEMA1.TESTTAB';
> > | END;
> > |
> > | BEGIN
> > | DoIt;
> > | END;
> > |
> > |
> > | There must be some additional privileges needed to use execute
> > | immediate across Schema's. (these all work if all objects are in the
> > | same Schema!
> > |
> > | TIA
> > |
> >
> > 1/ I bet if you execute "set role none" then you can no more execute the truncate.
> > 2/ If you can execute a truncate in another schema then you have "drop any table" privilege. Only DBA should have this high
> > privilege (and even that i doubt) and never an application schema or user.
> > 3/ If you really have the requirement to truncate schema1 table from schema2 then create a truncate_testtab procedure in schema1 and
> > grant execute privilege on this procedure to schema2.
> >
> > Regards
> > Michel Cadot
>
> If you can do the truncate table outside of PL/SQL (ie in SQLPLUS) then
> you have probably got the privilege via a role in schema two.

A nice try, but this proves otherwise:

> > | create table TESTTAB (Dummy varchar2(10) );
> > | grant alter,insert, update, delete, select ON TESTTAB to SCHEMA2

The permissions are a direct grant to the SCHEMA2 user. But let us apply Michel's advice to this
situation and see what happens:

SQL> create table testtab(dummy varchar2(1));

Table created.

SQL> grant alter,insert, update, delete, select ON TESTTAB to scott2;

Grant succeeded.

SQL> connect scott2/*******
Connected.
SQL> create or replace procedure DoIt IS

   2 BEGIN
   3 execute immediate 'TRUNCATE TABLE SCHEMA1.TESTTAB';    4 END;
   5 /

create or replace procedure DoIt IS
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysdba
Connected.
SQL> grant create procedure to scott2:

Grant succeeded.

SQL> connect scott2/*****
Connected.
SQL> create or replace procedure DoIt IS

   2 BEGIN
   3 execute immediate 'TRUNCATE TABLE SCHEMA1.TESTTAB';    4 END;
   5 /

Procedure created.

SQL> exec doit;
BEGIN doit; END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SCOTT2.DOIT", line 3
ORA-06512: at line 1

SQL> connect / as sysdba
Connected.
SQL> grant drop any table to scott2; -- REALLY bad idea

Grant succeeded.

SQL> connect scott2/*****
Connected.
SQL> exec doit;

PL/SQL procedure successfully completed.

SQL>  > Roles
> are disabled in PL/SQL, so it will not work there.

Your advice was taken before it was given, and the PL/SQL block does not work. Michel has
correctly assessed this situation, and his advice is the only valid advice provided in this thread, as proven by the example I have provided here. This IS a permissions issue and involves grants no normal user should be given. Had you read the original post in a more careful manner you'd have noticed the direct grants and avoided suggesting to 'Bullseye' he do exactly as he already had done.

> If you grant the
> drop any table priv directly to the user it will work - but take the
> advice of Michel above, as it is very valid!

Yes, as proven above such a grant WILL allow the procedure to work error-free; it's a shame, really, you couldn't pay closer attention to the details already provided by 'Bullseye' in order to avoid dispensing advice which was taken long before you chose to provide it.

>
> Stephen.

David Fitzjarrell Received on Tue Nov 29 2005 - 10:30:39 CST