This content has been marked as final. Show 5 replies
user7047382 wrote:with Oracle EVERYTHING is prohibited, except that which is explicitly GRANTed.
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...
remove the GRANT that allows these users to DROP partition.
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.
user7047382 wrote:If schema own can CREATE any object, Oracle implicitly GRANT DROP on same object.
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.
so you must accept this reality.