Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 30 Nov 2005 09:26:13 -0800
Message-ID: <1133371573.405809.14340@g14g2000cwa.googlegroups.com>

fitzjarrell_at_cox.net wrote:
> 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

Well, I would say what is more unfortunate is that when someone is actually trying to be helpful you get flamed for it - especially when what I said was correct ...

The OP said he successfully executed

>Truncate table schema1.TestTab;

in SQLPLUS. The only grants given to schema2 are alter, insert, select, delete, update on testTab. All given directly to the user, and NONE of which will allow him to truncate a table in a different schema, but he was able to truncate it anyway, hence he had another privilege from somewhere.

The fact that he could not execute the same statement within PL/SQL means he had the privilege via role which was automatically disabled in PL/SQL. Michel correctly pointed out that if he executes 'set role none' then the command will no longer work in SQLPLUS - I simply clarified that this is because roles become disabled in PL/SQL - which was not mentioned, and the fact that the OP was asking this question implies he was unaware of this. I knew exactly what Michel ment, I just clarified that point.

Michel's third point was clearly the best solution to the problem, and I never questioned that ... but I felt it was useful to explain why set role none mysteriously made SQLPLUS behave the same as PL/SQL. Received on Wed Nov 30 2005 - 11:26:13 CST