2

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
6
  • I see, so that is why I haven't seen much about private synonyms. When you say I have to create it for each user. If I have MyTable in S2, I thought I just had to create the synonym in S1.MyTable and then anyone with correct access rights should be able to do S1.MyTable. Am I wrong?
    – Dzyann
    Commented Nov 10, 2014 at 13:42
  • What you're describing is a public synonym. A private synonym in your case would look like create or replace synonym s1.table_name for s2.table_name;
    – captjackvane
    Commented Nov 11, 2014 at 21:57
  • I am sorry I phrased my previous comment incorrectly, I am just trying to make sure, when you say I have to create a synonym for every single user, you mean for every schema no? and anyone with access to those schemas would be able to use it. In your example a person would only need access to s1.table_name and s2.table_name. Am I correct? I am not used to oracle, I am aware that it is said schema = user, but for instance I am not interested in creating the synonym in my user schema, only in S1.
    – Dzyann
    Commented Nov 12, 2014 at 12:11
  • 1
    If I want schemas Tom, Dick, Jane to access a table owned by schema Larry, I can either: A) create 1 public synonym that points to LARRY.TABLE, and make sure that Tom, Dick, and Jane have at least SELECT privs on LARRY.TABLE B) create 3 private synonyms, one for each Tom, Dick, Jane, and also make sure that they have SELECT on LARRY.TABLE. Does that help?
    – captjackvane
    Commented Nov 17, 2014 at 16:57
  • 1
    No problem. Hang in there. Oracle doesn't get less confusing, you just learn to understand the confusion!
    – captjackvane
    Commented Nov 17, 2014 at 17:09
2

Yes, of course. You just need to create synonym in S1 schema (not public synonym).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.