[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

creating a synonoym for all tables in a database.

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
jomfra
Asked:
jomfra
3 Solutions
 
sathya_sCommented:
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
 
geobulCommented:
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
 
sathyagiriCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
johnsoneSenior Oracle DBACommented:
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
 
jrb1Commented:
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
 
rbrookerCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now