Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

creating a synonoym for all tables in a database.

Posted on 2006-07-19
6
Medium Priority
?
1,309 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
Technology Partners: 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!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

636 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