urgent dataretrival
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: urgent dataretrival

  1. #1
    Join Date
    Nov 2001
    Location
    MALAYSIA
    Posts
    11
    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

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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 */

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    You might consider creating and granting roles as well.
    Vinit

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Yeah forget the schema owner.

    select 'grant select, insert, update, delete on '||owner||'.'||table_name||' to USER1, USER2;'
    from dba_tables where owner='OWNERNAMEOFTHETABLES';

  6. #6
    Join Date
    Oct 2001
    Posts
    45
    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
  •  



Click Here to Expand Forum to Full Width