| How can I create synonyms iteratively ? [message #19778] |
Tue, 09 April 2002 08:29  |
Tabrez
Messages: 4
Registered: April 2002
|
Junior Member |
|
|
Before i pose the question, let me give you a background of what I am trying to do. I have created a user called B and user B creates all the tables in its own tablespace. I have created another user A and this user should have only read access to user B's tables. I can do this by granting the select rights to A on B's tables but when I do a query I have to give the query in the form SELECT * from B.TABLE1. I dont want that. So I had to create a public synonym for B.TABLE1 and call it TABLE1 using the statement CREATE PUBLIC SYNONYM TABLE1 FOR B.TABLE1.
Now if do a SELECT * from TABLE1 as user A, I am actually querying user B's TABLE1. Now comes the real bit. Since there can be several tables in user B's schema, I dont want to go thru each and every table and issue that many CREATE PUBLIC SYNONYMs statement. Instead, I want to automate this so that all the table names are selected from USER_TABLES and then these table names are used in some way so that a Synonym is created iteratively. Does anybody know how to do this ?
Any help would be greatly appreciated.
Regards
Tabrez
|
|
|
|
| Re: How can I create synonyms iteratively ? [message #19779 is a reply to message #19778] |
Tue, 09 April 2002 08:57  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
I haven't heard of a way to create synonyms dynamically when schema B creates a table. If someone else has do tell.
I would suggest using private synonyms unless you intend on giving more than schema A access to B's tables. You have to be very careful with public synonyms cause you can potentially modify the wrong table. Users with higher privs would be able to write to the table if they didn't preface the tablename with the schema name and they had a table with the same name. Or if a user creates a table and there is a public synonym with that name they can get bogus errors (privileges) and you may have to spend some time tracking down the problem. Just a thought. I am sure there are many pro's and con's but I try to avoid public synonyms if I can.
|
|
|
|
| Re: How can I create synonyms iteratively ? [message #19782 is a reply to message #19778] |
Tue, 09 April 2002 10:43 |
Alex Mazur
Messages: 17
Registered: March 2002
|
Junior Member |
|
|
Try to run this pl/sql block.
declare
cursor cur is
select 'CREATE PUBLIC SYNONYM '|| table_name ||' FOR B.' || table_name as sql_stmnt
from all_tables where owner = 'B';
sqlStatement Varchar2(1000);
begin
for rec in cur loop
sqlStatement := rec.sql_stmnt;
execute immediate sqlStatement;
end loop;
exception
when others then raise_application_error( -20001, sqlStatement||' :'||sqlerrm );
end;
I think it should work.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
