Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
So I am going through a code release, and as part of this, I had to revoke two roles from four tables.
Now I know someone is going to gripe, so I'm just stating it now... I am REQUIRED to code this way, because regardless of whether an error is a true error, or a warning, if the dba I send it to sees ANYTHING when they run it, resembling an error of any kind, the code gets thrown back. I run the code in my own environment with no exception handling at first to make sure that it's yielding the correct response. Then I add the dreaded NULL statement to it to make it not throw any error if run again. Then I run it again to make sure it completes successfully. This is REQUIRED....whether it's desired or not. Out of my hands... lol.
Now with that said, here is what is going on. Both roles had select, insert, update and delete. When this was run in the test environment, it removed all grants but select, and in some cases, all grants but select on only one or two of the tables. Below is my code from the script...
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_REF from NRV_ADMIN}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_ORDER_REF from NRV_ADMIN}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_GROUP_REF from NRV_ADMIN}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_SERAL_REF from NRV_ADMIN}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_REF from NRI_FRCC_ANALYST}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_ORDER_REF from NRI_FRCC_ANALYST}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_GROUP_REF from NRI_FRCC_ANALYST}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN execute immediate
Q'{REVOKE INSERT, UPDATE, SELECT, DELETE on NRSA_FRCC_SERAL_REF from NRI_FRCC_ANALYST}';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
Can anyone explain to me HOW that could be? NO object changes of any kind are made directly to PROD. If a new USER is created, it's done through DEV, and an ER is made to put it into effect. So there is no chance that the grants were made by someone else, leaving me not having the authority to change it. But at the same token, I have full dba privs when it comes to role security...so it shouldn't matter.
Thoughts? Comments? ....Prayers? ;)
Thanks.