PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bullseye - 28 Nov 2005 19:01 GMT

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

Reply to this Message

Michel Cadot - 28 Nov 2005 19:19 GMT

| Can anyone help me.
|
[quoted text clipped - 22 lines]
|
| 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

Reply to this Message

stephen O'D - 28 Nov 2005 23:24 GMT

> | Can anyone help me.
> |
[quoted text clipped - 31 lines]
> 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.

Reply to this Message

fitzjarrell@cox.net - 29 Nov 2005 16:30 GMT

> > | Can anyone help me.
> > |
[quoted text clipped - 34 lines]
> 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

Reply to this Message

stephen O'D - 30 Nov 2005 17:26 GMT

> > > | Can anyone help me.
> > > |
[quoted text clipped - 130 lines]
>
> 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.

Reply to this Message