Re: PRIVILEGE PROBLEMS USING EXECUTE IMMEDIATE!
- From: "stephen O'D" <stephen.odonnell@xxxxxxxxx>
- Date: 30 Nov 2005 09:26:13 -0800
fitzjarrell@xxxxxxx wrote:
> stephen O'D wrote:
> > Michel Cadot wrote:
> >
> > > <Bullseye> a écrit dans le message de news: 50lmo1hnd1l7u7l1i0ghloljb22pfe1vm9@xxxxxxxxxx
> > > | 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.
.
- Prev by Date: Question: RMAN Auxiliary instance required init parameters
- Next by Date: Drop a Temporary Segment
- Previous by thread: Question: RMAN Auxiliary instance required init parameters
- Next by thread: Drop a Temporary Segment
- Index(es):
Relevant Pages
|
