?
Solved

creating a synonoym for all tables in a database.

Posted on 2006-07-19
6
Medium Priority
?
1,302 Views
Last Modified: 2012-05-05
hello expert,

I have a huge oracle database.
i create two new users and would like to grant them

public synonym for all the tables in the database.
Is the a security risk in doing so?
Or would you recommend a different approch.

THANKS
0
Comment
Question by:jomfra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:sathya_s
ID: 17137546
It would be bad.  Developers can use "alter session set
current_schema=THAT_APPLICATION" in their apps if they find it too onerous to
prefix the schema name on tables.

There will be a possibility of  Name space clash -- both will have
a same table "TEST" or something and won't be able to coexist.  You should use a bare
minimum number of public synonyms.

Regards,
Sathya
0
 
LVL 17

Assisted Solution

by:geobul
geobul earned 600 total points
ID: 17137592
Hi,

Security risk: it depends on what privileges these two users must have on different objects. There is no difference whether the privileges will be granted on the object itself or on its public synonym, if I'm not wrong.

Different approach: you may grant select privilege on any table (current and future) in the database using the following statement (execute it as sysdba):
grant select on any table to user2;

It is your decision what to use.

Regards, Geo
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 600 total points
ID: 17137700
Create public synonyms and grant them only read only priviliges if that's what u want.
I don't think there is any security risk in it. The advantage would be that the users would not need to prefix the table names with schema name when using synonyms
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 35

Expert Comment

by:johnsone
ID: 17138062
There is no security risk with synonyms.  Even with a synonym, they cannot access the object without a grant.

As stated previously, there could be a name clash when creating public synonyms for all objects in a database.

An alternative is to create private synonyms that each user owns that will only have synonyms for objects they need access to.  This is what we do.  You can easily create private synonyms for all objects in a different schema.

After logging in as the new user, this would create private synonyms for all objects the user has privileges on.

begin
   for stmt in (select 'create synonym "' || object_name || '" for "' || owner || '"."' || object_name || '"' stmt from all_objects) loop
    execute immediate stmt.stmt;
  end loop;
end;
/
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17138625
I agree with johnsone.  There is no security risk of creating synonyms.  I work with a large DB application, (over 20,000) tables.  The only time we had an issue was when we had multiple copies of the DB in the same instance (difference SCHEMA) and the public synonym could only point to one environment.  That wasn't a problem in our case.  I'd use:

begin
   for stmt in (select 'create public synonym "' || object_name || '" for "' || owner || '"."' || object_name || '"' stmt from all_objects where object_type in ('TABLE','VIEW')) loop
    execute immediate stmt.stmt;
  end loop;
end;
/
0
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 800 total points
ID: 17141190
Hi,

if you have a sufficiently high level of trust of the people who will be logging in as the 2 users, you can grant them privilege to select any table :

from sys / system :

grant select any table to <user1>;
grant select any table to <user2>;

this gets around the whole synonym issue.

I maintaned a database with over 300000 grants / synonyms, it was bad. hugely bad.

good luck.

:)

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question