Going from memory here but this should work:
$ sqlplus /nolog
SQL> connect table_owner/table_owners_password
SQL> GRANT ALL ON table_name TO SYS WITH GRANT OPTION;
SQL> connect sys/sys_password
SQL> GRANT any_privilege ON table_owner.table_name TO other_user;
--
HTH. Additions and corrections welcome.
Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not. It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
"Fim Tox" <tim_at_blueheath.com> wrote in message
news:d4e73249.0202061140.13c4b34_at_posting.google.com...
> Thanks for your reply - I'm not sure what you mean by "grant all with
> grant option" - what's the exact syntax you mean?
> I've tried logging in as FRED and doing:
> grant all with grant option to sys;
> but that's not recognised.
> not sure what to type...
> (i've also tried:
> grant all to sys with grant option;
> to no avail.....)
>
> "Pete Sharman" <peter.sharman_at_oracle.com> wrote in message
news:<xsc88.3$Wo4.165_at_inet-nntp1.oracle.com>...
> > One of the little nuances that still make life annoying for DBA's. To
be
> > able to grant access to another user's objects, you must have been
directly
> > granted the access yourself with grant option, even if you're SYS. In
this
> > case, you must log on as FRED, do a grant all with grant option to SYS,
and
> > then SYS can pass the privilege on.
> >
> > It annoyed me in version 7 and it still annoys me now. As a DBA you can
> > drop someone else's objects, but you can't grant access to someone else.
> > Duh! Hopefully we can soon say "fixed in the next release"!
> >
> > --
> > HTH. Additions and corrections welcome.
> >
> > Pete
> > Author of "Oracle8i: Architecture and Administration Exam Cram"
> >
> > "Controlling developers is like herding cats."
> > Kevin Loney, Oracle DBA Handbook
> >
> > "Oh no, it's not. It's much harder than that!"
> > Bruce Pihlamae, long-term Oracle DBA
> >
> > "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!!!!
Received on Thu Feb 07 2002 - 17:39:39 CST
