Re: Granting privileges on table owned by different owner

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 - 04:28:36 CEST