-
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
-
Hi,
as far as I know, that is not possible. But you could create the same tables in your own schema...
Dan
-
[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
-
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.
-
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
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|