This discussion is archived
5 Replies Latest reply: Dec 5, 2012 12:42 AM by Paul Horth RSS

Do I need to reapply grants and synonyms. Altering Rename for other schema

user8941550 Newbie
Currently Being Moderated
Hi,

We are following the process to replace some tables in UAT/PROD with new structure of tables.

So for example for Table A in Schema A:

Step1- Create TableA_NEW with the required structure and partitions.
Step2- Insert into TableA_NEW Select * from TableA.
Step3- Alter Table TableA Rename to TableA_OLD --Take Backup of TableA
Step4- Alter Table TableA_NEW Rename to TableA --Change the New table ti Original Table

Now do I need to reapply all the grants and synonyms originally applied to TableA.
When I test in Dev, all the grants and synonyms still hold. But I can't take any chances for UAT/PROD.

Also when I rollback these changes and Rename the tables back to Original table.
Then do I need to Reapply all the grants and synonyms originally applied to TableA.


Please suggest..
  • 1. Re: Do I need to reapply grants and synonyms. Altering Rename for other schema
    user8941550 Newbie
    Currently Being Moderated
    I found this in Oracle Documentation

    If you use the RENAME command to rename a table, view, sequence, or a private synonym of a table, view, or sequence, then grants made for the object are carried forward for the new name,
    So do the synonyms also remain?
  • 2. Re: Do I need to reapply grants and synonyms. Altering Rename for other schema
    user8941550 Newbie
    Currently Being Moderated
    But then I found:

    Renaming a schema object has the following effects:

    All views and PL/SQL program units dependent on a renamed object become invalid (must be recompiled before next use).
    All synonyms for a renamed object return an error when used.

    But I still see Synonyms working in another schema after I have rename the tables.
  • 3. Re: Do I need to reapply grants and synonyms. Altering Rename for other schema
    Paul Horth Expert
    Currently Being Moderated
    user8941550 wrote:
    Hi,

    We are following the process to replace some tables in UAT/PROD with new structure of tables.

    So for example for Table A in Schema A:

    Step1- Create TableA_NEW with the required structure and partitions.
    Step2- Insert into TableA_NEW Select * from TableA.
    Step3- Alter Table TableA Rename to TableA_OLD --Take Backup of TableA
    Step4- Alter Table TableA_NEW Rename to TableA --Change the New table ti Original Table

    Now do I need to reapply all the grants and synonyms originally applied to TableA.
    When I test in Dev, all the grants and synonyms still hold. But I can't take any chances for UAT/PROD.

    Also when I rollback these changes and Rename the tables back to Original table.
    Then do I need to Reapply all the grants and synonyms originally applied to TableA.


    Please suggest..
    Step1- Create TableA_NEW with the required structure and partitions. New table - does not have grants
    Step2- Insert into TableA_NEW Select * from TableA.
    Step3- Alter Table TableA Rename to TableA_OLD --Take Backup of TableA Renamed table keeps grants. Synonym not valid at this point
    Step4- Alter Table TableA_NEW Rename to TableA --Change the New table ti Original Table New table still does not have grants, synonym now valid
    So, grants disappear but synonym will be valid at end of process.
  • 4. Re: Do I need to reapply grants and synonyms. Altering Rename for other schema
    user8941550 Newbie
    Currently Being Moderated
    Thanks for the good explanation Paul.

    But the thing is I can still query the TableA from both the schemas.
    So does the sysnonym still have the Grant/Privilege to Select in the other schema as well..

    Thanks..
  • 5. Re: Do I need to reapply grants and synonyms. Altering Rename for other schema
    Paul Horth Expert
    Currently Being Moderated
    user8941550 wrote:
    Thanks for the good explanation Paul.

    But the thing is I can still query the TableA from both the schemas.
    So does the sysnonym still have the Grant/Privilege to Select in the other schema as well..

    Thanks..
    No, the grants on the new table will disappear.

    So, either you are looking at another tableA for the oher schema or the other schema has 'SELECT ANY TABLE' privilege or something like that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points