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
Printable View
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'.
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.
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.