Re: SYSTEM as SYSDBA cannot grant privs on others' tables?

James Garrison wrote:
>
> If that's true, then what's the point of the "GRANT ANY PRIVILEGE"
> system privilege?
>
> George Barbour wrote:
> >
> > >.... what am I missing?
> > Security implications. Tables (or any other objects) created in other
> > schemas belong to those schemas.
> > Table "emp" created in schema Scott belongs to Scott and only Scott can
> > alter/manipulate it.
> > Scott can however grant object privileges to other schemas or roles.
> > Scott can also grant object privileges with the "WITH GRANT OPTION",
> > which may? solve the problem as you stated it
> > However, I do not like the idea of one super powerful user being able to
> > manipulate all the data in the database.
> >
> > George Barbour
> >
> > "Jim Garrison" <jhg_at_acm.org> wrote in message
> > news:397B8A37.6F93FB16_at_acm.org...
> > > I'm trying to set up a complex set of schemas and want to do
> > > all the work logged on as SYSTEM. I can create tables in
> > > other schemas, but when I try to grant privileges on those
> > > tables (to PUBLIC, for instance) Oracle tells me I have
> > > insufficient privileges. For example:
> > >
> > > grant select on mfg.orders to public;
> > > ORA-01031: insufficient privileges
> > >
> > > I'm logged on as SYSTEM. It makes no difference if I use
> > > "as SYSDBA" also. As far as I can tell, SYSTEM has all
> > > necessary privileges.... what am I missing?
> > >
> > > --
> > > Jim Garrison (jhg_at_acm.org)
> > > PGP Keys at http://www.acm.org/~jhg RSA 0x04B73B7F DH 0x70738D88
>
> --
> James Garrison Athens Group, Inc.
> mailto:jhg_at_athensgroup.com 5608 Parkcrest Dr
> http://www.athensgroup.com Austin, TX 78731
> PGP: RSA=0x04B73B7F DH/DSS=0x70738D88 (512) 345-0600 x150

A simple workaround is to create a procedure (which SYSTEM can do) in the appropriate schema and use it to do the grant...

create or replace
procedure scott.do_some_sql(p_sql varchar2) is begin
  execute immediate p_sql;
end;

exec scott.do_some_sql('grant select on blah to blah');

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse