Re: Granting priveleges

"Fim Tox" <tim_at_blueheath.com> wrote in message news:d4e73249.0202060526.2bf59f08_at_posting.google.com...
> I've created a user fred who has his own schema.
> I've created a user joe who I want to be able to
> update/insert/delete/select any table in fred's schema.
> So.. I log in as SYS (or SYSTEM, the effect is the same) and type:
> grant update,insert,delete,select on fred.mytable to joe;
> and it complains saying:
> ORA-01929: no privileges to GRANT
> I thought SYS could do anything?
> So.. I explicitly add the GRANT ANY PRIVELEGE system privilege to SYS,
> and try again.
> I get the same result.
> What's going on??
> Help!!!!

By design of SQL only the *owner* can grant privilege or he must grant those privileges with admin to another account. As that will result in a gordian knot of privileges that is usually not recommended. From SYS it would be quite easy to do (provided you have an appropiate version of Oracle, which you don't mention, please mention this always!!!) alter session set current_schema = fred
and do whatever you want
or alternatively write a small pl/sql procedure (again assuming 8i)
create or replace procedure fred.do_sql(sqlstr in varchar2) as begin
execute immediate :sqlstr;
end;
/

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address