473,320 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Granting privileges on table owned by different owner

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
Jul 19 '05 #1
4 19812
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
Jul 19 '05 #2
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 <fo******@yahoo.net> wrote in message news:<oT62d.32813$KU5.7479@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

Jul 19 '05 #3
Wade Chy wrote:
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.


Is this a 3rd party product?
Jul 19 '05 #4
Wade Chy wrote:
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.


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.
Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: DC_DBA | last post by:
I want to 'grant sysdba to a user' for an application. (At least that is what I believe so far after having had this problem dumped on me 2 days ago.) The support we get from the application vendor...
2
by: Ted | last post by:
How do I grant all privileges for a schema that has a large number of existing tables, procedures, functions, etc to a newly created role, without having to issue a grant statement for each object...
1
by: erin | last post by:
When I run: select * from testtable I get this error message: Invalid object name 'testtable'. However, if I run: select * from testuser.testtable
3
by: Manning | last post by:
Hi- apologies for asking a stupid newbie question, but I'm really stuck at the moment. I need to change table ownership. I've got an asp script which is looking for a table owned by the dbo role,...
2
by: Federica T | last post by:
Another question about SQL Server tables and access grants. I've created an user 'user1' as Access account, ad also as db_owner of a selected database. Then, I've created a table as user1.table1....
2
by: LazyAnt | last post by:
Hello, I have DB2 v8.1 for Linux for a class environment; each user has his/her own database as dbadm and they are suppose to study queries from another database. They have the right...
3
by: CAD Fiend | last post by:
Hello, I have a land development project that has a many-to-many relationship. I have ONE question regarding table structures, and ONE question on how to make a Form with two Subforms, below. ...
3
by: Rahul B | last post by:
Hi, I want to grant only the connect, select, insert, update privileges on all the tables of a schema to a particular user/group Initially, i had revoked all the privileges from public. It...
4
by: Wade Chy | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.