Direct access to other user/schema-tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Direct access to other user/schema-tables

  1. #1
    Join Date
    Aug 2000
    Posts
    7
    Hi,

    i have a user/schema with a lot of tables (SCOTT.TAB1, SCOTT.TAB2, ...).
    Is there a way (parameter "SCOTT") that other users can access these tables directly (TAB1, TAB2) without the user/schema value ?

    Tschuess
    Hubi

  2. #2
    Join Date
    Aug 2000
    Posts
    11
    Hi,

    as far as I know, that is not possible. But you could create the same tables in your own schema...

    Dan

  3. #3
    Join Date
    Oct 2000
    Posts
    6
    [QUOTE][i]Originally posted by hubi [/i]
    [B]
    Is there a way (parameter "SCOTT") that other users can access these tables directly (TAB1, TAB2) without the user/schema value ?
    [/B][/QUOTE]

    Hubi,

    Create public synonyms for the tables (e.g. CREATE PUBLIC SYNONYM TAB1 FOR SCOTT.TAB1) you wish to be accessed by other users and have SCOTT or the dba grant the appropriate privileges to the other users or roles.

    Moks

  4. #4
    Join Date
    Oct 2000
    Posts
    9

    Smile create synonyms

    Hubi,

    Why don't you create a public synonym on the tables of scott.
    create public synonym tab1 for scott.tab1;
    Now every user who has been granted privileges on the table can access it just by using tab1.

    Kind regards,
    SLAM.

  5. #5
    Join Date
    Sep 2000
    Posts
    5
    Easily. Create an alias. If they (other users) have been
    granted privileges on the tables, it should work fine.

    ie.
    CREATE PUBLIC SYNONYM tab_2 FOR scott.tab_2 ;

    success?
    ntokozo

  6. #6
    Join Date
    Aug 2000
    Posts
    7
    Thanks for the replys, but the user has more then 100 tables, so it's a lot of work to create a public synonym for every table.
    Is it possible to create a new user who works with the schema "SCOTT" by default ?

    regards,
    Hubi

  7. #7
    Join Date
    Oct 2000
    Posts
    9

    Create a script

    Hubi,

    You can generate a script that selects al the tables of the user SCOTT. Connect to the DB with the user scott via SQL*PLUS.
    Type in following commands:

    spool c:\temp\cr_pu_sy.sql
    SELECT 'create public synonym '||object_name||' for SCOTT.'||object_name||';'
    FROM user_objects
    WHERE object_type = 'TABLE';
    spool off

    Edit the file cr_pu_sy
    Connect again to the DB with scott and execute the script.
    (start c:\temp\cr_pu_sy.sql)

    Kind regards,
    SLAM.

  8. #8
    Join Date
    Aug 2000
    Location
    Straham NH
    Posts
    73
    Why not use SQL to generate the synonym statement. set a spool file the try this statement:

    select 'create public synonym ' || table_name || ' for ' ||
    user || '.' || table_name ||';'
    from user_tables;

    That will generate the statements to create all synonyms for all tables owned by the user. If run the way it is here you will need to edit the file for echo headers and feedback so turn them off first if you want to.


    Gary

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