Toad World® Forums

Grant Select on all tables


I am trying to grant privileges for every table I have (around 1000) to another user. Im having troubles finding the syntax to do so (not experiences in SQL)

I thought it was

Grant select on * to kkp

kkp is the user i am trying to grant access to. I know this works when you do one table at once but since I have so many tables it wont work to do this one by one. I remember I have done something like this before.

I have searched everywhere so hopefully there isnt already threads on this problem.

thank you in advance for any help



Try using Database->Administer->Multiple Object Privileges



No such beast, write a dynamic SQL script to generate the desired script as time
of execution and auto run that script – so something this example below to
return count of all my tables:

@header ‘Count All Tables (Row Count > 0)’


set term off

spool cnt_all.tmp

select ‘select ‘’’||table_name||’ = ‘’||to_char(count(*),’‘999,999,999,999’’)
from '||

table_name||’ having count(*) > 0;’

from user_tables

order by table_name


spool off

set term on







Or, in Toad, use the Database -> Administer -> Multiple Object Privileges page.
(At least, that’s where it was with 9.6, sorry for being out of date).


Dear Toadies,

I have a few questions:

Is TOAD version optimized for 64-bit operation systems? Can It
address more than 3gb of RAM?
On the DownLoad side for RAC installations, I see only “TOAD DBA Suite
for Oracle – RAC Edition 10.6.1. Installer”. What do I install
for regular developers who are connected to the RAC instance?

Many thanks,



1 – It’s still a 32 bit application, so as I understand it, no.

2 – Any download of Toad will have support for RAC. You only need that DBA
Suite download if you have purchased Spotlight on Oracle RAC as part of your
Toad license.


Ad 1 - OTOH you will only have problems with it if TOAD needs that much RAM for
itself and you will have to work hard to achieve that.

Otherwise, the system will handle it.

Ryszard Mikke