Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!

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. Roles are disabled in PL/SQL, so it will not work there. 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!

Stephen. Received on Mon Nov 28 2005 - 17:24:52 CST