| create synonym for multiple tables [message #12302] |
Fri, 14 May 2004 03:38  |
kim
Messages: 116
Registered: December 2001
|
Senior Member |
|
|
hi,
I have several tables(63) in schema 'KIMCHOW'. I have created 3 users and they have been given DBA role. however, to manipulate the tables in the KIMCHOW schema, I need to write 'kimchow.table_name'. I've been told to write synonym for the tables. How may I create synonyms for each table and give it the same name in one go?
thanks.
|
|
|
|
|
|
| Re: create synonym for multiple tables [message #12312 is a reply to message #12308] |
Fri, 14 May 2004 08:49  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You will want to put the KIMCHOW reference in front of the table name (kimchow.table_name).
Also, you don't need to give those others the DBA role - that is way too extreme. Just give them select (and insert/update/delete if necessary) on those tables.
kimchow>grant select on table_name to user1; |
|
|
|
| Re: create synonym for multiple tables [message #12345 is a reply to message #12312] |
Sun, 16 May 2004 20:53 |
kimchow
Messages: 16
Registered: May 2004
|
Junior Member |
|
|
I've tried to execute this:
'create synonym planning for planning.kimchow'
It gives the error ORA-00955: object name already exists.
Todd, I don't want to put the schema name 'KIMCHOW' in front of table_name, when I am accessing kimchow's tables. E.g. if I log in as user1(I am in USER1 schema), I will want to access table 'PLANNING' simply as:
'select * from planning'
and not as:
'select * from kimchow.planning'
how may I do so?
concerning the DBA role, it's only for testing purposes.
|
|
|
|
|
|
|
|
| Re: create synonym for multiple tables [message #12366 is a reply to message #12365] |
Mon, 17 May 2004 21:20 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Alternatively, you could create a public synonym from kimchow's schema, instead of creating a synonym from user1's schema. Please see the example below and note the required privilege to create a public synonym.
scott@ORA92> CREATE USER user1 IDENTIFIED BY user1
2 /
User created.
scott@ORA92> CREATE USER kimchow IDENTIFIED BY kimchow
2 /
User created.
scott@ORA92> GRANT CONNECT, RESOURCE TO user1, kimchow
2 /
Grant succeeded.
scott@ORA92> <b>GRANT CREATE PUBLIC SYNONYM TO kimchow
2 /</b>
Grant succeeded.
scott@ORA92> @ CONNECT kimchow/kimchow
scott@ORA92> set termout off
kimchow@ORA92>
kimchow@ORA92> set termout on
kimchow@ORA92> CREATE TABLE planning
2 (col1 NUMBER)
3 /
Table created.
kimchow@ORA92> INSERT INTO planning (col1) VALUES (1)
2 /
1 row created.
kimchow@ORA92> COMMIT
2 /
Commit complete.
kimchow@ORA92> GRANT SELECT ON planning TO user1
2 /
Grant succeeded.
kimchow@ORA92> --STORE SET saved_settings REPLACE
kimchow@ORA92> --SET ECHO OFF FEEDBACK OFF HEADING OFF PAGES 0 VERIFY OFF
kimchow@ORA92> --SPOOL r.sql
kimchow@ORA92> --SELECT 'CREATE PUBLIC SYNONYM ' || table_name || ' FOR kimchow.'
kimchow@ORA92> --FROM all_tables
kimchow@ORA92> --WHERE owner = 'KIMCHOW'
kimchow@ORA92> --/
kimchow@ORA92> --SPOOL OFF
kimchow@ORA92> --START saved_settings
kimchow@ORA92> --START r.sql
<b>kimchow@ORA92> CREATE PUBLIC SYNONYM PLANNING FOR kimchow.PLANNING
2 /</b>
Synonym created.
kimchow@ORA92> @ CONNECT user1/user1
kimchow@ORA92> set termout off
user1@ORA92>
user1@ORA92> set termout on
<b>user1@ORA92> SELECT * FROM planning
2 /</b>
COL1
----------
1
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
