Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!

<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 Received on Mon Nov 28 2005 - 13:19:21 CST