5 Replies Latest reply: Dec 12, 2011 3:13 PM by 687520 RSS

    Prevent specific users from dropping table partition

    687520
      Hello,

      I am having the following issue: on CUSTOMERS table I implemented a policy that prevents specific users to delete records that have REPORT_DATE (this is a DATE column) older than 2 months. Works fine.
      These users are actually 'login users' that I identify using sys_context('USERENV', 'OS_USER') function.

      However... I realized that these users can drop any partition from this table (the table in partitioned BY RANGE) so the policy I implemented is useless.

      My question: is there any way to prevent specific users to drop a partition?
      All partitions in CUSTOMERS table are associated with a unique REPORT_DATE therefore I would like to prevent deletions of a partition that has a related REPORT_DATE older than two months...


      Thank you,
      M. R.
        • 1. Re: Prevent specific users from dropping table partition
          sb92075
          user7047382 wrote:
          Hello,

          I am having the following issue: on CUSTOMERS table I implemented a policy that prevents specific users to delete records that have REPORT_DATE (this is a DATE column) older than 2 months. Works fine.

          However... I realized that these users can drop a partition from this table (the table in partitioned BY RANGE) so the policy I implemented is useless.

          My question: is there any way to prevent specific users to drop a partition?
          All partitions in CUSTOMERS table are associated with a unique REPORT_DATE therefore I would like to prevent deletions of a partition that has a related REPORT_DATE older than two months...


          Thank you,
          M. R.
          with Oracle EVERYTHING is prohibited, except that which is explicitly GRANTed.
          remove the GRANT that allows these users to DROP partition.
          • 2. Re: Prevent specific users from dropping table partition
            687520
            Thank you for your suggestion sb92075. I have little experience with Oracle. How can I remove a GRANT for a specific login user? There is only one schema in the database.

            I just mentioned that users are identified using sys_context('USERENV', 'OS_USER') function.

            Thank You,
            M.R.
            • 3. Re: Prevent specific users from dropping table partition
              Frank Kulash
              Hi,

              Revoke the privilege from the Oracle user that is logged in. (Privileges are granted to and help by Oracle users, not the internal users that you identify though SYS_CONTEXT.)

              If that is the schema owner, then create a different schema for all log-ins, that has only the privileges that those users should have. Change the application so that it uses that Oracle log-in, and not the schema of the owner of the table.
              • 4. Re: Prevent specific users from dropping table partition
                sb92075
                user7047382 wrote:
                Thank you for your suggestion sb92075. I have little experience with Oracle. How can I remove a GRANT for a specific login user? There is only one schema in the database.
                If schema own can CREATE any object, Oracle implicitly GRANT DROP on same object.
                so you must accept this reality.
                • 5. Re: Prevent specific users from dropping table partition
                  687520
                  Thank you Frank, I shall try to implement this.

                  Regards,
                  M.R.