allow user to run query on another users tables

[tbtbs]

Can someone quickly tell me how to allow a user to list all the tables in another user schema and select information from those tables?

[pando]

grant privilege

[tamilselvan]

Create a ROLE first.

Assign SELECT, INSERT, DELETE, UPDATE priv to the ROLE.

Assign the ROLE to the new user.

Tamil

[tbtbs]

I did the grants, but I cannot do a select * from tab, to see all the tables owned by the other user?

[slimdave]

Select
   owner,
   table_name
From
   all_tables

[rafiq]

Grant "select any table" system privilege to the user who wants to list the tables from another schema.


hope this will work wothout any problem.


Thanks

[pando]

how about select from owner.tab?

[pando]

Originally posted by rafiq
Grant "select any table" system privilege to the user who wants to list the tables from another schema.


hope this will work wothout any problem.


Thanks

omg is that the brute force way hehe

[tbtbs]

Thanks, to all who replied in a professional manner to my request.
My problem is solved. As per your responses.
This is what i did:

sql>grant select any table to scott

logon on sqlplus as scott, to get a listing of the tables.
sql>select * from all_catalog
where owner = 'scott'

[slimdave]

Originally posted by tbtbs
Thanks, to all who replied in a professional manner to my request.
My problem is solved. As per your responses.
This is what i did:

sql>grant select any table to scott

logon on sqlplus as scott, to get a listing of the tables.
sql>select * from all_catalog
where owner = 'scott'

"Select any table" may work, but I wouldn't describe it as a professional solution. More "a security hole created by administrator laziness".