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

Wade Chy wrote:

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
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

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 (DOT) net> wrote

Wade Chy wrote:
Is this a 3rd party product?

Wade Chy wrote:

You may wish to browse through Chapter 23 of the 8.1.7 Administrator's
Guide. There are a couple of ideas, but you'll need to dig into the
environment a lot deeper.

http://download-east.oracle.com/docs...6956/privs.htm

While this may not be (probably is not) the case: if you haven't been given
the password to FIN, we need to consider the possibility of internal
security breach. Therefore, the best thing I could suggest is to open an
iTAR - this kind of question is well within the purpose of iTAR.