This discussion is archived
5 Replies Latest reply: Dec 12, 2011 1:13 PM by 687520 RSS

Prevent specific users from dropping table partition

687520 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you Frank, I shall try to implement this.

    Regards,
    M.R.

Legend

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