Re: Can you grant select on a whole schema?

From: Slarti <1012-873_at_gmx.de>
Date: 15 Jan 2002 07:20:45 -0800
Message-ID: <ccb8e3b6.0201150720.46edd0e6_at_posting.google.com>


sldcrew2k_at_yahoo.com (steve deno) wrote in message news:<8e6b14d8.0201141046.603bd0ad_at_posting.google.com>... > Help is appreciated!

Or you'll have to make a simple script like this and execute with sql*plus. Just modify to your needs:

.
.
.

DEFINE dest_user=&1
DEFINE dest_pwd=&2
DEFINE source_user=&3
DEFINE source_pwd=&4
DEFINE instance_sid=&5
DEFINE log_dir=&6

set pagesize 0 linesize 1000
set feedback off verify off
spool &log_dir/exp_grant_select.sql

SELECT 'GRANT SELECT ON &source_user'||'.'||table_name||' TO &dest_user;'   FROM dba_tables
 WHERE ( table_name like 'INT%' OR table_name = 'BEZ_PF_BEFI_NUTZ' )    AND owner = UPPER('&source_user')
/
spool off

spool &log_dir/exp_create_synonym.sql
SELECT 'CREATE SYNONYM &dest_user'||'.'||table_name||   ' FOR &source_user'||'.'||table_name||'; '   FROM dba_tables
 WHERE ( table_name like 'INT%' OR table_name = 'BEZ_PF_BEFI_NUTZ' )    AND owner = UPPER('&source_user')
/
spool off
set pagesize 80
set feedback on verify on

connect &source_user/&source_pwd_at_&instance_sid spool &log_dir/exp_grant_select.log
_at_&log_dir/exp_grant_select.sql
spool off
disconnect

connect &source_user/&source_pwd_at_&instance_sid spool &log_dir/exp_create_synonym.log
_at_&log_dir/exp_create_synonym.sql
spool off
.
.
.
Received on Tue Jan 15 2002 - 16:20:45 CET

Original text of this message