Oracle Synonym – Point to Object in Another Schema

oracle

I don't have too much experience in Oracle, so I am sorry if this is a simple question.

I have to move many objects in the database from one schema to another.
I am trying to update all dependencies as well, but just to be in the safe side I would like to leave behind a link.

Example:

  1. Right now I have in my Schema 1 (S1) a MyTable.
    Users reference it like:

    S1.MyTable

  2. I would like to move MyTable to Schema 2 (S2)

    S2.MyTable

  3. I would like my users to be able to still use MyTable as they used to.

    S1.MyTable /MyTable is really in S2, but users can access it with S1/

I have been reading about Synonyms and it seems you can create public ones so users can reference an object directly, like:

MyTable

But I need them to be able to write S1.MyTable

Can I use synonyms for this?

Best Answer

Here's how searching for any object works (this example assumes the user has appropriate permissions, and that you didn't make the call as "schema.table")

  1. Search the current schema for that object
  2. Search private synonyms for references to that object
  3. Search public synonyms for references to that object

So if you create a public synonym, any user that has access to at least select on the underlying table can see it, without any further action on your part, even going forward for new users. If you create a private synonym, you'll have to create a private synonym for every single user that will have to access that specific object, and you'll have to do this for every object that you move (this applies to tables, sequences, and assorted other DDL like packages, functions, etc).

Either option is acceptable, depending on the level of security necessary for that object.

Related Question