Solved

creating a synonoym for all tables in a database.

Posted on 2006-07-19
6
1,293 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 150 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 150 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 200 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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