Re: Granting privileges on table owned by different owner

I am using oracle 8.1.7.4
I am logged on as system. I cant grant SELECT on FIN.ACCT_REF to FIN_READ_ONLY because I am not the owner of ACCT_REF. The FIN schema is already existing and it's for an accounting application. I dont know the password for FIN. Thats what the problem is. Otherwise I could login as FIN and grant the privs to the role. I dont want to change the password for FIN because the application may not work. I am in a tricky situation. TIA for your help.

Hans Forbrich <forbrich_at_yahoo.net> wrote in message news:<oT62d.32813$KU5.7479_at_edtnps89>...
> Wade Chy wrote:
>
> > Hi All
> > I am in a situation where I have to grant select privileges on tables
> > owned by a different owner to a specific role. I am logged in as
> > system/sysdba. I have created a new role FIN_READ_ONLY. I need to
> > grant SELECT privileges on all the existing and future tables owned by
> > FIN. I tried to grant SELECT on FIN.ACCT_REF to FIN_READ_ONLY. But was
> > not successful due to insufficient privileges since SYSTEM does not
> > own the table or dont have grantable on the tables. What will be the
> > best way to grant select on all the existing and future tables not
> > owned by me/system? Do I have to login as the tables owner? Or does
> > all the future tables created by FIN will have to granted individuall?
> > I dont know the password for FIN user. I dont want to change the
> > password because of some application problem.
> > Your help / suggestion will be highly appreciated.
> > TIA
> >
> > Wade Chy
>
> There are subtle differences based on database version and significant
> possibilities and implications to the command - to the extent I recommend
> you spend some time in the documentation.
>
> All the doc is available at http://docs.oracle.com. For Oracle9i R2, the
> shortcut is
> http://www.oracle.com/pls/db92/db92.homepage
> >> SQL, PL/SQL, and SQL*Plus syntax and examples
> >> G
> >> GR
> >> Grant: Definition
> or
> >> List of Books
> >> Reference (and look up Grant in the Table of Contents)
>
> One way to do what you want is to use a 'chain of responsibility' method ...
>
> preferrably as owner (but a DBA like SYSTEM will do)
> GRANT {privilege list} TO {app admin} WITH GRANT OPTION;
>
> then as the {app admin} user
> GRANT {privilege sublist} TO {user and role list};
>
> I don't know what's happening in your environment - I just successfully
> tested the following:
>
> connect system/{password}
> create user test identified by test;
> grant connect, resource to test;
> connect test/test
> create table a ( b number );
> connect system/{password}
> grant select on test.a to oe with grant option;
> connect oe/oe
> grant select on test.a to hr;
> connect hr/hr
> select count(*) from test.a;
>
>
> Hans
Received on Thu Sep 16 2004 - 18:17:25 CEST