CODEWITHSUNDEEP
oracle-database
Charles Owen
Charles Owen

Reputation: 2840

Creating a synonym for all objects in another schema

I am interested in creating a SYNONYM for another schema and all objects in them (Tables, View, Procedures, Packages, etc). Currently users are getting an error that a certain table doesn't exist. It's happening because they are running a query like SELECT * FROM mytable. If the query were SELECT * FROM myschema.mytable it would work.

Does creating public synonym make the schema available to all users or roles? Are there any security issues with granting a public synonym? Should I use a non-public synonym? We are allocating permissions to the schema via roles.

Is there a query or script that would enable this?

Upvotes: 0

Views: 4404

Answers (1)

kfinity
kfinity

Reputation: 9091

Yes, you probably want a public synonym. From the docs:

Specify PUBLIC to create a public synonym. Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym.

I think the main security issue they mention is to not create a public synonym with the same name as an existing schema.

See also object name resolution.

There's not a simple command, but you could script it with something like this:

begin
  for r in (select owner, table_name from all_tables where owner = 'MYSCHEMA')
  loop
    execute immediate 'create public synonym ' || r.table_name || ' for ' || r.owner || '.' || r.table_name;
  end loop;
end;
/

Edit: if you want more than just tables, you can change the query to loop over all_objects instead and pick the object types you want:

begin
  for r in (select owner, object_name from all_objects
            where owner = 'MYSCHEMA' 
            and object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE'))
  loop
    execute immediate 'create public synonym ' || r.object_name 
        || ' for ' || r.owner || '.' || r.object_name;
  end loop;
end;
/

Upvotes: 3

Related Questions