-
hiii I have 3 users on a same database.
but one user has all tables.
Remaining users just select the all tables of data .
around 300 tables.
how to grant these tables to other users?
thanking u
-
grant select any table to user (if there is only one schema).
or
select 'grant select on schema.'||table_name|| to user
from dba_tables
where owner = 'OWNER'
will produce script to individually grant select privileges on eah table owned by 'OWNER'.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
set heading off
set underline off
set pagesize 0
set feedback off
spool yourquery.sql
select 'grant select, insert, update, delete on '||table_name||' to USER1, USER2;'
from dba_tables where owner='OWNERNAMEOFTHETABLES';
spool off
@yourquery.sql
/*review first the script generated(yourquery.sql) if it is what you need */
-
You might consider creating and granting roles as well.
Vinit
-
Yeah forget the schema owner.
select 'grant select, insert, update, delete on '||owner||'.'||table_name||' to USER1, USER2;'
from dba_tables where owner='OWNERNAMEOFTHETABLES';
-
why not create public synonyms.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|