Procedure to REVOKE grants
670024Dec 1 2008 — edited Dec 1 2008Hello,
I want to write a PROCEDURE that help to REVOKE some privileges from objects that have been DROPPED.
We have several privileges assigned to a role, for example:
GRANT SELECT, INSERT, UPDATE,DELETE ON EXAMPLE_TBL TO OPERATOR_ROLE;
Well, the main problem is that when we DROP a table, this name (EXAMPLE_TBL) dissapears from the OBJECT column in objects privilege at ROLE, instead of that name appears something like "BIN...." cause is in the RECYCLEBIN.
This last name can be link with the old one, searching in DBA_RECYCLEBIN:
SELECT OBJECT_NAME FROM DBA_RECYCLEBIN WHERE OWNER = "USER" AND ORIGINAL_NAME = "EXAMPLE_TBL";
Then, how can I put them together ? I want to execute the REVOKE command once a week, for example.
Thanks!