OraFAQ Forum: SQL & PL/SQL » SELECT ALL TABLES question

Home » SQL & PL/SQL » SQL & PL/SQL » SELECT ALL TABLES question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT ALL TABLES question [message #9968]

Wed, 17 December 2003 22:05

DG
Messages: 5
Registered: October 2000

Junior Member

Hi! I have a user, say USER_1 with some tables. I want to grant USER_2 select access to all of USER_1's tables only. It seems that the SELECT ALL TABLES "permission" would give USER_2 read-access to all tables for all users. Is there any way to give select access to all tables for a particular user only? thanks!!!!

Report message to a moderator

Re: SELECT ALL TABLES question [message #9969 is a reply to message #9968]

Wed, 17 December 2003 22:55

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Ask Tom (Kyte): He knows the answer.

HTH,
MHE

Report message to a moderator

Re: SELECT ALL TABLES question [message #9975 is a reply to message #9969]

Thu, 18 December 2003 01:34

Thiru
Messages: 1089
Registered: May 2002

Senior Member

In Tom's suggestion, I would change the cursor from 'select tname from tab' into ' select table_name from user_tables' as TAB has been desupported by Oracle,long back.

-Thiru

Report message to a moderator

Re: SELECT ALL TABLES question [message #9976 is a reply to message #9968]

Thu, 18 December 2003 01:43

Thiru
Messages: 1089
Registered: May 2002

Senior Member

One more option is
-- As USER_1,

thiru@9.2.0:SQL>set heading off feedback off pagesize 200 linesize 132 echo off
thiru@9.2.0:SQL>spool grant_user.sql
thiru@9.2.0:SQL>select 'grant select,insert,update,delete on '||table_name||' to USER_2;'||chr(10)
2 from user_tables;
thiru@9.2.0:SQL>spool off
thiru@9.2.0:SQL>@grant_user

-Thiru

Report message to a moderator

Re: SELECT ALL TABLES question [message #10034 is a reply to message #9975]

Wed, 24 December 2003 14:07

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Or even USER_ALL_TABLES if you need to include object tables.

Report message to a moderator

Previous Topic:	Indexes and PL/SQL Tbale
Next Topic:	cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 05:17:58 CDT 2024