1 2 3 Previous Next 33 Replies Latest reply on Aug 31, 2018 8:46 AM by Sven W. Go to original post
      • 30. Re: Lock Table but only for external Sessions
        Sven W.

        Billy~Verreynne wrote:

         

        I always have an issue when someone with a self-made problem, does not own the problem, but instead blame the product, or others.

         

        UPDATE ANY TABLE is a very dangerous priv to grant to application schemas. And is the root cause of why you have a problem "protecting" a table against other sessions' DML, during "migration".

         

        Adding a trigger is a hack - it is not a robust approach as identifying the single valid "migration" session requires using session attributes that can easily be duplicated, or even spoofed.

         

        I am quite correct in my analysis that the UPDATE ANY TABLE priv is an idiotic approach to application security.

         

        And neither have you mentioned how you are going to maintain data integrity during a migration process that uses distinct transactions, when this process can fail partially through the migration, with committed transactions that cannot be undone.

         

        Shooting from the hip? Expect some shots to go wild and through your foot instead.

        I totally agree with that UPDATE ANY TABLE is extremly dangerous and should never be used.

         

        Apart from that I mostly disagree with you (which is rare).

         

        Oracle itself used the "trigger hack" for doing replication in the past (now deprecated).

        It can be made fairly secure, if that is required.

        What can be considered a hack is the process to ADD and later to REMOVE the trigger code itself. I don't like that too.

         

        About data integrity: Hans already showed that the commits are done for each partition. So in case of a fail, it should be possible to repeat the process for the not yet done partitions.

        • 31. Re: Lock Table but only for external Sessions

           

          Now your session, that does the migration would set this package variable to TRUE, before running the update. In that case the check in the trigger would not stop the update. Since package variables are session specific, only your session will see the TRUE value.

          Which, of course, means that OTHER SESSIONS can set their copy of the flag to TRUE and perform DML on the same data.

          • 32. Re: Lock Table but only for external Sessions
            Billy~Verreynne

            Is a trigger the most suitable place for implementing database security?

            • 33. Re: Lock Table but only for external Sessions
              Sven W.

              Billy~Verreynne wrote:

               

              Is a trigger the most suitable place for implementing database security?

              Certainly not.

               

              But this whole topic is not about security. Some of us here were trying hard to make it into such a topic. The goal is not to make the system hack-proof. As I understood it, it is simply to prevent other users from accidentically issueing DML while some migration logic is running. Similar to locking the table. A database lock is also no security thing. It is a safety mechanism to protect data integrity.

              1 2 3 Previous Next