Solved

creating a synonoym for all tables in a database.

Posted on 2006-07-19
6
1,282 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is the version of ojdbc6.jar 2 54
SQL Developer 6 48
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 43
form builder not starting 3 34
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

785 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