5 Replies Latest reply: Dec 5, 2012 2:42 AM by Paul Horth RSS

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

    user8941550
      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
          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
            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
              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
                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
                  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.